1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98
| Option Explicit
Dim Ws As Worksheet
'Pour le formulaire
Private Sub UserForm_Initialize()
Dim J As Long
Dim I As Integer
Set Ws = Sheets("Produits") 'Correspond au nom de votre onglet dans le fichier Excel
With Me.ComboBox1
For J = 2 To Ws.Range("A" & Rows.Count).End(xlUp).Row
.AddItem Ws.Range("A" & J)
Next J
End With
With Me.ComboBox2
For J = 2 To Ws.Range("B" & Rows.Count).End(xlUp).Row
.AddItem Ws.Range("B" & J)
Next J
End With
For I = 1 To 5
Me.Controls("TextBox" & I).Visible = True
Next I
End Sub
'Pour la liste d?roulante numéro d'arret de commercialisation
Private Sub ComboBox1_Change()
Dim Ligne As Long
Dim I As Integer
If Me.ComboBox1.ListIndex = -1 Then Exit Sub
Ligne = Me.ComboBox1.ListIndex + 2
ComboBox2 = Ws.Cells(Ligne, "B")
For I = 1 To 5
Me.Controls("TextBox" & I) = Ws.Cells(Ligne, I + 2)
Next I
End Sub
'Pour le bouton Nouveau produit
Private Sub CommandButton1_Click()
Dim L As Integer
If MsgBox("Confirmez-vous l'insertion de ce nouveau produit?", vbYesNo, "Demande de confirmation d'ajout") = vbYes Then
L = Sheets("Produits").Range("a65536").End(xlUp).Row + 1 'Pour placer le nouvel enregistrement ? la premi?re ligne de tableau non vide
Range("A" & L).Value = ComboBox1
Range("B" & L).Value = ComboBox2
Range("C" & L).Value = TextBox1
Range("D" & L).Value = TextBox2
Range("E" & L).Value = TextBox3
Range("F" & L).Value = TextBox4
Range("G" & L).Value = TextBox5
End If
End Sub
'Pour le bouton Modifier
Private Sub CommandButton2_Click()
Dim Ligne As Long
Dim I As Integer
If MsgBox("Confirmez-vous la modification de ce produit ?", vbYesNo, "Demande de confirmation de modification") = vbYes Then
If Me.ComboBox1.ListIndex = -1 Then Exit Sub
Ligne = Me.ComboBox1.ListIndex + 2
Ws.Cells(Ligne, "B") = ComboBox2
For I = 1 To 5
If Me.Controls("TextBox" & I).Visible = True Then
Ws.Cells(Ligne, I + 2) = Me.Controls("TextBox" & I)
End If
Next I
End If
End Sub
'Pour le bouton Quitter
Private Sub CommandButton3_Click()
Unload Me
End Sub
Private Sub CommandButton4_Click()
Result = "Validate"
Me.Hide
End Sub
Property Get Choices() As String
Dim Counter As Long
Sub Test()
With UserForm1
.lboChoice.Clear
.lboChoice.List = Array("Service Réglementaire et Sécurité", "Service Développement R&D", "Service MKT FR", "Service MKT INT", "Service Achat", "Service Logistique", "PAYS CANADA", "Service Approvisionnement", "Service Qualité")
.Show
If .Result = "Validate" Then
Range("h1").Value = .Choices
End If
End With
Unload UserForm1
End Sub
Private Sub UserForm_Click()
End Sub |
Partager