Alimenter une liste ListBox avec les OptionButton
n
Bonjour;
Bonjour merci pour votre précieuse aide
- Feuille ‘’ Menuprincipale ‘’, bouton recherche, UserForm1 :
ListBox1 alimenté par les OptionButton, puis après select item dans ListBox1 affiche les données dans les TextBox.
Remarque
- L’OptionButton17 (cherche dans toute la faille ‘’MATRICE’’) marche parfaitement.
- Les autre OptionButton ne fonctionnent pas.
Option Explicit
Option Compare Text
Dim Ws As Worksheet
Dim Colonne As Integer
Private Sub CommandButton4_Click()
Recherche
End Sub
Private Sub CommandButton6_Click()
If ListBox1.ListIndex = -1 Then
MsgBox "Aucune personne n'ait sélectionné"
End If
If Me.ListBox1.ListIndex <> -1 Then
Sheets("MATRICE").Activate
Sheets("MATRICE").Rows(Me.ListBox1.List(Me.ListBox1.ListIndex)).Select
Unload Me
End If
End Sub
Private Sub CommandButton7_Click()
With Sheets("Menuprincipale")
Range("C2") = TextBox1.Text
Range("F2") = TextBox2.Text
End With
Unload Me
End Sub
Private Sub CommandButton8_Click()
Dim Prem As String
Dim c As Range
With Me.ListBox1
.Clear
.ColumnCount = 2
.ColumnWidths = "0;100"
End With
With Worksheets("MATRICE").UsedRange
Set c = .Find(Me.TextBox14, LookIn:=xlValues, Lookat:=xlPart)
If Not c Is Nothing Then
Prem = c.Address
Do
With Me.ListBox1
.AddItem c.Row
.List(.ListCount - 1, 1) = c.Offset(, 2 - c.Column)
End With
Set c = .FindNext(c)
Loop While Not c Is Nothing And c.Address <> Prem
End If
End With
If ListBox1.ListCount = 0 Then
MsgBox "Il n'existe pas dans la matrice"
End If
End Sub
Private Sub CommandButton9_Click()
If ListBox1.ListIndex = -1 Then
MsgBox "Aucune personne n'ait sélectionné"
End If
If Me.ListBox1.ListIndex <> -1 Then
Dim yourmsgbox As Integer
yourmsgbox = MsgBox("Veuillez confirmer la suppression définitive", vbOKCancel, "confirmation")
If yourmsgbox = vbCancel Then
Exit Sub
Else
Sheets("MATRICE").Rows(Me.ListBox1.List(Me.ListBox1.ListIndex)).EntireRow.Delete
End If
End If
Unload Me
End Sub
Private Sub ListBox1_Click()
If Me.ListBox1.ListIndex <> -1 Then
Me.TextBox1 = Sheets("MATRICE").Range("A" & Me.ListBox1.List(Me.ListBox1.ListIndex))
Me.TextBox15 = Sheets("MATRICE").Range("O" & Me.ListBox1.List(Me.ListBox1.ListIndex))
Me.TextBox2 = Sheets("MATRICE").Range("B" & Me.ListBox1.List(Me.ListBox1.ListIndex))
Me.TextBox8 = Sheets("MATRICE").Range("H" & Me.ListBox1.List(Me.ListBox1.ListIndex))
Me.TextBox9 = Sheets("MATRICE").Range("I" & Me.ListBox1.List(Me.ListBox1.ListIndex))
Me.TextBox12 = Sheets("MATRICE").Range("L" & Me.ListBox1.List(Me.ListBox1.ListIndex))
Me.TextBox46 = Sheets("MATRICE").Range("AT" & Me.ListBox1.List(Me.ListBox1.ListIndex))
Me.TextBox47 = Sheets("MATRICE").Range("AU" & Me.ListBox1.List(Me.ListBox1.ListIndex))
Me.TextBox5 = Sheets("MATRICE").Range("E" & Me.ListBox1.List(Me.ListBox1.ListIndex))
Me.TextBox18 = Sheets("MATRICE").Range("R" & Me.ListBox1.List(Me.ListBox1.ListIndex))
Me.TextBox44 = Sheets("MATRICE").Range("AR" & Me.ListBox1.List(Me.ListBox1.ListIndex))
Me.TextBox50 = Sheets("MATRICE").Range("AX" & Me.ListBox1.List(Me.ListBox1.ListIndex))
Me.TextBox49 = Sheets("MATRICE").Range("AW" & Me.ListBox1.List(Me.ListBox1.ListIndex))
Me.TextBox20 = Sheets("MATRICE").Range("T" & Me.ListBox1.List(Me.ListBox1.ListIndex))
Me.TextBox34 = Sheets("MATRICE").Range("AH" & Me.ListBox1.List(Me.ListBox1.ListIndex))
Me.TextBox21 = Sheets("MATRICE").Range("U" & Me.ListBox1.List(Me.ListBox1.ListIndex))
Me.TextBox22 = Sheets("MATRICE").Range("V" & Me.ListBox1.List(Me.ListBox1.ListIndex))
End If
End Sub
Private Sub OptionButton1_Click()
' Descriptions
Colonne = 1
'Recherche
End Sub
Private Sub OptionButton12_Click()
' Descriptions
Colonne = 12
'Recherche
End Sub
Private Sub OptionButton15_Click()
' Descriptions
Colonne = 15
'Recherche
End Sub
Private Sub OptionButton17_Change()
If Me.OptionButton17 = True Then
Me.CommandButton8.Visible = True
Else
Me.CommandButton8.Visible = False
End If
End Sub
Private Sub OptionButton18_Click()
' Descriptions
Colonne = 18
'Recherche
End Sub
Private Sub OptionButton2_Click()
' Descriptions
Colonne = 2
'Recherche
End Sub
Private Sub OptionButton20_Click()
' Descriptions
Colonne = 20
'Recherche
End Sub
Private Sub OptionButton21_Click()
' Descriptions
Colonne = 21
'Recherche
End Sub
Private Sub OptionButton22_Click()
' Descriptions
Colonne = 22
'Recherche
End Sub
Private Sub OptionButton34_Click()
' Descriptions
Colonne = 34
'Recherche
End Sub
Private Sub OptionButton44_Click()
' Descriptions
Colonne = 44
'Recherche
End Sub
Private Sub OptionButton46_Click()
' Descriptions
Colonne = 46
'Recherche
End Sub
Private Sub OptionButton47_Click()
' Descriptions
Colonne = 47
'Recherche
End Sub
Private Sub OptionButton50_Click()
' Descriptions
Colonne = 50
'Recherche
End Sub
Private Sub OptionButton5_Click()
' Descriptions
Colonne = 5
'Recherche
End Sub
Private Sub OptionButton8_Click()
' Descriptions
Colonne = 8
'Recherche
End Sub
Private Sub OptionButton9_Click()
' Descriptions
Colonne = 9
'Recherche
End Sub
Private Sub TextBox14_Change()
If Me.TextBox14 = "" Then
Me.CommandButton4.Locked = True
Me.CommandButton8.Locked = True
Else
Me.CommandButton4.Locked = False
Me.CommandButton8.Locked = False
End If
End Sub
Private Sub UserForm_Initialize()
Set Ws = Sheets("MATRICE")
With Me.ListBox1
.ColumnCount = 2
.ColumnWidths = "0;100"
End With
Me.OptionButton1 = True
End Sub
Sub Recherche()
Dim J As Long, NbLg As Long
Dim Tb1, Tb2()
Dim I As Integer, Indice As Integer
Me.ListBox1.Clear
NbLg = Ws.Range("A" & Rows.Count).End(xlUp).Row
Tb1 = Ws.Range("A2:EL" & NbLg)
For J = 1 To UBound(Tb1)
If Tb1(J, Colonne) Like "*" & Me.TextBox14 & "*" Then
Tb1(J, 6) = "X"
Indice = Indice + 1
End If
Next J
If Indice > 0 Then
ReDim Tb2(1 To Indice, 1 To 17)
Indice = 0
For J = 1 To UBound(Tb1)
If Tb1(J, 6) = "X" Then
Indice = Indice + 1
For I = 1 To 17
Tb2(Indice, I) = Tb1(J, I)
Next I
End If
Next J
With Me.ListBox1
.List() = Tb2
End With
End If
If ListBox1.ListCount = 0 Then
MsgBox "Il n'existe pas dans la matrice"
End If
End Submerci et salutations