Using items from VBA combo box (form control, inside worksheet) to run macros

Question

So what I wanted to achieve is when someone clicks a certain year inside the combo box (which is located near cell A1 in my worksheet), a macro runs according to the year that was clicked. I want the list items to be stored in VBA code itself, rather than selecting them on the worksheet (I haven't found a single tutorial about combo box that doesn't use worksheet data).

I have created a button to load the data into the ComboBox1.

This is the code for now (I'm not trying to stick to it strictly, so if there's an easier way to write this, please let me know):

Sub Button1_Click()
      Sheets("MacroBase").ComboBox1.List = Array("2015", "2016", "2017", "2018", "2019")    
End Sub

Sub ComboBox1_GotFocus()
    With ThisWorkbook.Sheets("MacroBase").Shapes("ComboBox1").ControlFormat
    Select Case .List(.Value)
        Case "2015": ShowOnly2015Columns
        Case "2016": ShowOnly2016Columns
        Case "2017": ShowOnly2017Columns
        Case "2018": ShowOnly2017Columns
        Case "2019": ShowOnly2019Columns
    End With
End Sub

I didn't paste the code for ShowOnly####Columns (tested and running without problems).

Currently the _GotFocus doesn't work.

I thank in advance for all your time and help :)

score 1 · Answer 1 · edited May 23 '17 at 10:33

1

There are 2 types of comboboxes.

See this question and answer. This should get you going.

Run Macro When ComboBox is Clicked

You can take the GotFocus event and fill in the combobox.

edited May 23 '17 at 10:33

Community

1
1

answered May 07 '16 at 08:25

ib11

2,530
3
22
55

Thank you for that, so I need to change "_Change" with "_GtFocus", but do you know why my combobox is still empty? How to load in the data using VBA? I don't want to choose the list data from the worksheet. – Alen Šimunic May 07 '16 at 08:32
I see you sorted this out since. So ask the new question about the `GotFocus` using the link http://stackoverflow.com/questions/ask and then post the link of the question here as a comment, so we can help you. – ib11 May 08 '16 at 03:47

score 1 · Accepted Answer · answered May 07 '16 at 08:56

1

use this

Sheets(1).Shapes("ComboBox1").ControlFormat.List = Array("2015", "2016", "2017", "2018", "2019")

be sure that Sheets(1) refers to the worksheet your "ComboBox1" combobox is actually in

it'd be safer to use sheet name like

Sheets("mySheet").Shapes("ComboBox1").ControlFormat.List = Array("2015", "2016", "2017", "2018", "2019")

finally, if you want to fill combobox with some worksheet range content than you'd use:

Sheets("mySheet").Shapes("ComboBox1").ControlFormat.ListFillRange = "'My Sheet'!A1:A5"

answered May 07 '16 at 08:56

user3598756

28,893
4
18
28

What I get when running that code (the one with array) is Run-time error 438: Object doesn't support this property or method. I have no idea whats happening here lol – Alen Šimunic May 07 '16 at 09:04
which line throws that error? you may want to update your actual code in the code pane question – user3598756 May 07 '16 at 09:11
I created a button to execute the following code: Sheets("MacroBase").Shapes("ComboBox1").ControlFormat.List = Array("2015", "2016", "2017", "2018", "2019") and that same line throws the error – Alen Šimunic May 07 '16 at 09:14
which "following" code? you'd better update the code in the question – user3598756 May 07 '16 at 09:15
Sorry, I'm quite new to SO :) – Alen Šimunic May 07 '16 at 09:21
click "edit" button (sort of...) which is below you actual code – user3598756 May 07 '16 at 09:24
I did. But you didn't answer the question **"which line throws that error?"** I guess it's not the one in `Sub Button1_Click()` ... – user3598756 May 07 '16 at 09:32
Yes, that line throws the error, the second line, precisely this code: Sheets("MacroBase").Shapes("ComboBox1").ControlFormat.List = Array("2015", "2016", "2017", "2018", "2019") I have no idea why – Alen Šimunic May 07 '16 at 09:37
are you working with different workbooks? perhaps one with the macro and another one with "Macrobase" sheet in it? – user3598756 May 07 '16 at 09:40
I am working in only one workbook that has only one worksheet in it ("MacroBase" worksheet)...I'm really confused about the reason why this is not working – Alen Šimunic May 07 '16 at 09:43
I also tried the same line of code on the ListBox1 and it didn't work either, so it has something to do with ControlFormat – Alen Šimunic May 07 '16 at 09:51
you must be using "ActiveX" controls instead of "Form" ones. Two choices: 1) delete your controls and reposition new ones choosing them from the "Form Controls" list (the upmost one when clicking _Developer->Insert_). and you can use the code as it is. 2) change code to `Sheets("MacroBase").ComboBox1.List = Array("4015", "2016", "2017", "2018", "4019")` and the likes. – user3598756 May 07 '16 at 10:06
you are welcome. out of curiosity: what was your choice: 1 or 2? – user3598756 May 07 '16 at 10:12
it was 2, I want to use ActiveX ComboBox :) now all that is left to be fixed is the part for GotFocus part. Do you have any experience there as well? – Alen Šimunic May 07 '16 at 10:27
not till now... besides you must start a new question with this issue only – user3598756 May 07 '16 at 10:31

Using items from VBA combo box (form control, inside worksheet) to run macros

2 Answers2