I'm using a UserForm with a Combo Box that displays the numbers 1-10 as
choices. I need a different macro to fire based on whatever number is
selected. I've tried many of the solutions offered by previous posts,
but I can't make it work. i.e.: Should the user select "1", then the
macro "makeman1" will execute and so forth through "10".

Thanks in advance. Please be specific in any reply about how the
syntax should be.

You may use call and a select statement, something like:
Select Case combo
Case 1
Call makeman1
Case 2
Call makeman2
End Select
You will need to fill it up to 10, and choose the right variable name.

Hope this helps,
Miguel.

"michaelberrier" wrote:

I'm using a UserForm with a Combo Box that displays the numbers 1-10 as
choices. I need a different macro to fire based on whatever number is
selected. I've tried many of the solutions offered by previous posts,
but I can't make it work. i.e.: Should the user select "1", then the
macro "makeman1" will execute and so forth through "10".

Thanks in advance. Please be specific in any reply about how the
syntax should be.

Thanks for looking.

I've tried that, but it calls the same macro (Makeman3, in this case)
regardless of which number I select, and it always fails, even though
the macro works fine if triggered manually or from a button.

Code is below:
Private Sub ComboBox1_Click()
Select Case ComboBox1.ListIndex
Case 0
Call Makeman1
Case 1
Call Makeman2
Case 2
Call Makeman3
Case 3
Call Makeman4
Case 4
Call Makeman5
Case 5
Call makeman6
Case 6
Call makeman7
Case 7
Call makeman8
Case 8
Call makeman9
Case 9
Call makeman10
End Select
End Sub

I put these in a general module:
Option Explicit
Sub MakeMan1()
MsgBox "hi from 1"
End Sub
Sub MakeMan2()
MsgBox "hi from 2"
End Sub
Sub MakeMan3()
MsgBox "hi from 3"
End Sub
Sub MakeMan4()
MsgBox "hi from 4"
End Sub
Sub MakeMan5()
MsgBox "hi from 5"
End Sub
Sub MakeMan6()
MsgBox "hi from 6"
End Sub
Sub MakeMan7()
MsgBox "hi from 7"
End Sub
Sub MakeMan8()
MsgBox "hi from 8"
End Sub
Sub MakeMan9()
MsgBox "hi from 9"
End Sub
Sub MakeMan10()
MsgBox "hi from 10"
End Sub

I put this behind the userform:

Option Explicit
Private Sub ComboBox1_Change()
If Me.ComboBox1.ListIndex < 0 Then
Exit Sub
Else
Application.Run "MakeMan" & Me.ComboBox1.ListIndex + 1
End If
End Sub
Private Sub UserForm_Initialize()
Dim iCtr As Long
For iCtr = 1 To 10
Me.ComboBox1.AddItem iCtr
Next iCtr
End Sub


And it worked ok for me.

Just a thought...

As a user, I often screw up and choose the wrong item from a
listbox/combobox/checkbox/optionbutton, well every control there is! I think
I'd want to see an Ok button that does the real work--instead of using the
_click event.

But that's just me...

michaelberrier wrote:

I'm using a UserForm with a Combo Box that displays the numbers 1-10 as
choices. I need a different macro to fire based on whatever number is
selected. I've tried many of the solutions offered by previous posts,
but I can't make it work. i.e.: Should the user select "1", then the
macro "makeman1" will execute and so forth through "10".

Thanks in advance. Please be specific in any reply about how the
syntax should be.

--

Dave Peterson

Dave,
works well except for this:

Private Sub UserForm_Initialize()
Dim iCtr As Long
For iCtr = 1 To 10
Me.ComboBox1.AddItem iCtr
Next iCtr
End Sub

With this in the userform code module, I cannot show the userform, it
says "Permission Denied".
?
Thanks.

Dave Peterson wrote:
I put these in a general module:
Option Explicit
Sub MakeMan1()
MsgBox "hi from 1"
End Sub
Sub MakeMan2()
MsgBox "hi from 2"
End Sub
Sub MakeMan3()
MsgBox "hi from 3"
End Sub
Sub MakeMan4()
MsgBox "hi from 4"
End Sub
Sub MakeMan5()
MsgBox "hi from 5"
End Sub
Sub MakeMan6()
MsgBox "hi from 6"
End Sub
Sub MakeMan7()
MsgBox "hi from 7"
End Sub
Sub MakeMan8()
MsgBox "hi from 8"
End Sub
Sub MakeMan9()
MsgBox "hi from 9"
End Sub
Sub MakeMan10()
MsgBox "hi from 10"
End Sub

I put this behind the userform:

Option Explicit
Private Sub ComboBox1_Change()
If Me.ComboBox1.ListIndex < 0 Then
Exit Sub
Else
Application.Run "MakeMan" & Me.ComboBox1.ListIndex + 1
End If
End Sub
Private Sub UserForm_Initialize()
Dim iCtr As Long
For iCtr = 1 To 10
Me.ComboBox1.AddItem iCtr
Next iCtr
End Sub


And it worked ok for me.

Just a thought...

As a user, I often screw up and choose the wrong item from a
listbox/combobox/checkbox/optionbutton, well every control there is! I think
I'd want to see an Ok button that does the real work--instead of using the
_click event.

But that's just me...

michaelberrier wrote:

I'm using a UserForm with a Combo Box that displays the numbers 1-10 as
choices. I need a different macro to fire based on whatever number is
selected. I've tried many of the solutions offered by previous posts,
but I can't make it work. i.e.: Should the user select "1", then the
macro "makeman1" will execute and so forth through "10".

Thanks in advance. Please be specific in any reply about how the
syntax should be.

--

Dave Peterson

That's the way I initialized the combobox. Did you use a range that held 1-10?

You could drop that portion and use the .rowsource you used before--or you could
drop the rowsource (manually in design mode) or even set things up in code:

Private Sub UserForm_Initialize()
Dim iCtr As Long
Me.ComboBox1.RowSource = ""
Me.ComboBox1.Clear
For iCtr = 1 To 10
Me.ComboBox1.AddItem iCtr
Next iCtr
End Sub



michaelberrier wrote:

Dave,
works well except for this:

Private Sub UserForm_Initialize()
Dim iCtr As Long
For iCtr = 1 To 10
Me.ComboBox1.AddItem iCtr
Next iCtr
End Sub

With this in the userform code module, I cannot show the userform, it
says "Permission Denied".
?
Thanks.

Dave Peterson wrote:
I put these in a general module:
Option Explicit
Sub MakeMan1()
MsgBox "hi from 1"
End Sub
Sub MakeMan2()
MsgBox "hi from 2"
End Sub
Sub MakeMan3()
MsgBox "hi from 3"
End Sub
Sub MakeMan4()
MsgBox "hi from 4"
End Sub
Sub MakeMan5()
MsgBox "hi from 5"
End Sub
Sub MakeMan6()
MsgBox "hi from 6"
End Sub
Sub MakeMan7()
MsgBox "hi from 7"
End Sub
Sub MakeMan8()
MsgBox "hi from 8"
End Sub
Sub MakeMan9()
MsgBox "hi from 9"
End Sub
Sub MakeMan10()
MsgBox "hi from 10"
End Sub

I put this behind the userform:

Option Explicit
Private Sub ComboBox1_Change()
If Me.ComboBox1.ListIndex < 0 Then
Exit Sub
Else
Application.Run "MakeMan" & Me.ComboBox1.ListIndex + 1
End If
End Sub
Private Sub UserForm_Initialize()
Dim iCtr As Long
For iCtr = 1 To 10
Me.ComboBox1.AddItem iCtr
Next iCtr
End Sub


And it worked ok for me.

Just a thought...

As a user, I often screw up and choose the wrong item from a
listbox/combobox/checkbox/optionbutton, well every control there is! I think
I'd want to see an Ok button that does the real work--instead of using the
_click event.

But that's just me...

michaelberrier wrote:

I'm using a UserForm with a Combo Box that displays the numbers 1-10 as
choices. I need a different macro to fire based on whatever number is
selected. I've tried many of the solutions offered by previous posts,
but I can't make it work. i.e.: Should the user select "1", then the
macro "makeman1" will execute and so forth through "10".

Thanks in advance. Please be specific in any reply about how the
syntax should be.

--

Dave Peterson

--

Dave Peterson

Dave,
Absolutely PERFECT advice. Thanks again.
Dave Peterson wrote:
That's the way I initialized the combobox. Did you use a range that held 1-10?

You could drop that portion and use the .rowsource you used before--or you could
drop the rowsource (manually in design mode) or even set things up in code:

Private Sub UserForm_Initialize()
Dim iCtr As Long
Me.ComboBox1.RowSource = ""
Me.ComboBox1.Clear
For iCtr = 1 To 10
Me.ComboBox1.AddItem iCtr
Next iCtr
End Sub



michaelberrier wrote:

Dave,
works well except for this:

Private Sub UserForm_Initialize()
Dim iCtr As Long
For iCtr = 1 To 10
Me.ComboBox1.AddItem iCtr
Next iCtr
End Sub

With this in the userform code module, I cannot show the userform, it
says "Permission Denied".
?
Thanks.

Dave Peterson wrote:
I put these in a general module:
Option Explicit
Sub MakeMan1()
MsgBox "hi from 1"
End Sub
Sub MakeMan2()
MsgBox "hi from 2"
End Sub
Sub MakeMan3()
MsgBox "hi from 3"
End Sub
Sub MakeMan4()
MsgBox "hi from 4"
End Sub
Sub MakeMan5()
MsgBox "hi from 5"
End Sub
Sub MakeMan6()
MsgBox "hi from 6"
End Sub
Sub MakeMan7()
MsgBox "hi from 7"
End Sub
Sub MakeMan8()
MsgBox "hi from 8"
End Sub
Sub MakeMan9()
MsgBox "hi from 9"
End Sub
Sub MakeMan10()
MsgBox "hi from 10"
End Sub

I put this behind the userform:

Option Explicit
Private Sub ComboBox1_Change()
If Me.ComboBox1.ListIndex < 0 Then
Exit Sub
Else
Application.Run "MakeMan" & Me.ComboBox1.ListIndex + 1
End If
End Sub
Private Sub UserForm_Initialize()
Dim iCtr As Long
For iCtr = 1 To 10
Me.ComboBox1.AddItem iCtr
Next iCtr
End Sub


And it worked ok for me.

Just a thought...

As a user, I often screw up and choose the wrong item from a
listbox/combobox/checkbox/optionbutton, well every control there is! I think
I'd want to see an Ok button that does the real work--instead of using the
_click event.

But that's just me...

michaelberrier wrote:

I'm using a UserForm with a Combo Box that displays the numbers 1-10 as
choices. I need a different macro to fire based on whatever number is
selected. I've tried many of the solutions offered by previous posts,
but I can't make it work. i.e.: Should the user select "1", then the
macro "makeman1" will execute and so forth through "10".

Thanks in advance. Please be specific in any reply about how the
syntax should be.

--

Dave Peterson

--

Dave Peterson