Private Sub ComboBox1_Change()
If Me.ComboBox1.ListIndex = -1 Then Exit Sub
Nettoyage
Me.ListBox1.Clear
Dim sht As Worksheet
Dim J As Long
Dim lastRow As Integer
Set sht = Application.ThisWorkbook.Worksheets("Recettes")
'sht.Activate
lastRow = sht.Cells(sht.Rows.Count, "A").End(xlUp).Row
With Me.ListBox1
For J = 2 To lastRow
If sht.Range("A" & J) = Me.ComboBox1 Then
.AddItem sht.Range("B" & J)
.List(.ListCount - 1, 1) = J
End If
Next J
End With
If Not sht Is Nothing Then Set sht = Nothing
End Sub
Private Sub UserForm_Activate()
Dim rngList As Range, XRng As Range
Dim xDic As Object
Dim lastRow As Integer
Dim sht As Worksheet
Set sht = Application.ThisWorkbook.Worksheets("Liste")
sht.Activate
lastRow = sht.Cells(sht.Rows.Count, "A").End(xlUp).Row
Set rngList = sht.Range("A2:A" & lastRow)
Set xDic = CreateObject("Scripting.Dictionary")
With Me.ComboBox2
.Clear
For Each XRng In rngList
If xDic.exists(XRng.Value) Then
' Cancel is true
Else
xDic.Add XRng.Value, 0
.AddItem XRng.Value
End If
Next XRng
End With
Set sht = Application.ThisWorkbook.Worksheets("Recettes")
sht.Activate
lastRow = sht.Cells(sht.Rows.Count, "A").End(xlUp).Row
Set rngList = sht.Range("A2:A" & lastRow)
Set xDic = CreateObject("Scripting.Dictionary")
With Me.ComboBox1
.Clear
For Each XRng In rngList
If xDic.exists(XRng.Value) Then
' Cancel is true
Else
xDic.Add XRng.Value, 0
.AddItem XRng.Value
End If
Next XRng
End With
'---------------------------------- ( Pleao cod )>
NbLignes = sht.Range("A65536").End(xlUp).Row
With Me.ListBox1
.ColumnCount = 2
.ColumnWidths = "-1;0"
End With
Me.ComboBox2.Visible = False
Me.CommandButton2.Visible = False
Me.Label13 = Cells(65535, 1).End(xlUp).Row - 1
TextBox5.Visible = False
'---------------------------------- ( Pleao cod )<
If Not rngList Is Nothing Then Set rngList = Nothing
If Not xDic Is Nothing Then Set xDic = Nothing
If Not XRng Is Nothing Then Set XRng = Nothing
If Not sht Is Nothing Then Set sht = Nothing
End Sub
Private Sub UserForm_Initialize()
End Sub
Private Sub ListBox1_Click()
Dim i As Integer
Dim rTitle As String
Dim xRow As Range
Dim typePlat As String
Dim sht As Worksheet
Dim lastRow As Integer
typePlat = Me.ComboBox1
Debug.Print typePlat
If typePlat = "" Then MsgBox "Un type de plat doit être sélectionné avant de continuer!", vbCritical, "Information!": Exit Sub
i = 0
If Me.ListBox1.ListCount = 0 Then
i = 0
Else
i = Me.ListBox1.ListCount
End If
Debug.Print i
If i = 0 Then Exit Sub
rTitle = Me.ListBox1.List(ListBox1.ListIndex, 0)
Debug.Print rTitle
Set sht = Application.ThisWorkbook.Worksheets("Recettes")
lastRow = sht.Cells(sht.Rows.Count, "A").End(xlUp).Row
Me.Image1.Picture = LoadPicture("")
With sht
For i = 1 To lastRow
If .Cells(i, 1).Value = typePlat Then
If .Cells(i, 2).Value = rTitle Then
Me.ComboBox3.Text = .Cells(i, 3).Value
Me.ComboBox4.Text = .Cells(i, 4).Value
Me.ComboBox5.Text = .Cells(i, 5).Value
Me.ComboBox6.Text = .Cells(i, 6).Value
Me.ComboBox7.Text = .Cells(i, 7).Value
Me.TextBox1.Text = .Cells(i, 8).Value
Me.TextBox2.Text = .Cells(i, 9).Value
Me.TextBox3.Text = .Cells(i, 10).Value
Me.TextBox4.Text = .Cells(i, 11).Value
If VBA.Dir(Me.TextBox4.Text) <> "" Then
Me.Image1.Picture = LoadPicture(Me.TextBox4.Text)
Me.Image1.PictureSizeMode = fmPictureSizeModeClip
Me.Repaint
Me.Label11.Caption = Me.ComboBox4.Text
End If
Exit For
End If
End If
Next i
End With
If Not sht Is Nothing Then Set sht = Nothing
End Sub
