PDA

View Full Version : Solved: Entering Value in Combobox with Dynamic Range



Ethan
07-06-2011, 11:48 PM
Hello all,

I use the following code to fill a Combobox with data stored somewhere else.
It works fine but I like to be able to fill in a value that isn't in the list already.
Do you guys have an idea?

Private Sub UserForm_Initialize()
Dim I As Range
Dim ws As Worksheet

Set ws = Worksheets("Data")
For Each I In ws.Range("Data")
Me.ComboBox1.AddItem I.Value
Next I
End Sub


Private Sub ComboBox1_Change()
If ComboBox1.ListIndex > -1 Then
Exit Sub
Else
MsgBox ComboBox1 & " Not Found"
End If
End Sub

mikerickson
07-07-2011, 12:03 AM
Perhaps this will do what you want
Private Sub ComboBox1_AfterUpdate()
With ComboBox1
If .ListIndex = -1 Then
If .Text <> vbNullString Then
.AddItem .Text
End If
End If
End With
End Sub

Ethan
07-07-2011, 12:15 AM
Works fine, thanks Mike!