CODEWITHSUNDEEP
vbaexcelcombobox
Abeer
Abeer

Reputation: 9

Userform combobox take first value only - Excel VBA

I have a user form set up which contains a ComboBox. The ComboBox is populated with values from a dynamic named range "List1" .. every thing is Fine .. when I choose the first value from the list it works and I could complete my tasks .. but when I choose another value from the list the value disappears and the combobox value become empty .. here is the code :

Private Sub ComboBox1_Change()
ComboBox1.RowSource = "'[" & ActiveWorkbook.Name & "]DATA'!List1"
ComboBox1.DropDown
Worksheets("TEMP").Range("A3").Value = ComboBox1.Value
End Sub

Could anyone give me a solution please ..

UPDATE: I follow the instructions in this tutorial .. but I do it in a userform: http://trumpexcel.com/2013/10/excel-drop-down-list-with-search-suggestions

Upvotes: 1

Views: 2609

Answers (2)

Dirk Reichel
Dirk Reichel

Reputation: 7979

Main problem: the combobox will change its values by the selected item

select item 5 -> item 5 is selected (also if values change) If the source is changed (no item 5 exists or has an empty string) the box will be empty. You are NOT selecting a value! Try it like that:

Private Sub UserForm_Initialize()
  Dim ListCB As Variant, TmpText As String
  If Not IsArray(Sheets("Data").Range(Names("List1"))) Then
    temptext = Worksheets("TEMP").Range("A3").Value
    Worksheets("TEMP").Range("A3").Value = ""
  End If
  ListCB = Sheets("Data").Range(Names("List1"))
  If Len(temptext) Then Worksheets("TEMP").Range("A3").Value = temptext
  ComboBox1.List = ListCB
End Sub

ListCB will not change when an item is selected -> your combobox stays as it is.

EDIT:
Try it this way:

Dim ListCB As Variant

Private Sub UserForm_Initialize()
  Dim TmpText As String
  If Not IsArray(Sheets("Data").Range(Names("List1"))) Then
    temptext = Worksheets("TEMP").Range("A3").Value
    Worksheets("TEMP").Range("A3").Value = ""
  End If
  ListCB = Sheets("Data").Range(Names("List1"))
  If Len(temptext) Then Worksheets("TEMP").Range("A3").Value = temptext
  GetCBList
End Sub

Sub GetCBList()
  Dim b As Variant, i As Long
  Dim a() As Variant: ReDim a(UBound(ListCB))
  For Each b In ListCB
    If Len(b) Then
      If InStr(1, b, ComboBox1.Value, vbTextCompare) > 0 Or ComboBox1.Value = "" Then: a(i) = b: i = i + 1
    End If
  Next
  If i > 0 Then ReDim Preserve a(i - 1)
  ComboBox1.List = a
End Sub

Private Sub ComboBox1_Change()
  GetCBList
  ComboBox1.DropDown
  Worksheets("TEMP").Range("A3").Value = ComboBox1.Value
End Sub

Attention: Dim ListCB As Variant needs to be outside of the subs/functions (should be first line at all in this form). Replace Private Sub UserForm_Initialize() and Private Sub ComboBox1_Change() with this code and check if that is what you want.

Upvotes: 0

Darren Bartrup-Cook
Darren Bartrup-Cook

Reputation: 19782

I couldn't get it to display blank in cell A3 - it all worked, except I had to type in the combo box to get the values to appear (i.e. get the change event to fire). Also try changing ActiveWorkbook to ThisWorkbook (the book with the code in).

Try populating your combo box when you first open the form:

Private Sub UserForm_Initialize()
    ComboBox1.RowSource = "'[" & ActiveWorkbook.Name & "]DATA'!List1"
End Sub

Private Sub ComboBox1_Change()
    Worksheets("TEMP").Range("A3").Value = ComboBox1.Value
End Sub

Upvotes: 1

Related Questions