Option Explicit
'code Patricktoulon
Private Sub Add_ima_Click()
Dim fichier As Variant
fichier = Application.GetOpenFilename("Tous les fichiers (*.jpg),*.jpg")
If fichier = False Then Exit Sub
With Image_art
.Picture = LoadPicture(fichier)
.Tag = fichier
End With
End Sub
Private Sub Userform_initialize()
Combogam.List = Range("Gamme_Article").Value ' affectation de la liste gamme d'article
Comboimp.List = Range("Tableau3").Value 'Affectation impression à la liste impression
Combostock.List = Range("Lumiere9").Value 'Affectation stock à la liste stock
End Sub
Private Sub Add_art_Click()
'Définition des variables
Dim SurComm, LL As Object, Pic As Shape, R As Range
If Me.Combogam <> "" And Me.Text_art <> "" And Me.Text_ref <> "" And Me.Combostock <> "" And Image_art.Tag <> "" Then
If Range("Tarticles").Cells(1) = "" Then 'si la premiere ligne est vide alors c'est la ligne
Set R = Range("Tarticles").Rows(1)
Else 'sinon on ajoute une ligne
Set LL = Range("Tarticles").ListObject.ListRows.Add
Set R = LL.Range
End If
R.EntireRow.RowHeight = 90
SurComm = IIf(Me.Combostock = "Sur commande", Me.Combostock, Val(Me.Combostock))
R.Value = Array(Me.Combogam, Me.Text_art, Me.Text_ref, Me.Comboimp, Me.Text_coul, "", _
Me.Text_taille, Me.Text_description, SurComm, "", "", Image_art.Tag)
With Sheets("Articles")
.Pictures.Insert (Image_art.Tag)
Set Pic = .Shapes(.Shapes.Count)
End With
PlaceThePictureInCenterRange R.Cells(6), Pic, 90 'la marge c'est de 0 a 100
Else
MsgBox "vous avez oublié de choisir une image": Exit Sub
End If
ThisWorkbook.Save
Unload New_article
f
End Sub
Sub PlaceThePictureInCenterRange(rng As Range, Obj As Variant, Optional PercentMarge As Long = 100) 'la marge exprime un pourcentage de 1 à x%
'fonction perso patricktoulon
Dim Ratio#, Wx#, Yx#
Wx = rng.Cells(1).MergeArea.Width * (PercentMarge / 100)
Yx = rng.Cells(1).MergeArea.Height * (PercentMarge / 100)
Ratio = Application.Min(Wx / Obj.Width, Yx / Obj.Height)
With Obj
If TypeName(Obj) = "Shape" Then .LockAspectRatio = msoTrue Else .ShapeRange.LockAspectRatio = msoTrue
.Width = .Width * Ratio
.Top = rng.Top + ((rng.Cells(1).MergeArea.Height - .Height) / 2)
.Left = rng.Left + ((rng.Cells(1).MergeArea.Width - .Width) / 2)
End With
End Sub