Can't acess ComboBox in VBA

0
votes

I've created a combobox in an Excel sheet and named it "GraphChoice". It's located in a sheet named "Choose Graph"

In VBA I'm trying to fill it with data using this code:

Sub Choose_graph_and_date()

Dim Graph As Worksheet
Dim FormInfo As Worksheet
Set Graph = Worksheets("Choose Graph")
Set FormInfo = Worksheets("Forminfo")
Graph.Activate

Graph.GraphChoice.List = FormInfo.Range("A1:A3").Value

End Sub

I get an error saying Can't find method. I've also tried.

GraphChoice.List = FormInfo.Range("A1:A3").Value

Then I get error 424. Object Required.

The code is in the ThisWorkbook module since I want it to load every time you open the workbook.

Anyone knows whats wrong?

2
excelvba
There is a difference between the Name property and the (name) property. Which property did you update to GraphChoice? – Samuel Everson
Is GraphChoice the name of the combobox? Is it an ActiveX control or a Form control? – JNevill
I selected it in the excel sheet and renamed it using the textbox in the top left. The one that normally shows the cell selected. – Jens
It's a Form Control. GraphChoice is the name. – Jens
Enter Design Mode (on the developer tab), right click the combobox and choose properties. Then update the (Name) property to GraphChoice. – Samuel Everson

2 Answers

3
votes

For a form control, you can use the DropDowns collection...

Graph.DropDowns("GraphChoice").List = FormInfo.Range("A1:A3").Value

Or, you can use the Shapes collection...

Graph.Shapes("GraphChoice").ControlFormat.List = FormInfo.Range("A1:A3").Value

For an ActiveX control, you can refer to it using the OleObjects collection...

Graph.OLEObjects("GraphChoice").Object.List = FormInfo.Range("A1:A3").Value

Or, you can use the Shapes collection...

Graph.Shapes("GraphChoice").OLEFormat.Object.Object.List = FormInfo.Range("A1:A3").Value

Actually, you can also refer to it using the code name for your sheet. So, for example, let's say that the code name for your sheet is Sheet2, you could do the following...

Sheet2.GraphChoice.List = FormInfo.Range("A1:A3").Value
0
votes

My understanding is that this issue exists, because ActiveX Controls have a different kind of relation (hence referencing) than other common objects (such as cells) towards the sheets. You can solve your problem by either using:

Sheets("Choose Graph").GraphChoice.List = FormInfo.Range("A1:A3").Value

or by changing the object properties of the sheet with your graph to e.g. myGraphSheet and then using:

 myGraphSheet.GraphChoice.List = FormInfo.Range("A1:A3").Value

Also if you want the ComboBox to be filled everytime you open it, you need to place your code in ThisWorkbook-Module (as you did) and in a sub with the following name Workbook_Open() :

Sub Workbook_Open()
   'code executed when opened
End Sub