Office & Productivity Software forum

General discussion

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

by andyyap / August 16, 2004 2:46 PM PDT


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
You are posting a reply to: Using a list box to jump to a partifular sheet in a workbook
The posting of advertisements, profanity, or personal attacks is prohibited. Please refer to our CNET Forums policies for details. All submitted content is subject to our Terms of Use.
Track this discussion and email me when there are updates

If you're asking for technical help, please be sure to include all your system info, including operating system, model number, and any other specifics related to the problem. Also please exercise your best judgment when posting in the forums--revealing personal information such as your e-mail address, telephone number, and address is not recommended.

You are reporting the following post: Using a list box to jump to a partifular sheet in a workbook
This post has been flagged and will be reviewed by our staff. Thank you for helping us maintain CNET's great community.
Sorry, there was a problem flagging this post. Please try again now or at a later time.
If you believe this post is offensive or violates the CNET Forums' Usage policies, you can report it below (this will not automatically remove the post). Once reported, our moderators will be notified and the post will be reviewed.
Collapse -
Re: Using a list box to jump to a partifular sheet in a work
by swisse / August 16, 2004 3:29 PM 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
by andyyap / August 16, 2004 5:18 PM 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?=)


Collapse -
Re: Using a list box to jump to a partifular sheet in a work
by swisse / August 17, 2004 5:22 AM 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:


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

Sub Button1_Click()
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
by Kees Bakker / August 16, 2004 7:49 PM PDT 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.

Popular Forums
Computer Newbies 10,686 discussions
Computer Help 54,365 discussions
Laptops 21,181 discussions
Networking & Wireless 16,313 discussions
Phones 17,137 discussions
Security 31,287 discussions
TVs & Home Theaters 22,101 discussions
Windows 7 8,164 discussions
Windows 10 2,657 discussions


Help, my PC with Windows 10 won't shut down properly

Since upgrading to Windows 10 my computer won't shut down properly. I use the menu button shutdown and the screen goes blank, but the system does not fully shut down. The only way to get it to shut down is to hold the physical power button down till it shuts down. Any suggestions?