Data Validation: Userforms & Combo Boxes (97 SR2)

Topic

New Reply

I’ve got several combo boxes on my userforms that use specific row sources. My question is, if an item the user enters is not in the combo box, is there any way to place it in a separate column?

For example, if the user types in an office that is not in my list, can I pull this and put it into another column?

Any help would be greatly appreciated!

Reply | Quote

Replies

Hi Drk,

Not entirely sure what you’re asking.

>>can I pull this

You can compare new item to combo list and if not found…

>> and put it into another column?

if test is true, you can designate the

Cells(myRow, myCol) = cboBox.Text

Reply | Quote

I’m missing everything but the if not in row source statement…

Sub combobox_afterupdate() ‘<=- I assume this is the right syntax…
'Comparison statement here…
combobox.value = range("A" & i).value
end sub

I represents the current row, and is publicly declared and assigned elsewhere in the code.

Any help would be greatly appreciated!

Reply | Quote

Hi Drk,

Sorry, I must be a bit confused, but I think you want
to compare the typed value in the Combobox to the
combobox list?

Try this code:

Private Function AlreadyListed(ListControl As Control, NewItem As String) As Boolean
Dim i As Integer

AlreadyListed = False

For i = 0 To ListControl.ListCount – 1
If UCase$(ListControl.List(i)) = UCase$(NewItem) Then AlreadyListed = True
Next i
End Function

Private Sub cboOffice_AfterUpdate()
If Not AlreadyListed(cboOffice, cboOffice.Text) Then
‘Do something here
MsgBox “Not in the list”
End If
End Sub

Reply | Quote