Duplicates items in a listbox

Zsals

New Member
Joined
Jun 1, 2020
Messages
4
Office Version
  1. 2007
Platform
  1. Windows
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
E4WVm.png

I want to show the data in a listbox without duplicates and showing only recent dates for each value (N IT) like this:
xRvkY.png


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
 

Excel Facts

Why does 9 mean SUM in SUBTOTAL?
It is because Sum is the 9th alphabetically in Average, Count, CountA, Max, Min, Product, StDev.S, StDev.P, Sum, VAR.S, VAR.P.

Forum statistics

Threads
1,224,817
Messages
6,181,149
Members
453,021
Latest member
Justyna P

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top