Combo box question

[rjc4]

Hi All,
How can I select and run other macros from a combo box.

Thankyou,
R

 

[erik.van.geit]

Hi, rjc4,

try this
Run ComboBox1.Value

I suppose you know there is an inbuilt function for the purpose ?
Tools/macro/macros ==> select and click "run"
kind regards,
Erik

 

[HalfAce]

Here's a way you can do that.
Using an activeX combobox, you can list your macro names somewhere on the sheet and use that range as the ListFillRange for the ComboBox.
Then in your combobox code you can just use this:

Private Sub ComboBox1_Change()
Application.Run ComboBox1.Text
End Sub

That what you had in mind?

 

[rjc4]

Hello Erik,
Thanks for that but I'm confused.
I can see the function which lists all my macros but I have many macro buttons on my sheet and wish to tidy it up by just having one combo box with all the macros listed. When you say Run ComboBox1.Value where and how do I run it. HalfAce gave me a solution which is close to what I need but I have a problem with it and I'm replying to him separately.
All this is a bit beyond my skill.
Kind regards,
RC

Hi, rjc4,

try this
Run ComboBox1.Value

I suppose you know there is an inbuilt function for the purpose ?
Tools/macro/macros ==> select and click "run"
kind regards,
Erik

 

[rjc4]

Hello HalfAce,
Am I doing something wrong?
I created a combobox. (What do you mean by activeX combobox?)

Here it is:-
Private Sub ComboBox7_Change()
Application.Run ComboBox7.Text
End Sub

Then I listed a couple of my macro names like this in AL12 down:-
Test1()
Test2()
Test3()
and referred to this range in the ListFill section of the combobox properties.

Then selected from the combobox. It selects them but won't run the entire macro. It runs one line of code and inserts a zero at an odd place on the spreadsheet. I don't understand what it is doing or whether I have set it up correctly. The line of code it runs is about the 13th line of code and it just puts the value of that line (it is a formula) at a position on the sheet as 0.

What you have suggested is exactly what I'm trying to do but can you see why it is behaving this way.

Thanks and regards,
RC

Here's a way you can do that.
Using an activeX combobox, you can list your macro names somewhere on the sheet and use that range as the ListFillRange for the ComboBox.
Then in your combobox code you can just use this:

Private Sub ComboBox1_Change()
Application.Run ComboBox1.Text
End Sub

That what you had in mind?

 

[HalfAce]

What I mean by activeX combobox is simply a combobox from the Controls toolbar, and not one from the Forms toolbar.

I don't know off hand why it would behave like that unless it has something to do with the code that's being called. here's an easy way to test how it's supposed to work.
In a standard module, paste these simple routines.

Sub Macro1()
[A1] = "Mac - 1"
End Sub

Sub Macro2()
[A2] = "Mac - 2"
End Sub

Sub Macro3()
[A3] = "Mac - 3"
End Sub

Sub Macro4()
[A4] = "Mac - 4"
End Sub

Next, enter "Macro1" in J1, "Macro2" in J2, "Macro3" in J3 & "Macro4" in J4.
Then Set the ListFillRange as J1:J4.

Now, (making sure you've exited design mode - the toolbar button that has the pencil, ruler & blue triangle on it), when you make a selection from the combobox dropdown, you should see the results in the range A1:A4.

Does this work for you so far?

 

[rjc4]

Hi HalfAce,

Thanks for the response.
Tried that but no go.

This is what I did.
Inserted a new worksheet.
Went to VBA tools and inserted a new Module.
Pasted the code you gave into that module.
Used Controls toolbar and created a new combobox.
Entered Macro1-4 in J1:J4.
Set ListFillRange to J1:J4.

The combobox shows the listing of entries in J1:J4 and I can select any.

Nothing after. No entries in A1:A4.

Got me stumped. When you say "in a standard module, did I do that correctly as above outline?"

Also thanks for clarification on activeX question. I didn't know that.

Cheers,
RC

 

[HalfAce]

When you say "in a standard module, did I do that correctly as above outline?"

Yep. Sounds like everything you've mentioned was done right.
Only thing I see that's missing is the code for the combobox itself. Without that nothing is going to happen when a selection is made.
If this hasn't been done then right click the sheet tab, choose View code and paste this in there.

Private Sub ComboBox1_Change()
Run ComboBox1.Text
End Sub

If this doesn't work then we'll have to dig a little deeper.

 

[rjc4]

Hi again HalfAce,

You were right. I didn't have the code in the ComboBox. But still no luck.

This is the combobox code I have.

Private Sub ComboBox1_Change()
Run ComboBox1.Text
End Sub

When I click on a selection from the combobox, I get "error 1004. The macro Macro1 cannot be found".

So to recap:
In the VBA module, I have:-
Under MS Excel Objects is:-
Sheet4(Sheet15) and the combobox code as above is in here.
Sheet15 was the number Excel gave to my new inserted sheet.

Also in the VBA under Modules is- Module29 containing the 4 macro subs.

On the s/s J1:J4 has Macro1, Macro2 etc up to 4.
The combobox on the sheet shows the 4 macros in the dropdown and inserts the selected one.

When the code crashes, it finishes at the Run ComboBox1.Text line which is highlighted. I also changed the line to read Application.Run ComboBox1.Text but with the same error result.

I feel it's something I've screwed up but can't see the wood for the trees.

Can you see anything I might have missed.

Cheers
RC

 

[Tom Urtis]

Hope you guys don't mind me chiming in.

rjc4 - -

You wrote:
"On the s/s J1:J4 has Macro1, Macro2 etc up to 4."

Do you really have those text values in J1:J4 as you wrote. I'm suspicious because in your third post on this thread, you wrote:

Then I listed a couple of my macro names like this in AL12 down:-
Test1()
Test2()
Test3()


So, in J1:J4 did you enter as you should have to avoid the 1004 error...
Macro1
Macro2
Macro3
Macro4

...or did you really enter
Macro1()
Macro2()
Macro3()
Macro4()

...which if you did would create the 1004 error.

Take a close look at exactly how you entered those macro names in J1:J4, and take out the parentheses if they are there.

If that is not the reason, something else is going on with your workbook but let's first see if it's a simple issue of how the macro names are spelled in the cells, must be exact without the parentheses.