Hello,
I am new in VBA programming,
I have data on an excel worksheet in column A cells(23,i) and A celles(24,i) ordered like this
I want to show the data in a listbox without duplicates and showing only recent dates for each value (N IT) like this:
To show the list I created a button, and set this code :
Private Sub CommandButton1_Click()
If Me.ListBox_Pers.ListIndex = -1 Then
MsgBox ("Vous n'avez pas selectionner une personne")
Else
Acrtu_IT
Load UF_Profil_Edit1
UF_Profil_Edit1.Show
'UF_Choix_Pers_Edit.ListBox_Pers.Clear
End If
End Sub
Where: Private Sub Actu_IT()
Personne = UF_Profil_Edit1.TextBox_Nom & " " & UF_Profil_Edit1.TextBox_Prenom.Value
Set ws = ActiveWorkbook.Worksheets(Personne)
UF_Profil_Edit1.ListBox_IT.Clear
Fin_Col_IT = ws.Cells(23, 256).End(xlToLeft).Column
UF_Profil_Edit1.ListBox_IT.ColumnCount = 4
UF_Profil_Edit1.ListBox_IT.ColumnWidths = "50;450;60;20"
Set Plage = ws.Rows(23)
Set Plage2 = ws_Liste_IT.Columns(2)
For i = 2 To Fin_Col_IT
Val_Cherch = ws.Cells(23, i).Value
Set Trouve = Plage.Cells.Find(what:=Val(Val_Cherch))
If Trouve Is Nothing Then
Else
Set Trouve2 = Plage2.Cells.Find(what:=Val(Val_Cherch))
If Trouve2 Is Nothing Then
Else
UF_Profil_Edit1.ListBox_IT.AddItem Trouve2.Offset(, 2)
'---------------Nom de l'IT------------------------------
UF_Profil_Edit1.ListBox_IT.List(UF_Profil_Edit1.ListBox_IT.ListCount - 1, 1) = Trouve2.Offset(, 1)
'---------------Date de fin de la formation------------------------------
UF_Profil_Edit1.ListBox_IT.List(UF_Profil_Edit1.ListBox_IT.ListCount - 1, 2) = ws.Cells(24, Trouve.Column)
'---------------n° IT---------------------
UF_Profil_Edit1.ListBox_IT.List(UF_Profil_Edit1.ListBox_IT.ListCount - 1, 3) = Trouve2
'-----------°DE-------------------
End If
End If
Next i
'---sorting data in alphabetic order-----------------
Dim a()
a = UF_Profil_Edit1.ListBox_IT.List
If UBound(a, 1) > 1 Then
Module2.Tri a(), LBound(a), UBound(a), 0
UF_Profil_Edit1.ListBox_IT.List = a
I am new in VBA programming,
I have data on an excel worksheet in column A cells(23,i) and A celles(24,i) ordered like this
I want to show the data in a listbox without duplicates and showing only recent dates for each value (N IT) like this:
To show the list I created a button, and set this code :
Private Sub CommandButton1_Click()
If Me.ListBox_Pers.ListIndex = -1 Then
MsgBox ("Vous n'avez pas selectionner une personne")
Else
Acrtu_IT
Load UF_Profil_Edit1
UF_Profil_Edit1.Show
'UF_Choix_Pers_Edit.ListBox_Pers.Clear
End If
End Sub
Where: Private Sub Actu_IT()
Personne = UF_Profil_Edit1.TextBox_Nom & " " & UF_Profil_Edit1.TextBox_Prenom.Value
Set ws = ActiveWorkbook.Worksheets(Personne)
UF_Profil_Edit1.ListBox_IT.Clear
Fin_Col_IT = ws.Cells(23, 256).End(xlToLeft).Column
UF_Profil_Edit1.ListBox_IT.ColumnCount = 4
UF_Profil_Edit1.ListBox_IT.ColumnWidths = "50;450;60;20"
Set Plage = ws.Rows(23)
Set Plage2 = ws_Liste_IT.Columns(2)
For i = 2 To Fin_Col_IT
Val_Cherch = ws.Cells(23, i).Value
Set Trouve = Plage.Cells.Find(what:=Val(Val_Cherch))
If Trouve Is Nothing Then
Else
Set Trouve2 = Plage2.Cells.Find(what:=Val(Val_Cherch))
If Trouve2 Is Nothing Then
Else
UF_Profil_Edit1.ListBox_IT.AddItem Trouve2.Offset(, 2)
'---------------Nom de l'IT------------------------------
UF_Profil_Edit1.ListBox_IT.List(UF_Profil_Edit1.ListBox_IT.ListCount - 1, 1) = Trouve2.Offset(, 1)
'---------------Date de fin de la formation------------------------------
UF_Profil_Edit1.ListBox_IT.List(UF_Profil_Edit1.ListBox_IT.ListCount - 1, 2) = ws.Cells(24, Trouve.Column)
'---------------n° IT---------------------
UF_Profil_Edit1.ListBox_IT.List(UF_Profil_Edit1.ListBox_IT.ListCount - 1, 3) = Trouve2
'-----------°DE-------------------
End If
End If
Next i
'---sorting data in alphabetic order-----------------
Dim a()
a = UF_Profil_Edit1.ListBox_IT.List
If UBound(a, 1) > 1 Then
Module2.Tri a(), LBound(a), UBound(a), 0
UF_Profil_Edit1.ListBox_IT.List = a