Listbox or combobox on Form to run macros

Posted by tee on January 14, 2002 2:10 PM

Hi Everyone

I would like to put a Listbox on my main menu form so the user an select the worksheet (report) to view and print. I have a number of worksheets - too many to use command buttons to run the macros.

I have the macros set up and need to display them in a list box or combo box so when the user selects a "report" the macro will run and the report will be displayed.

Any help would be great. Thanks in advance

tee

Posted by Jacob on January 14, 2002 2:41 PM

Lets say you have 100 sheets then do this

Private Sub UserForm_Activate()

For x = 1 To Sheets.Count

Userform1.ListBox1.AddItem (Sheets(x).Name)

Next x

End Sub

This will put all the sheet names in the listbox.

Then in your code just use userform1.listbox1.value in the place of the sheet name and it will work based on what sheet was selected.

HTH

Jacob

Posted by tee on January 14, 2002 3:05 PM

Many thanks for that -

Do I DIM the x as an Integer?

How do I not display some sheets in the list box - like the ones that I have my formulas on - do I hide them and if so will they still work?

and in the last part of your answer - are you referring to the code in my macro that calls the form?

Many thanks
tee Userform1.ListBox1.AddItem (Sheets(x).Name) Next x

Posted by Jacob on January 14, 2002 4:04 PM

It isnt necessary to Dim the x, but I do recomment it you should also use option explicit at the top of the module to force this. In this case x would be an integer since it wouldnt get bigger that 32000+ which is the cut off for integers.

Jacob Many thanks for that - Do I DIM the x as an Integer? How do I not display some sheets in the list box - like the ones that I have my formulas on - do I hide them and if so will they still work? and in the last part of your answer - are you referring to the code in my macro that calls the form? Many thanks Userform1.ListBox1.AddItem (Sheets(x).Name) Next x : End Sub : This will put all the sheet names in the listbox. : Then in your code just use userform1.listbox1.value in the place of the sheet name and it will work based on what sheet was selected. : HTH : Jacob :

Posted by tee on January 14, 2002 9:28 PM

Re: Many thanks once again - no msg

Many thanks once again - no msg