VBA un code qui fonctionne mais qui fait peur

D

dreamalain

XLDnaute Junior
Bonjour

Débutant en VBA, je glane des infos par ci par là pour apprendre.

J'ai réussi un code. Il fonctionne mais c'est une horreur quand je voit ce que vous faites

J'éspère ne pas trop vous faire mal en postant mon usine à gaz ...
Si quelqu'un pouvait me le corriger ce serait très sympa


Private Sub CmdAnuler_Click()
Unload Me
End Sub

Private Sub CmdEnregistrer_Click()
'If ComboLVM1 = "" Then
'MsgBox "Pas de nom saisi !", vbOKOnly, "SAISIE INCOMPLÈTE"
'Exit Sub
'End If
With Sheets("VIANDES")
Select Case ComboLVM1.ListIndex
Case -1
lg = .Range("A65536").End(xlUp).Row + 1
Case Else
lg = ComboLVM1.ListIndex + 2
End Select
.Cells(lg, 1) = Me.ComboLVM1
.Cells(lg, 2) = Me.ComboppVM1
.Cells(lg, 3) = Me.TextBoxVM1
Unload Me

Select Case ComboLVM2.ListIndex
Case -1
lg = .Range("A65536").End(xlUp).Row + 1
Case Else
lg = ComboLVM2.ListIndex + 2
End Select
.Cells(lg, 1) = Me.ComboLVM2
.Cells(lg, 2) = Me.ComboppVM2
.Cells(lg, 3) = Me.TextBoxVM2
Unload Me

Select Case ComboLVM3.ListIndex
Case -1
lg = .Range("A65536").End(xlUp).Row + 1
Case Else
lg = ComboLVM3.ListIndex + 2
End Select
.Cells(lg, 1) = Me.ComboLVM3
.Cells(lg, 2) = Me.ComboppVM3
.Cells(lg, 3) = Me.TextBoxVM3
Unload Me

Select Case ComboLVM4.ListIndex
Case -1
lg = .Range("A65536").End(xlUp).Row + 1
Case Else
lg = ComboLVM4.ListIndex + 2
End Select
.Cells(lg, 1) = Me.ComboLVM4
.Cells(lg, 2) = Me.ComboppVM4
.Cells(lg, 3) = Me.TextBoxVM4
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

With Sheets("LEGUMES")
Select Case ComboLLM1.ListIndex
Case -1
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

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

End With
Unload Me
End Sub


Private Sub ComboLLM1_Change()
With Sheets("LEGUMES")
If ComboLLM1.ListIndex = -1 Then
Me.ComboppLM1 = ""
Me.TextBoxLM1 = ""
Exit Sub
End If
Me.ComboppLM1 = .Cells(ComboLLM1.ListIndex + 2, 2)
Me.TextBoxLM1 = .Cells(ComboLLM1.ListIndex + 2, 3)
End With
End Sub

Private Sub ComboLLM2_Change()
With Sheets("LEGUMES")
If ComboLLM2.ListIndex = -1 Then
Me.ComboppLM2 = ""
Me.TextBoxLM2 = ""
Exit Sub
End If
Me.ComboppLM2 = .Cells(ComboLLM2.ListIndex + 2, 2)
Me.TextBoxLM2 = .Cells(ComboLLM2.ListIndex + 2, 3)
End With


End Sub

Private Sub ComboLLM3_Change()
With Sheets("LEGUMES")
If ComboLLM3.ListIndex = -1 Then
Me.ComboppLM3 = ""
Me.TextBoxLM3 = ""
Exit Sub
End If
Me.ComboppLM3 = .Cells(ComboLLM3.ListIndex + 2, 2)
Me.TextBoxLM3 = .Cells(ComboLLM3.ListIndex + 2, 3)
End With
End Sub

Private Sub ComboLLM4_Change()
With Sheets("LEGUMES")
If ComboLLM4.ListIndex = -1 Then
Me.ComboppLM4 = ""
Me.TextBoxLM4 = ""
Exit Sub
End If
Me.ComboppLM4 = .Cells(ComboLLM4.ListIndex + 2, 2)
Me.TextBoxLM4 = .Cells(ComboLLM4.ListIndex + 2, 3)
End With
End Sub

Private Sub ComboLLM5_Change()
With Sheets("LEGUMES")
If ComboLLM5.ListIndex = -1 Then
Me.ComboppLM5 = ""
Me.TextBoxLM5 = ""
Exit Sub
End If
Me.ComboppLM5 = .Cells(ComboLLM5.ListIndex + 2, 2)
Me.TextBoxLM5 = .Cells(ComboLLM5.ListIndex + 2, 3)
End With
End Sub

Private Sub ComboLLM6_Change()
With Sheets("LEGUMES")
If ComboLLM6.ListIndex = -1 Then
Me.ComboppLM6 = ""
Me.TextBoxLM6 = ""
Exit Sub
End If
Me.ComboppLM6 = .Cells(ComboLLM6.ListIndex + 2, 2)
Me.TextBoxLM6 = .Cells(ComboLLM6.ListIndex + 2, 3)
End With
End Sub

Private Sub ComboLVM2_Change()
With Sheets("VIANDES")
If ComboLVM2.ListIndex = -1 Then
Me.ComboppVM2 = ""
Me.TextBoxVM2 = ""
Exit Sub
End If
Me.ComboppVM2 = .Cells(ComboLVM2.ListIndex + 2, 2)
Me.TextBoxVM2 = .Cells(ComboLVM2.ListIndex + 2, 3)
End With

End Sub




Private Sub ComboLVM1_Change()
With Sheets("VIANDES")
If ComboLVM1.ListIndex = -1 Then
Me.ComboppVM1 = ""
Me.TextBoxVM1 = ""
Exit Sub
End If
Me.ComboppVM1 = .Cells(ComboLVM1.ListIndex + 2, 2)
Me.TextBoxVM1 = .Cells(ComboLVM1.ListIndex + 2, 3)
End With
End Sub


Private Sub ComboLVM3_Change()
With Sheets("VIANDES")
If ComboLVM3.ListIndex = -1 Then
Me.ComboppVM3 = ""
Me.TextBoxVM3 = ""
Exit Sub
End If
Me.ComboppVM3 = .Cells(ComboLVM3.ListIndex + 2, 2)
Me.TextBoxVM3 = .Cells(ComboLVM3.ListIndex + 2, 3)
End With
End Sub

Private Sub ComboLVM4_Change()
With Sheets("VIANDES")
If ComboLVM4.ListIndex = -1 Then
Me.ComboppVM4 = ""
Me.TextBoxVM4 = ""
Exit Sub
End If
Me.ComboppVM4 = .Cells(ComboLVM4.ListIndex + 2, 2)
Me.TextBoxVM4 = .Cells(ComboLVM4.ListIndex + 2, 3)
End With
End Sub

Private Sub ComboLVM5_Change()
With Sheets("VIANDES")
If ComboLVM5.ListIndex = -1 Then
Me.ComboppVM5 = ""
Me.TextBoxVM5 = ""
Exit Sub
End If
Me.ComboppVM5 = .Cells(ComboLVM5.ListIndex + 2, 2)
Me.TextBoxVM5 = .Cells(ComboLVM5.ListIndex + 2, 3)
End With
End Sub

Private Sub ComboLVM6_Change()
With Sheets("VIANDES")
If ComboLVM6.ListIndex = -1 Then
Me.ComboppVM6 = ""
Me.TextBoxVM6 = ""
Exit Sub
End If
Me.ComboppVM6 = .Cells(ComboLVM6.ListIndex + 2, 2)
Me.TextBoxVM6 = .Cells(ComboLVM6.ListIndex + 2, 3)
End With
End Sub

Private Sub UserForm_Initialize()
'
Dim Cell As Range

With Sheets("VIANDES")
For Each Cell In .Range("A2:A" & .Range("A65536").End(xlUp).Row)
Me.ComboLVM1.AddItem (Cell)
Next

For Each Cell In .Range("A2:A" & .Range("A65536").End(xlUp).Row)
Me.ComboLVM2.AddItem (Cell)
Next

For Each Cell In .Range("A2:A" & .Range("A65536").End(xlUp).Row)
Me.ComboLVM3.AddItem (Cell)
Next

For Each Cell In .Range("A2:A" & .Range("A65536").End(xlUp).Row)
Me.ComboLVM4.AddItem (Cell)
Next

For Each Cell In .Range("A2:A" & .Range("A65536").End(xlUp).Row)
Me.ComboLVM5.AddItem (Cell)
Next

For Each Cell In .Range("A2:A" & .Range("A65536").End(xlUp).Row)
Me.ComboLVM6.AddItem (Cell)
Next
End With

With Sheets("LEGUMES")
For Each Cell In .Range("A2:A" & .Range("A65536").End(xlUp).Row)
Me.ComboLLM1.AddItem (Cell)
Next
For Each Cell In .Range("A2:A" & .Range("A65536").End(xlUp).Row)
Me.ComboLLM2.AddItem (Cell)
Next
For Each Cell In .Range("A2:A" & .Range("A65536").End(xlUp).Row)
Me.ComboLLM3.AddItem (Cell)
Next
For Each Cell In .Range("A2:A" & .Range("A65536").End(xlUp).Row)
Me.ComboLLM4.AddItem (Cell)
Next
For Each Cell In .Range("A2:A" & .Range("A65536").End(xlUp).Row)
Me.ComboLLM5.AddItem (Cell)
Next
For Each Cell In .Range("A2:A" & .Range("A65536").End(xlUp).Row)
Me.ComboLLM6.AddItem (Cell)
Next
End With


ComboLVM1.Value = Range("b8").Value
ComboppVM1.Value = Range("c8").Value
TextBoxVM1.Value = Range("f8").Value

ComboLVM2.Value = Range("b9").Value
ComboppVM2.Value = Range("c9").Value
TextBoxVM2.Value = Range("f9").Value

ComboLVM3.Value = Range("b17").Value
ComboppVM3.Value = Range("c17").Value
TextBoxVM3.Value = Range("f17").Value

ComboLVM4.Value = Range("b18").Value
ComboppVM4.Value = Range("c18").Value
TextBoxVM4.Value = Range("f18").Value

ComboLVM5.Value = Range("b27").Value
ComboppVM5.Value = Range("c27").Value
TextBoxVM5.Value = Range("f27").Value

ComboLVM6.Value = Range("b28").Value
ComboppVM6.Value = Range("c28").Value
TextBoxVM6.Value = Range("f28").Value


ComboLLM1.Value = Range("b10").Value
ComboppLM1.Value = Range("c10").Value
TextBoxLM1.Value = Range("f10").Value

ComboLLM2.Value = Range("b11").Value
ComboppLM2.Value = Range("c11").Value
TextBoxLM2.Value = Range("f11").Value


ComboLLM3.Value = Range("b19").Value
ComboppLM3.Value = Range("c19").Value
TextBoxLM3.Value = Range("f19").Value

ComboLLM4.Value = Range("b20").Value
ComboppLM4.Value = Range("c20").Value
TextBoxLM4.Value = Range("f20").Value

ComboLLM5.Value = Range("b29").Value
ComboppLM5.Value = Range("c29").Value
TextBoxLM5.Value = Range("f29").Value


ComboLLM6.Value = Range("b30").Value
ComboppLM6.Value = Range("c30").Value
TextBoxLM6.Value = Range("f30").Value
End Sub



Ouf !!!

A bientôt pour vos lumières
 
Trier par date Trier par votes
Pierrot93

Pierrot93

XLDnaute Barbatruc
Re : VBA un code qui fonctionne mais qui fait peur

Bonjour,

un code jeté comme ca sur un post est assez imbuvable, enfin pour moi.... Vu le nombre probable de contrôles sur ton usf, pour optimiser ton code, le mieux serait l'utilisation de module de classe...

Fait une petite recherche sur le forum, il y a pas mal d'exemple assez simple à appliquer, ci-dessous le lien vers le dernier en date :
https://www.excel-downloads.com/threads/resolu-recuperer-le-nom-dun-commandbutton.151203/

Sinon reviens avec un tout petit fichier et juste le minimum d'objets afin de bien représenter le problème qui se pose à toi...

bonne journée
@+
 
Vote positif 0 Vote négatif

Discussions similaires

S
  • Question
XL 2021 macro VBA excel
Réponses
1
Affichages
159
crocrocro
C
S
  • Question
Microsoft 365 créer un macro vba
Réponses
0
Affichages
133
souhaib ad
S
D
  • Question
XL 2010 Identification d'une cellule cliquée
Réponses
6
Affichages
312
dadu35
D
P
  • Question
XL 2019 Copier des cellules d'une feuille sur une autre
Réponses
2
Affichages
210
phoceenjo
P
A
  • Question
Microsoft 365 Code VBA Envoi contenu tableau Excel par Email
Réponses
1
Affichages
92
Phil69970
Phil69970
D
  • Question
XL 2010 Valoriser cellule par clic
Réponses
4
Affichages
191
fanch55
fanch55
C
  • Résolu(e)
XL 2021 Remplir données devis en vba avec descriptions/quantités/PUs alignés
2
Réponses
18
Affichages
435
Phil69970
Phil69970
lynyrd
  • Résolu(e)
XL 2016 Supprimer 1 sur 13
Réponses
6
Affichages
121
lynyrd
lynyrd
L
  • Question
VBA : erreur sur boucle
Réponses
2
Affichages
58
Latias
L
C
  • Résolu(e)
XL 2021 Tri alpha sur combobox à 2 colonnes
2
Réponses
15
Affichages
340
dysorthographie
dysorthographie

Membres actuellement en ligne

13 membres et 157 invités.

Partager cette page

Tutos vidéos

Power Query : Ajouter des requêtes (Excel et Power BI)
Créez votre première fonction LAMBDA dans Excel en 3 étapes!
LAMBDA, l’arme secrète de Microsoft pour Excel !
Office Insider - Découvrez les nouveautés Excel avant tout le monde

Statistiques des forums

Discussions
312 082
Messages
2 085 172
Membres
102 806
dernier inscrit
rle