Dim lstObj As ListObject
Dim Plage As Range
Dim X, Y
Dim nbcol
Dim Ctrl As Control
Private Sub UserForm_Initialize()
'xxx DECLARATION DES VARIABLES xxxxxxxxxxxxxxx
Set lstObj = Worksheets("BD Client").ListObjects("Tableau1")
nbcol = lstObj.HeaderRowRange.Count
MsgBox nbcol
Set Plage = lstObj.DataBodyRange
'xxxxxxxx ALIMENTATION DES LABELS DES MULTIPLAGES xxxxxxxxxxxxx
With Me.Frame2
Dim a, b, c, d
a = 10 'Position Leff
b = 100 'largeur Width
c = 25 'Hauteur Height
d = 5 'Position Top
For X = 1 To Me.Frame2.Controls.Count
'Me("Label" & X + 99).Caption = lstObj.HeaderRowRange.Cells(X) 'Code OK
'Me("Label" & X + 99).Caption = lstObj.HeaderRowRange.Columns(X).Value 'Code Ok
With Me("Label" & X + 99)
.Caption = lstObj.HeaderRowRange.Columns(X).Value
.Left = a
.Width = b
.Height = c
.Top = d
.ForeColor = vbRed
d = d + 30 'Décalage pour le positionnement du prochain Label
End With
Next X
End With
For Y = 1 To Me.Frame2.Controls.Count Step 2
With Me("Label" & Y + 99)
.ForeColor = vbWhite
End With
Next Y
'xxxxxxxx ALIMENTATION DES TextBox DES MULTIPLAGES xxxxxxxxxxxxx
With Me.Frame3
a = 1 'Position Leff
b = 150 'largeur Width
c = 25 'Hauteur Height
d = 5 'Position Top
For X = 1 To Me.Frame3.Controls.Count
'MsgBox X
With Me("TextBox" & X)
.Left = a
.Width = b
.Height = c
.Top = d
d = d + 30 'Décalage pour le positionnement du prochain TextBox
End With
Next X
End With
'xxx ALIMENTATION DU LISTBOX xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx
With Me.ListBox1
.Clear
.ColumnCount = nbcol
.AddItem
.List = Plage.Value
For i = 1 To nbcol
temp = temp & Plage.Columns(i).Width * 0.9 & ";"
Next
.ColumnWidths = temp
End With
Me.MultiPage1.Value = 0
With Me.Frame1
.Caption = " F O R M U L A I R E D E R E C H E R C H E "
End With
With Me.Label1
Set Plage = Range("Tableau1[Code client]")
Reponse = Application.WorksheetFunction.Max(Plage)
.Caption = Format(Reponse, "00000")
.ForeColor = 3
End With
Dim Cel As Range
Set mondico = CreateObject("Scripting.Dictionary")
For Each Cel In Range("Tableau1[Type Client]")
If Not mondico.Exists(Cel.Value) Then mondico.Add Cel.Value, Cel.Value
Next Cel
Me.ComboBox1.AddItem "*"
For Each i In mondico.Items
Me.ComboBox1.AddItem i
Next
Me.ComboBox1.ListIndex = 0
End Sub
Private Sub ListBox1_DblClick(ByVal Cancel As MSForms.ReturnBoolean)
Me.MultiPage1.Value = 1 'Pour acceder la page 2 du multipages
'With Me.Frame3
ligne = Me.ListBox1.ListIndex + 2
'MsgBox Me.ListBox1.ListCount
'MsgBox Me.ListBox1.ColumnCount
For X = 1 To Me.Frame3.Controls.Count
' Me ("TextBox" & X).Cancel
Me("TextBox" & X).Value = Me.ListBox1.List(ligne, X)
Next X
End Sub
Private Sub Image1_Click()
Unload Me
End Sub
Private Sub UserForm_QueryClose(Cancel As Integer, CloseMode As Integer)
'Interdit la fermeture de l'USF avec la crois
If CloseMode = vbFormControlMenu Then
Cancel = True
Me.Caption = "Click the CommandButton to close Me!"
End If
MsgBox "Vous devez appuyer sur le bouton Close sur la 1ère page"
End Sub
