Thank you for being a valued part of the CNET community. As of December 1, 2020, the forums are in read-only format. In early 2021, CNET Forums will no longer be available. We are grateful for the participation and advice you have provided to one another over the years.

Thanks,

CNET Support

General discussion

Using a list box to jump to a partifular sheet in a workbook

Aug 16, 2004 2:46PM PDT

Hi

I have basic understanding of macros and i would like to use a list box, and perhaps with the help of a button, to allow me to goto a particular sheet in the workbook (assuming i have the names of these sheets in the listbox)

Would you be able to advice me?

Thanks very much!

Discussion is locked

- Collapse -
Re: Using a list box to jump to a partifular sheet in a work
Aug 16, 2004 3:29PM PDT

You could use a button from the forms toolbar, click new from the assign macro pop-up window and paste this code inside the procedure:

Sheets("Sheet1").Select 'If Sheet1 is the name of your first sheet, and so on...

- Collapse -
Re: Using a list box to jump to a partifular sheet in a work
Aug 16, 2004 5:18PM PDT

Hi Swisse

I am afraid i do not quite understand you.

How would the button i created know that it is suppose to obtain the names of the worksheet from the list box that i have created?=)

Thanks

- Collapse -
Re: Using a list box to jump to a partifular sheet in a work
Aug 17, 2004 5:22AM PDT

OK let's start from scratch. Open Excel go to View then Toolbars then Forms. Be sure that before the word Forms there is a check. Now the Forms toolbar is visible on your worksheet. From the Forms toolbar click the List Box icon. If you move the cursor over the worksheet it changes to a crosshair. Press the left button and drag it downwards and rightwards then release it. A List box should be created on your worksheet. In the Forms toolbar on your worksheet click on the button icon, place your cursor over the created List Box, press the left button drag it downwards and rightwards then release it. A button should be created in your List Box, and a pop-up window with the name Assign Macro appears. It assigns a macroname for the new button (for example Button1_Click). On the right side of the Assign Macro pop-up window are several buttons, click on the New button, and the Microsoft Visual Basic for Excel opens. A small window opens up with the name Book1 - Module1(Code), just an example. The cursor should be placed between the lines Sub Button1_Click() End Sub. This is the procedure I've been talking about. Now paste this code:

Sheets("Sheet1").Select

inside the procedure. Your procedure should look like this now:

Sub Button1_Click()
Sheets("Sheet1").Select
End Sub

while you're in Microsoft Visual Basic save what you've done, say Test.xls. Do the same for the other buttons in your List box that should point to the other worksheets and just replace "Sheet1" with Sheet2 and so on in the other procedures.

- Collapse -
Re: Using a list box to jump to a partifular sheet in a work
Aug 16, 2004 7:49PM PDT
http://www.excelforum.com/t240081-s shows you how to fill a listbox with the names of the sheets, and then let the user select one or more to print.

It's a nice programming exercise to change the action to a select (or activate?) of just one sheet. Of course, there's only one sheet at the time to jump to, so a combo-box might be a good alternative. If you use a listbox, see if you can make it so that a simple click on the name suffices (the example, I suppose, lets you select more than one name, then click on OK to print).

There's a awful lot of Excel VBA programming information, examples tips and trickson the net. Use a google search for Excel VBA and some useful terms to find it, f.e. Excel VBA select worksheet if you want to know how to select a worksheet if you know it's name.

Hope this helps.


Kees