Hi all,

I have a list box that has the numbers 1 - 30 in this list. I would like to
have a particular macro run for each individual number.

How do I get a macro to run for a certain number using a listbox, or should
I be using something else?

Thanks

Greg

Greg,
Try the following which assumes there are 30 different macros
called macro1 to macro30.

Private Sub ListBox1_Click()
Application.Run "macro" & ListBox1.Value
End Sub

An alternative is to use the Case statement

Select Case Listbox1.value
Case is=1
Call FirstMacro
Case is=2
Call SecondMacro
........
End Select

If the same macro is called for several listbox numbers, these can be
combined in the case statement e.g.

Case is = 3 ,7,15
Call MacroX


HTH

"Greg" wrote:

Hi all,

I have a list box that has the numbers 1 - 30 in this list. I would like to
have a particular macro run for each individual number.

How do I get a macro to run for a certain number using a listbox, or should
I be using something else?

Thanks

Greg

a simple method would be to ensure that your sub have similar names eg
MySub01
MySub02
....
MySub31

then you'd just need one line

Call "MySub" & Format$(MyListBox.Value,"00")

Otherwise, the Select Case method is worth trying

SELECT CASE MyListBox.Value
CASE 1,5,8 : Call ThisSUb
CASE 2 : Call ThisOtherSub
CASE 3,12,15: Call YetAnotherSub
CASE ELSE : msgbox "oops !",,"missing sub: " & MyListBox.Value
END SELECT



"Greg" wrote:

Hi all,

I have a list box that has the numbers 1 - 30 in this list. I would like to
have a particular macro run for each individual number.

How do I get a macro to run for a certain number using a listbox, or should
I be using something else?

Thanks

Greg

Patrick,
When I try this :

Private Sub ListBox1_Click()
Call "MySub" & Format$(ListBox1.Value,"00")
End Sub

I get an error message "expected identifier" which is why is used
Application.Run in my response. What am I doing wrong?

TIA.
"Patrick Molloy" wrote:

a simple method would be to ensure that your sub have similar names eg
MySub01
MySub02
...
MySub31

then you'd just need one line

Call "MySub" & Format$(MyListBox.Value,"00")

Otherwise, the Select Case method is worth trying

SELECT CASE MyListBox.Value
CASE 1,5,8 : Call ThisSUb
CASE 2 : Call ThisOtherSub
CASE 3,12,15: Call YetAnotherSub
CASE ELSE : msgbox "oops !",,"missing sub: " & MyListBox.Value
END SELECT



"Greg" wrote:

Hi all,

I have a list box that has the numbers 1 - 30 in this list. I would like to
have a particular macro run for each individual number.

How do I get a macro to run for a certain number using a listbox, or should
I be using something else?

Thanks

Greg

My error, not yours

Run "MySub" & Format$(ListBox1.Value, "00")

works fine



"Toppers" wrote in message
...
Patrick,
When I try this :

Private Sub ListBox1_Click()
Call "MySub" & Format$(ListBox1.Value,"00")
End Sub

I get an error message "expected identifier" which is why is used
Application.Run in my response. What am I doing wrong?

TIA.
"Patrick Molloy" wrote:

a simple method would be to ensure that your sub have similar names eg
MySub01
MySub02
...
MySub31

then you'd just need one line

Call "MySub" & Format$(MyListBox.Value,"00")

Otherwise, the Select Case method is worth trying

SELECT CASE MyListBox.Value
CASE 1,5,8 : Call ThisSUb
CASE 2 : Call ThisOtherSub
CASE 3,12,15: Call YetAnotherSub
CASE ELSE : msgbox "oops !",,"missing sub: " & MyListBox.Value
END SELECT



"Greg" wrote:

Hi all,

I have a list box that has the numbers 1 - 30 in this list. I would
like to
have a particular macro run for each individual number.

How do I get a macro to run for a certain number using a listbox, or
should
I be using something else?

Thanks

Greg

Thanks sorry for the late response. You reply never showed up at all just
the one stating the error. I will try it.

Thanks Greg