Checking values already in combo boxes

  • Thread starter Thread starter Dean Knox
  • Start date Start date
D

Dean Knox

The value I input into textbox1 I want to add into
combobox1. But first I want the code to check combobox1
first to see if the value exists, if it does then the code
stops, if not then add it in.

I have this already but am stuck:

If combobox1.? = textbox1.text Then
GoTo end
Else:
cmbRollNumber.AddItem (txtBAID.Text)
End If
 
Dean,

I haven't tested this, I just made it up off the top of my
head, but how about:

Dim X as integer
Dim InList as boolean

For X = 1 to Combobox1.ListCount
If ComboBox1.List(X,1) = TextBox1.value then
InList = True
End If
Next X

If InList = False

ComboBox1.AddItem (TextBox1.value)

End IF

Cheers, Pete
 
I'd use a dictionary - Microsoft Scripting Runtime - that
works as a collection except has an Exists method that
can be tested....

Here's some code behind a simple form that first
populates a combo from a named range.
The form has a combobox (comboBox1) and a command button
(cmdProcess)
It also loads a dictionary. The initialise event calls
the same sub to load the form as the button.

when the process command is clicked, the value of whats
in the combo is also passed to the Load_Combo procedure.
This procedure adds the passed value to the dictionary
and combo if its not there already

Option Explicit

Private myDic As Scripting.Dictionary

Private Sub UserForm_Initialize()
Set myDic = New Scripting.Dictionary
Dim cell As Range
Dim Text As String
For Each cell In ThisWorkbook.Names("MyData")
Text = cell.Value
Load_Combo Text
Next
End Sub
Private Sub Load_Combo(Text As String)
If Not myDic.Exists(Text) Then
myDic.Add Text, Text
ComboBox1.AddItem Text
End If
End Sub

Private Sub cmdProcess_Click()
Load_Combo ComboBox1.Value
End Sub

The code is relatively easy to follow

Patrick Molloy
Microsoft Excel MVP
 
Back
Top