Trigger ActiveX ComboBox to display the drop down list VBA

[julhs]

I have an ActiveX ComboBox dropdown list.
What I’m trying to do with code is simulate the physical action of clicking on the ComboBox dropdown arrow to reveal the list.
This is what I have to-date, BUT surprise surprise it is not doing what I wanted.

Public Sub SelectGetSubList()
Application.SendKeys "{NUMLOCK}", False   'As User preference is set as TRUE
ActiveSheet.Shapes("GetSubList").Select
With Selection
.Enabled = True
.Activate
End With
Application.SendKeys "%{DOWN}"
End Sub

Is the code even remotely close or is what I’m trying to just do not possible?
Not going to bore you with ALL error codes but ALMOST everything I have tried has come up with “Error 438” message, application doesn’t support it.

 

[Bosquedeguate]

I have an ActiveX ComboBox dropdown list.
What I’m trying to do with code is simulate the physical action of clicking on the ComboBox dropdown arrow to reveal the list.
This is what I have to-date, BUT surprise surprise it is not doing what I wanted.

Public Sub SelectGetSubList()
Application.SendKeys "{NUMLOCK}", False   'As User preference is set as TRUE
ActiveSheet.Shapes("GetSubList").Select
With Selection
.Enabled = True
.Activate
End With
Application.SendKeys "%{DOWN}"
End Sub

Is the code even remotely close or is what I’m trying to just do not possible?
Not going to bore you with ALL error codes but ALMOST everything I have tried has come up with “Error 438” message, application doesn’t support it.

is your combobox on a worksheet or in a UserForm?

 

[julhs]

Sorry for not stating it is on the worksheet.

 

[Akuini]

Try something like this:

Sheets("Sheet1").ComboBox1.DropDown

 

[julhs]

Try something like this:

Sheets("Sheet1").ComboBox1.DropDown

Made some changes and used your suggestion but it’s still coming back with “Error 438”
If it makes any difference; in design mode in the formula bar I am seeing:-

=EMBED("Forms.ComboBox.1","")

And in the name bar, seeing:-

GetSubList

 

[Akuini]

I don't understand what your code does.
Try it on a clean sheet first.

 

[Georgiboy]

If you are seeing that in the name bar then try the below instead:

Sheets("Sheet1").GetSubList.DropDown

 

[julhs]

With the combination of Akuini suggestion of “Try it on a clean sheet first”
and Geogiboy suggestion “Sheets("Sheet1").GetSubList.DropDown”
I have managed to get the ActiveX Combobox DropDown list displayed, “AND” remain “Active/Visible” WHEN
the code below gets to “End Sub”
This should enable me to fit the code into my “Main Sub” code????
Whether or not I can refine the code further is yet to be tested, but it has given me something to work with.

Public Sub GetSubList() 'This would need changing to fit own requirements
Dim sht As Worksheet
Set sht = ThisWorkbook.ActiveSheet
sht.Shapes("GetSubList").Select 'This would need changing to fit own requirements
With Selection
.Enabled = True
.Activate
End With
Sheets("sheet2").GetSubList.DropDown "Sheet2"   ' This would need changing to fit own requirements
End Sub

 

[julhs]

Just want to share the final outcome
I wanted a way to use code to mimic the physical action of clicking on the Dropdown to reveal the list
The intention is to integrate the code below into my “Main Sub” so I can eliminate several lines of code in it and a couple of clicks of the “Drop Down”

Book1.xlsm
	A	B	C	D	E	F	G	H	I	J
1
2
3			cmd button “Click here to Select the Sub to Run” should be here if XL2BB would allowed it
4								List Fill Range for Drop Down list
5
6
7			"Sub List" will appear here when you click button "Click here to Select Sub to Run"					Run Sub "Paid"
8								Run Sub "Repaid"
9								Run Sub "No Payment"
10
11								Exit Sub
12
13				D14 is linked cell
14				Run Sub "Repaid"
15
16
17		The code that does all this will be integrated into my "Main Sub". So the Sub that appears in the Linked Cell will be called for via case statements in the Sheet Change Event
18
19
20
21
22
23
24
Test ActiveX

Public Sub cmdClickToSelectSubToRun_Click()
Dim sht As Worksheet

    Set sht = ThisWorkbook.ActiveSheet
        '("GetSubList") is name of ActiveX Dropdown
      sht.Shapes("GetSubList").Visible = True 'Needed as it's Set to FALSE in its Properties
      sht.Shapes("GetSubList").Select
    Sheets("Test ActiveX").GetSubList.DropDown
End Sub

Sorry for resorting to picture images, but Mini sheet didn’t want to show the cmd button “Click here to Select the Sub to Run” that has the code assigned to it.