Solved How do I reference the value in a combo box in VBA macro

July 14, 2016 at 18:18:55
Specs: Windows 7
I have various pieces of code that reference a the value in a cell but I want to replace the cell that has a drop down list with a combo box. I normally would just reference the value using the range command but not sure how to reference the value from the combo box. Here is the code I currently use

ActiveSheet.Range("$A$6:$AQ$1000").AutoFilter Field:=1, Criteria1:=Range("F4")

I want to replace F4 with the value of the combo box


See More: How do I reference the value in a combo box in VBA macro

Report •


#1
July 15, 2016 at 04:09:15
I've never coded VBA for combo boxes, but have you tried this?

Criteria1:=ComboBox1.Value

BTW, I believe you are asking about a VBA macro, not VB. Visual Basic and Visual Basic for Applications are 2 different coding languages.

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.


Report •

#2
July 15, 2016 at 04:27:26
I did try that and it didn't work, thanks for the clarification on the VB vs VBA part. I will continue my googling on the combobox.

Report •

#3
July 15, 2016 at 09:32:08
✔ Best Answer
I was able to do it from another person that recommended this code to add:

Dim mysht As Worksheet
Dim myDropDown As Shape
Dim myVal As String

Set mysht = ThisWorkbook.Worksheets("Pipeline")
Set myDropDown = mysht.Shapes("Drop Down 261")

myVal = myDropDown.ControlFormat.List(myDropDown.ControlFormat.Value)
ActiveSheet.Range("$A$6:$AQ$1000").AutoFilter Field:=1, Criteria1:=myVal


Report •

Related Solutions

#4
July 15, 2016 at 09:39:11
Thank you for posting the solution. I will mark the thread as Solved with your solution set as Best Answer.

I was playing around and noticed that recording a macro while creating a Forms Control ComboBox actually wrote code referencing a "Drop Down", not a ComboBox.

I don't recall it always being that way, but it's been a very long time since I tried anything like that.

In any case, I'm glad you found a solution.

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.

message edited by DerbyDad03


Report •

Ask Question