Office & Productivity Software forum

General discussion

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

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

In reply to: Using a list box to jump to a partifular sheet in a workbook

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

In reply to: Re: Using a list box to jump to a partifular sheet in a work

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

In reply to: Re: Using a list box to jump to a partifular sheet in a work

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

In reply to: Using a list box to jump to a partifular sheet in a workbook

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

Popular Forums

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

GIVEAWAY

Enter to win* a free holiday tech gift!

CNET's giving five lucky winners the gift of their choice valued up to $250!