Running a macro from combobox selection

[joshfishtail]

Hello there,

So this is my first attempt at using VB, besides using record to create a macro, and I am having issues.

I have an ActiveX ComboBox with a list having the values: 25 Stages, 26 Stages, 27 Stages......38 Stages.
I would like for a specific macro to run when I select each option in the combobox. I have recorded these macros and named them Stages_25, Stages_26, so on and so forth.
When I select 25 Stages in the combobox I would like for the macro Stages_25 to run.

Can anyone help me with this?

I right clicked the combobox (ComboBox1) and entered this in to try to see if I can get the first one to run;


Private Sub ComboBox1_Change()
Select Case ComboBox1.Value
Case "25%Stages"
Call Stages_25
End Select

End Sub


Keep in mind I am completely virgin to VB, Thanks!!

Josh

[Tinbendr]

Welcome to the board!

You say you have values like 25 Stages, but your Case is looking for "25%Stages". They have to match exactly, even letter case.

Add a breakpoint to the Select Case line and step through it to see what's going on.

[joshfishtail]

I changed it to "25 Stages" and it worked! From looking at other code I had assumed you put the % in place of spaces. I'll go through and try to add all the other items in and let you know if I have any more issues.

Thanks, David

[joshfishtail]

Okay, now it is giving me an error after adding in my other commands.

The error is- Compile error: Sub or Function not defined

This is what I have,


Private Sub ComboBox1_Change()

Select Case ComboBox1.Value
Case "25 Stages"
Call Stages_25
Case "26 Stages"
Call Stages_26
Case "27 Stages"
Call Stages_27
Case "28 Stages"
Call Stages_28
Case "29 Stages"
Call Stages_29
Case "30 Stages"
Call Stages_30
Case "31 Stages"
Call Stages_31
Case "32 Stage"
Call Stages_32
Case "33 Stages"
Call Stages_33
Case "34 Stages"
Call Stages_34
Case "35 Stages"
Call Stages_35
Case "36 Stages"
Call Stages_36
Case "37 Stages"
Call Stages_37
End Select

End Sub




It highlights the Private Sub line when giving me the error.

Thanks

[Tinbendr]

Press F8 when it stops and see if you can progress any further in the code.

[joshfishtail]

When pressing F8 it continuously pops up the same error message.

[Tinbendr]

And is the combo box named Combobox1? If not, go into design mode and double click the combobox. Then copy all of the code into that sub.

[Norie]

What are the actual names of the subs and where are they located?

By the way, what do the subs do?

Are they basically the same apart from the no of stages involved?

[joshfishtail]

I got it all working, David. Thanks a lot for your help!

[joshfishtail]

My subs were basically reformatting a table of data. Deleting the extra data when those stages weren't included, drag and dropping cells, and then reformatting the borders to fit. Got it working now!

Thanks

[Norie]

You could probably do everything with one sub, passing the no of stages as an argument to it.