Re : combobox dans userform qui crée des cellules vides
bonjour
les 2 with.... end with étaient imbriqués
plus quelques idées pour plus de contrôle
Private Sub CmdEnregistrer_Click()
'If ComboLVM1 = "" Then
'MsgBox "Pas de nom saisi !", vbOKOnly, "SAISIE INCOMPLÈTE"
'Exit Sub
'End If
Application.ScreenUpdating = False
With Sheets("VIANDES")
Lg = 0
Select Case ComboLVM1.ListIndex
Case -1
If ComboLVM1 <> "" Then Lg = .Range("A65536").End(xlUp).Row + 1
Case Else
Lg = ComboLVM1.ListIndex + 2
End Select
If Lg > 0 Then
.Cells(Lg, 1) = Me.ComboLVM1
.Cells(Lg, 2) = Me.ComboppVM1
.Cells(Lg, 3) = Me.TextBoxVM1
' Unload Me
End If
Lg = 0
Select Case ComboLVM2.ListIndex
Case -1
If ComboLVM2 <> "" Then Lg = .Range("A65536").End(xlUp).Row + 1
Case Else
Lg = ComboLVM2.ListIndex + 2
End Select
If Lg > 0 Then
.Cells(Lg, 1) = Me.ComboLVM2
.Cells(Lg, 2) = Me.ComboppVM2
.Cells(Lg, 3) = Me.TextBoxVM2
End If
' Unload Me
Lg = 0
Select Case ComboLVM3.ListIndex
Case -1
If ComboLVM3 <> "" Then Lg = .Range("A65536").End(xlUp).Row + 1
Case Else
Lg = ComboLVM3.ListIndex + 2
End Select
If Lg > 0 Then
.Cells(Lg, 1) = Me.ComboLVM3
.Cells(Lg, 2) = Me.ComboppVM3
.Cells(Lg, 3) = Me.TextBoxVM3
End If
' Unload Me
Select Case ComboLVM4.ListIndex
Case -1
If ComboLVM4 <> "" Then Lg = .Range("A65536").End(xlUp).Row + 1
Case Else
Lg = ComboLVM4.ListIndex + 2
End Select
If Lg > 0 Then
.Cells(Lg, 1) = Me.ComboLVM4
.Cells(Lg, 2) = Me.ComboppVM4
.Cells(Lg, 3) = Me.TextBoxVM4
End If
' Unload Me
Select Case ComboLVM5.ListIndex
Case -1
Lg = .Range("A65536").End(xlUp).Row + 1
Case Else
Lg = ComboLVM5.ListIndex + 2
End Select
.Cells(Lg, 1) = Me.ComboLVM5
.Cells(Lg, 2) = Me.ComboppVM5
.Cells(Lg, 3) = Me.TextBoxVM5
' Unload Me
Select Case ComboLVM6.ListIndex
Case -1
Lg = .Range("A65536").End(xlUp).Row + 1
Case Else
Lg = ComboLVM6.ListIndex + 2
End Select
.Cells(Lg, 1) = Me.ComboLVM6
.Cells(Lg, 2) = Me.ComboppVM6
.Cells(Lg, 3) = Me.TextBoxVM6
' Unload Me
End With
With Sheets("LEGUMES")
Select Case ComboLLM1.ListIndex
Case -1
If ComboLLM1 <> "" Then Lg = .Range("A65536").End(xlUp).Row + 1
Case Else
Lg = ComboLLM1.ListIndex + 2
End Select
.Cells(Lg, 1) = Me.ComboLLM1
.Cells(Lg, 2) = Me.ComboppLM1
.Cells(Lg, 3) = Me.TextBoxLM1
'
Select Case ComboLLM2.ListIndex
Case -1
Lg = .Range("A65536").End(xlUp).Row + 1
Case Else
Lg = ComboLLM2.ListIndex + 2
End Select
.Cells(Lg, 1) = Me.ComboLLM2
.Cells(Lg, 2) = Me.ComboppLM2
.Cells(Lg, 3) = Me.TextBoxLM2
Select Case ComboLLM3.ListIndex
Case -1
Lg = .Range("A65536").End(xlUp).Row + 1
Case Else
Lg = ComboLLM3.ListIndex + 2
End Select
.Cells(Lg, 1) = Me.ComboLLM3
.Cells(Lg, 2) = Me.ComboppLM3
.Cells(Lg, 3) = Me.TextBoxLM3
Select Case ComboLLM4.ListIndex
Case -1
Lg = .Range("A65536").End(xlUp).Row + 1
Case Else
Lg = ComboLLM4.ListIndex + 2
End Select
.Cells(Lg, 1) = Me.ComboLLM4
.Cells(Lg, 2) = Me.ComboppLM4
.Cells(Lg, 3) = Me.TextBoxLM4
Select Case ComboLLM5.ListIndex
Case -1
Lg = .Range("A65536").End(xlUp).Row + 1
Case Else
Lg = ComboLLM5.ListIndex + 2
End Select
.Cells(Lg, 1) = Me.ComboLLM5
.Cells(Lg, 2) = Me.ComboppLM5
.Cells(Lg, 3) = Me.TextBoxLM5
Select Case ComboLVM6.ListIndex
Case -1
Lg = .Range("A65536").End(xlUp).Row + 1
Case Else
Lg = ComboLLM6.ListIndex + 2
End Select
.Cells(Lg, 1) = Me.ComboLLM6
.Cells(Lg, 2) = Me.ComboppLM6
.Cells(Lg, 3) = Me.TextBoxLM6
End With
With ActiveSheet
.Range("b8").Value = ComboLVM1.Value
.Range("c8").Value = ComboppVM1.Value
.Range("f8").Value = TextBoxVM1.Value
.Range("b9").Value = ComboLVM2.Value
.Range("c9").Value = ComboppVM2.Value
.Range("f9").Value = TextBoxVM2.Value
.Range("b17").Value = ComboLVM3.Value
.Range("c17").Value = ComboppVM3.Value
.Range("f17").Value = TextBoxVM3.Value
.Range("b18").Value = ComboLVM4.Value
.Range("c18").Value = ComboppVM4.Value
.Range("f18").Value = TextBoxVM4.Value
.Range("b27").Value = ComboLVM5.Value
.Range("c27").Value = ComboppVM5.Value
.Range("f27").Value = TextBoxVM5.Value
.Range("b28").Value = ComboLVM6.Value
.Range("c28").Value = ComboppVM6.Value
.Range("f28").Value = TextBoxVM6.Value
.Range("b10").Value = ComboLLM1.Value
.Range("c10").Value = ComboppLM1.Value
.Range("f10").Value = TextBoxLM1.Value
.Range("b11").Value = ComboLLM2.Value
.Range("c11").Value = ComboppLM2.Value
.Range("f11").Value = TextBoxLM2.Value
.Range("b19").Value = ComboLLM3.Value
.Range("c19").Value = ComboppLM3.Value
.Range("f19").Value = TextBoxLM3.Value
.Range("b20").Value = ComboLLM4.Value
.Range("c20").Value = ComboppLM4.Value
.Range("f20").Value = TextBoxLM4.Value
.Range("b29").Value = ComboLLM5.Value
.Range("c29").Value = ComboppLM5.Value
.Range("f29").Value = TextBoxLM5.Value
.Range("b30").Value = ComboLLM6.Value
.Range("c30").Value = ComboppLM6.Value
.Range("f30").Value = TextBoxLM6.Value
End With
Application.ScreenUpdating = True
Unload Me
End Sub
à bientôt