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 99 100 101 102 103 104 105 106 107 108 109
| Option Explicit
Dim MyCategorie As String
Private Sub UserForm_Initialize()
Me.OptionButton1.Value = True
End Sub
Private Sub OptionButton1_Click()
Dim LastInputRow As Long
MyCategorie = "plomberie"
LastInputRow = Sheets(MyCategorie).Cells(1, 1).End(xlDown).Row
ListBox1.RowSource = "plomberie!A1: A" & LastInputRow
Me.ListBox1.ListIndex = 0
' Mise à jour libellé liste déroulante
With UserForm1
.Label1.Caption = "Liste de la " & MyCategorie & " "
End With
End Sub
Private Sub OptionButton2_Click()
Dim LastInputRow As Long
MyCategorie = "electricite"
LastInputRow = Sheets(MyCategorie).Cells(1, 2).End(xlDown).Row
ListBox1.RowSource = "electricite!A1: A" & LastInputRow
Me.ListBox1.ListIndex = 0
' Mise à jour libellé liste déroulante
With UserForm1
.Label1.Caption = "Liste de l' " & MyCategorie & " "
End With
End Sub
Private Sub OptionButton3_Click()
Dim LastInputRow As Long
MyCategorie = "carrelage"
LastInputRow = Sheets(MyCategorie).Cells(1, 2).End(xlDown).Row
ListBox1.RowSource = "carrelage!A1: a" & LastInputRow
Me.ListBox1.ListIndex = 0
' Mise à jour libellé liste déroulante
With UserForm1
.Label1.Caption = "Liste du " & MyCategorie & " "
End With
End Sub
Private Sub OptionButton4_Click()
Dim LastInputRow As Long
MyCategorie = "prestation"
LastInputRow = Sheets(MyCategorie).Cells(1, 2).End(xlDown).Row
ListBox1.RowSource = "prestations!A1: a" & LastInputRow
Me.ListBox1.ListIndex = 0
' Mise à jour libellé liste déroulante
With UserForm1
.Label1.Caption = "Liste de la " & MyCategorie & " "
End With
End Sub
Private Sub UpdateListBox(wsName As String, Parametres As MSForms.ListBox, IndexValue As Integer)
Dim LastInputRow As Integer, ColumnIndex As Integer, InputRange As Range
' Les données commencent à la ligne 3
Const FirstInputRow As Integer = 2
' Détermine depuis quelle colonne on prend la liste des items
ColumnIndex = 5 + 3 * (IndexValue - 1)
' Détermine la dernière ligne de la colonne sélectionnée et la plage correspondante
With Sheets(wsName)
LastInputRow = .Cells(FirstInputRow, ColumnIndex).End(xlDown).Row
Set InputRange = .Range(.Cells(FirstInputRow, ColumnIndex), .Cells(LastInputRow, ColumnIndex + 2))
With Parametres
.MultiSelect = False
.ColumnCount = 3
.ColumnWidths = "140;60;40"
.ColumnHeads = True ' Affiche les en-têtes de colonne
.RowSource = wsName & "!" & InputRange.Address ' Spécifie la source de données
.ListIndex = 0 ' Sélectionne le premier item
End With
End With
Set InputRange = Nothing
End Sub
Private Sub ListBox1_Change()
' Mise à jour des items dans la ListBox2
UpdateListBox MyCategorie, Me.ListBox2, Me.ListBox1.ListIndex
End Sub
Private Sub ListBox2_Change()
With Me.ListBox2
Me.TextBox1.Value = .List(.ListIndex) 'Designation
Me.TextBox2.Value = .List(.ListIndex, 1) 'PU
Me.TextBox3.Value = .List(.ListIndex, 2) 'Unité
End With
End Sub
Private Sub CommandButton1_Click()
Me.Hide
Unload Me
End Sub
Private Sub CommandButton2_Click()
Dim Myselection As String
Me.Hide
Myselection = Me.ListBox1.List(ListBox1.ListIndex)
Unload Me
End Sub |
Partager