Private Sub UserForm_Initialize()
'***********************
'Essai du 2012.02.25 avec aide du fichier AdiFourn de Boisgontier
'Microsoft ActiveX Data Object 2.8 Library est activé sur mon poste
'Microsoft ActiveX Data Object 2.8 doit être activé
Dim rs As ADODB.Recordset
Set cnn = New ADODB.Connection
répertoire = ThisWorkbook.Path & "\"
fichier = "ERPLOGICO MSIT - BDD2012.xlsm"
cnn.Open "DRIVER={Microsoft Excel Driver (*.xlm)};DBQ=" & répertoire & fichier
Set rs = cnn.Execute("SELECT [Code Produit],[Désignation MP] FROM BD WHERE [Code Produit]<>'' group BY [Code Produit],[Désignation MP]")
Me.ComboBox1.List = Application.Transpose(rs.GetRows)
rs.Close
cnn.Close
Set rs = Nothing
Set cnn = Nothing
SendKeys "{F4}"
'******************************
For i = 1 To 5 '5
If i = 1 Then largeur = 120 Else largeur = 200
Me("ListView" & i).ColumnHeaders.Add , , "niveau" & i, largeur
Me("ListView" & i).Gridlines = True
Me("ListView" & i).View = lvwReport
Next
For Each c In Application.Index([bd], , 1)
If c <> "" Then
Me.ListView1.ListItems.Add , , c & " - " & c.Offset(, 6)
End If
Next
Me.ListView1.ListItems(1).Selected = False
Set Me.ListView1.SelectedItem = Nothing
For i = 1 To Me.ListView1.ListItems.Count
If i Mod 2 = 0 Then
Me.ListView1.ListItems(i).ForeColor = &H8000& ' &HFF0000 'bleu &H8000& 'vert
Me.ListView1.ListItems(i).Bold = True
End If
Next
Me.Label1.Visible = False
' Me.ListView1.SetFocus
' SendKeys "{F4}"
End Sub
Sub liste(col)
Me("Listview" & CStr(col)).ListItems.Clear
For Each c In Application.Index([bd], , col)
If c.Value <> "" Then
Select Case col
Case 2
If Left(c, 1) = Left(Me("Listview" & CStr(col - 1)).ListItems(L).Text, 1) Then
Me("Listview" & CStr(col)).ListItems.Add , , c.Value & " - " & c.Offset(, 7 - col).Value 'avec ajout " - "
End If
Case Else
X = Me("Listview" & CStr(col - 1)).ListItems(L).Text
X = Mid(X, 1, InStr(X, "-") - 2)
If Left(c, Len(X)) = X Then
Me("Listview" & CStr(col)).ListItems.Add , , c.Value & " - " & c.Offset(, 7 - col).Value 'avec ajout " - "
End If
End Select
End If
Next c
If Me("Listview" & CStr(col)).ListItems.Count > 0 Then
Me.Label1.Visible = False
Me("Listview" & CStr(col)).ListItems(1).Selected = False
Set Me("Listview" & CStr(col)).SelectedItem = Nothing
For i = 1 To Me("Listview" & CStr(col)).ListItems.Count
If i Mod 2 = 0 Then
Me("Listview" & CStr(col)).ListItems(i).ForeColor = &H8000& ' &HFF0000 'bleu &H8000& 'vert
Me("Listview" & CStr(col)).ListItems(i).Bold = True
End If
Next
Else
Me.Label1.Visible = True
Me.Label1.Caption = "Pas de données"
End If
' Me("Listview" & CStr(col)).SetFocus
' SendKeys "{F4}"
End Sub