Excel VBA Combobox

Question

I have a ComboBox in a userform, and I'm looking for a method to use to find what the selection is so that I can utilize that as a string and pass it to a cell on the sheet. For instance:

 Private Sub Filter_Click()
 'The Userform is called Summary, and the ComboBox in question is named Month_Filter
 If Summary.Month_Filter = "January" Then .......
 Call UpdatedTotals
 End Sub


 Sub UpdatedTotals()
 Dim ChosenDate as String
 If Summary.Month_Filter <> "" Then ChosenDate = Summary.Month_Filter.Value
 Range("A1").FormulaR1C1 = ChosenDate
 End Sub

ComboBox has a .Value property that should work, unless it's a multi-column combobox. — David Zemens
– David Zemens, Commented Aug 20, 2013 at 18:15

jerussell · Accepted Answer · 2013-08-20 18:18:23Z

1

The value of the combobox is accessible using:

Me.comboBoxName.Value

So something like:

If Me.comboBoxName.Value = "January" Then ....

would work.

However, this only applies to code written in the form's code (if that makes sense). You won't be able to use that code in a separate module as it won't know anything about Me.comboBoxName... If you want to use that value in a different module you would have to pass the value when calling that sub/function.

answered Aug 20, 2013 at 18:18

jerussell

1466 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

David Zemens Over a year ago

would have to pass the value when calling that sub/function -- not ncessarily. In many cases it's possible to refer to the form & control by name, e.g., strVar = UserForm1.ComboBox2.Value.

Jonh Over a year ago

what does "Me" refer to?

Dick Kusleika · Accepted Answer · 2013-08-20 18:22:04Z

1

The Value property will return the value of the BoundColumn for the selected list item.

The ListIndex property returns the index (starting at zero for the first item) of the selected list item. It can be used with Column to get the right value if the value you're looking for is not in BoundColumn.

ChosenDate = Summary.Month_Filter.Column(Summary.Month_Filter.ListIndex,1)

The second argument, 1, refers to the second column as it's zero-based just like the list items.

answered Aug 20, 2013 at 18:22

Dick Kusleika

33.3k4 gold badges56 silver badges81 bronze badges

Comments

DataSmarter · Accepted Answer · 2013-08-20 18:17:33Z

0

Try this:

Private Sub Filter_Click()
 If Summary.Month_Filter.Value = "January" Then .......
 Call UpdatedTotals
 End Sub


 Sub UpdatedTotals()
 If Summary.Month_Filter.Value <> "" Then Range("A1").Value = Summary.Month_Filter.Value
 End Sub

answered Aug 20, 2013 at 18:17

DataSmarter

1972 silver badges15 bronze badges

Collectives™ on Stack Overflow

Excel VBA Combobox

3 Answers 3

2 Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

2 Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related