List box click event no longer working

[Courgettey]

Hello - I am new to posting on here but have found these forums very helpful so hopefully you can help!

I am using a userform with a listbox. The listbox is pre populated. When the user selects an item from the listbox, I would like to search previous outputs (in column C) in the worksheet to see if there are any duplicates, alert the user and stop them from progressing.

My code did work... but then I closed and re-opened my file and it no longer works. Can anyone help or spot where I went wrong?

Code:

Private Sub ListBox_Click()
With Sheet4.Range("C2:C500")
    For ListItem = 0 To ListBox.ListCount - 1
    
        If Asset_List.Selected(ListItem) = True Then
                If Selection.Find(What:=ListBox.List(ListItem), LookIn:=xlValues) Is Nothing Then
'
                Else
                   MsgBox "This listbox item has already been submitted, please choose another  "
                    Exit Sub
                End If
        End If
        
    Next ListItem
End With

End Sub

 

[RoryA]

Welcome to the forum.

Did you change the name of the listbox?

 

[Courgettey]

Hi Rory,

Oops, yes - the code should say:

Private Sub Asset_List_Click()
With Sheet4.Range("C2:C500")
    For ListItem = 0 To Asset_List.ListCount - 1
    
        If Asset_List.Selected(ListItem) = True Then
                If Selection.Find(What:=Asset_List.List(ListItem), LookIn:=xlValues) Is Nothing Then
                 '   MsgBox "NOT Found " & Asset_List.Selected(ListItem)
                 '   Exit Sub
                Else
                   MsgBox "This asset has already been submitted, please choose another  "
                    Exit Sub
                End If
        End If
        
    Next ListItem
End With


End Sub

 

[RoryA]

Does it work like that?

 

[Courgettey]

Sadly not any ideas?

 

[RoryA]

Are you sure that is the correct name for the control? What appears in the left hand dropdown in the VB code editor?