This useful code listing prevents users from entering duplicate entries into a listbox or modifying existing listbox entries. While the code prevents users from adding duplicate entries, you can modify the True condition of the If block in the Add_New procedure to suit your needs. Declare the constant, LB_FINDSTRINGEXACT and the SendMessage function declaration in a BAS module:
Declare Function SendMessageByString Lib _ "user32" Alias "SendMessageA" (ByVal hWnd As _ Long, ByVal wMsg As Long, ByVal wParam As _ Long, ByVal lParam As String) As LongPublic Const LB_FINDSTRINGEXACT = &H1A2
This function uses the SendMessageByString API and returns either the existing item’s index or -1. If it returns -1, you can add the item:
Function ChkListDuplicates(chwnd As Long, _ StrText As String) As Boolean ChkListDuplicates = (SendMessageByString(chwnd, _ LB_FINDSTRINGEXACT, -1, StrText) > -1)End Function
If the LB_FINDEXACTSTRING message returns a value of -1, no match was found, so ChkListDuplicates returns False. You can use this value to determine whether to add a new item to your list:
Private Sub Add_New() Dim user As String user = InputBox("Add ListBox Entry", "Test Project") If Len(user) Then If Not ChkListDuplicates(List1.hWnd, _ Trim(user)) Then List1.AddItem Trim(user) Else MsgBox "Duplicate Names can not be " & _ "added." & vbCrLf & "Please " & _ "make sure you are not adding " & _ "duplicate names.", vbExclamation, _ "Test Project: Invalid Operation" End If End IfEnd Sub