Numbers

[Greg]

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

[Toppers]

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
>
>
>

[Patrick Molloy]

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
>
>
>

[Toppers]

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
> >
> >
> >

[Patrick Molloy]

My error, not yours

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

works fine



"Toppers" <[email protected]> wrote in message
news:[email protected]...
> 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
>> >
>> >
>> >

[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