1. Computer problem? Tech Support Guy is completely free -- paid for by advertisers and donations. Click here to join today! If you're new to Tech Support Guy, we highly recommend that you visit our Guide for New Members.

Use a button to select worksheet

Discussion in 'Business Applications' started by dmcclure, Jul 18, 2007.

Thread Status:
Not open for further replies.
Advertisement
  1. dmcclure

    dmcclure Thread Starter

    Joined:
    Jul 29, 1999
    Messages:
    142
    I have an Excel workbook with many worksheets. I have a title page on which I have listed the names of the worksheets. Currently the user selects a worksheet and clicks on a central button to open that worksheet. In the macro I’ve used Case to activate the worksheet selected. I would like to be able to use a set of buttons each with the worksheet name as the text on the button. I want to write a macro so that when the user clicks on a button the macro will open that worksheet. I have a feeling this isn't very clear! Help?


    Don
     
  2. OBP

    OBP

    Joined:
    Mar 8, 2005
    Messages:
    19,895
    Don, that is fairly straight forward, you can create the buttons with your Sheet names on the front and then copy your current macro in to the buttons code and then just change the names of the sheet that will open.
     
  3. slurpee55

    slurpee55

    Joined:
    Oct 20, 2004
    Messages:
    7,837
    Or, you can create you several buttons, name them Sheet 1, Sheet 2, etc. (or whatever, obviously), click on a button, go to Insert, Hyperlink. Edit Hyperlink will come up - choose Place in this document, then select the appropriate worksheet - the default is cell A1, but you could have the link send them anywhere on the sheet.
     
  4. dmcclure

    dmcclure Thread Starter

    Joined:
    Jul 29, 1999
    Messages:
    142
    What I was really hoping to do is: retrieve the button text (worksheet name) and then activate that worksheet. That way I can use one simple(?) macro for all buttons.
     
  5. bomb #21

    bomb #21

    Joined:
    Jul 1, 2005
    Messages:
    8,546
    You can do this using buttons from the Forms toolbar, you just have to name the button the same as (i) the text on the button (ii) the sheet to jump to.

    To name a button you have to select it first. Click on the button while holding down CTRL to do that. Then type the name (e.g. Sheet2) in the Name Box (which is just left of the formula bar, assuming you have that showing). It's important that you type the button name and then press ENTER.

    Once you've named your buttons, you can assign the following to all of them:

    Sub To_Sheet()
    Sheets(Application.Caller).Select
    End Sub


    HTH (if you need a "demo" file for this, post back)
     
  6. dmcclure

    dmcclure Thread Starter

    Joined:
    Jul 29, 1999
    Messages:
    142
    I would appreciate a "demo" file. I tried the code you suggested but I get an error message: "Subscript out of range"

    Don
     
  7. bomb #21

    bomb #21

    Joined:
    Jul 1, 2005
    Messages:
    8,546
    The subscript error is is from incorrectly naming a button, most likely. Here's your file.
     

    Attached Files:

  8. slurpee55

    slurpee55

    Joined:
    Oct 20, 2004
    Messages:
    7,837
    The only advantage to doing it this way is that you can set the link to the page directly - if the page name does not match the name on the button (aside from caps) you will get an error (e.g. Sheet 2 is not equal to Sheet2).
     

    Attached Files:

  9. Sponsor

As Seen On
As Seen On...

Welcome to Tech Support Guy!

Are you looking for the solution to your computer problem? Join our site today to ask your question. This site is completely free -- paid for by advertisers and donations.

If you're not already familiar with forums, watch our Welcome Guide to get started.

Join over 733,556 other people just like you!

Loading...
Thread Status:
Not open for further replies.

Short URL to this thread: https://techguy.org/597397

  1. This site uses cookies to help personalise content, tailor your experience and to keep you logged in if you register.
    By continuing to use this site, you are consenting to our use of cookies.
    Dismiss Notice