Bonjour,
svp j'ai mis mon classeur a l’épreuve je travail sur une base de donnée volumineuse je peux pas vous envoyer le classeur vu les données des personnes
problème : je cherche un N° dossier quand je sélectionne la ligne dans la listbox les texbox ne s'affichent pas!!!
Private Sub ListBox1_Click()
Dim col, lig As Variant, i As Byte
col = Array(1, 2, 3, 4, 5, 6, 9, 10, 13, 14, 15, 16, 18, 19) 'numéros des colonnes à récupérer
With [Tab_BD] 'tableau structuré
lig = Application.Match(ListBox1, .Columns(1), 0)
For i = 2 To UBound(col) + 2
If IsError(lig) Then Me("TextBox" & i) = "" Else Me("TextBox" & i) = .Cells(lig, col(i - 2))
Me("TextBox" & i).Visible = True
Next
End With
End Sub
Private Sub TextBox1_Change()
If Me.TextBox1 <> "" Then
mots = Split(Trim(Me.TextBox1), " ")
Tbl = choix
For i = LBound(mots) To UBound(mots)
Tbl = Filter(Tbl, mots(i), True, vbTextCompare)
Next i
If UBound(Tbl) > -1 Then
Dim b(): ReDim b(1 To UBound(Tbl) + 1, 1 To Ncol)
For i = LBound(Tbl) To UBound(Tbl)
a = Split(Tbl(i), "*")
For k = 1 To Ncol: b(i + 1, k) = a(k - 1): Next k
Next i
Me.ListBox1.List = b
Me.Label1.Caption = UBound(Tbl) + 1 & " Ligne(s)"
End If
Else
Userform_Initialize
End If
End Sub
Private Sub TextBox1_Enter()
Dim i As Byte
For i = 2 To 15 'à adapter
Me("TextBox" & i).Visible = True
Next
End Sub
Private Sub Userform_Initialize()
Set f = Sheets("BD")
ColVisu = Array(1, 2, 3, 4, 5, 6, 9, 13, 14) ' colonnes à visualiser
Set Rng = f.Range("Tab_BD")
bd = Rng.Value 'on colle la table Excel dans un tableau VBA
Ncol = UBound(ColVisu) + 1
'-- en têtes de colonne ListBox
x = 9
Y = Me.ListBox1.Top - 12
For Each k In ColVisu
Set Lab = Me.Controls.Add("Forms.Label.1")
Lab.Caption = f.Cells(1, k)
Lab.Top = Y
Lab.Left = x
x = x + f.Columns(k).Width * 0.9
temp = temp & f.Columns(k).Width * 0.9 & ";"
Next
temp = Left(temp, Len(temp) - 1)
Me.ListBox1.ColumnCount = UBound(ColVisu) + 1
Me.ListBox1.ColumnWidths = temp
'--
TblTmp = Rng.Value
For i = LBound(bd) To UBound(bd)
ReDim Preserve choix(1 To i)
For Each k In ColVisu
choix(i) = choix(i) & bd(i, k) & "*"
Next k
Next i
'--- valeurs initiales dans ListBox
Dim Tbl(): ReDim Tbl(1 To UBound(bd), 1 To Ncol)
For i = 1 To UBound(bd)
c = 0
For Each k In ColVisu
c = c + 1: Tbl(i, c) = bd(i, k)
Next k
Next i
Me.StartUpPosition = 0
Me.Top = -20
Me.Left = 0
Me.Height = Application.Height + 20
Me.Width = Application.Width
'TriMultiCol Tbl, LBound(Tbl), UBound(Tbl), 1
Me.ListBox1.List = Tbl
Me.Label1.Caption = Me.ListBox1.ListCount & " Ligne(s)"
ParametresHtml "Géstion des documents Archive"
Me.StartUpPosition = 3
end sub