How do i retrieve the values from a combo box control using VBA?

[keithaul]

I have a user form where the user must type in a new value into the combobox control on the form. I want to be able to validate the entry to make sure it doesn't already exist in the list. There is a add button that adds the new value to the list

So I'm trying to add code behind the 'add' button to compare the entry made by the user to the values already in the combo box list. However I'm unsure how to extract the values in the list from the combo box and then compare those values to the value the user entered in the combo box. The following code is started but I don't know the syntax to get the values from the list to compare:

For Each cCatg In ws.Range("CategoryList")
With Me.cboCatgList

If Trim(Me.cboCatgList.Value) = *** what do I put here **** Then
Me.cboCatgList.SetFocus
MsgBox "This value already exists"
Exit Sub
End If
End With
Next cCatg

 

[Trebor76]

Hi keithaul,

Here's one possibility:

On Error Resume Next
    'The following message will only appear if the Me.CatgList.Value is matched in the first column of the 'CategoryList' named range.
    'Change to suit if necessary.
    MsgBox Evaluate("VLOOKUP(""" & Me.CatgList.Value & """,CategoryList,1,FALSE)") & " has already been entered.", vbExclamation
    If Err.Number <> 0 Then
        'Code here to add record as it doesn't exist in the list
    End If
On Error GoTo 0

Regards,

Robert

 

[keithaul]

Thank you very much, Robert. This solution worked. I learned something new.

Keith

 

[Trebor76]

Hi Keith,

Thanks for the feedback and you're welcome

Regards,

Robert