Error Checking in Excel
Thanks Thanks:  0
Likes Likes:  0
Results 1 to 6 of 6

Thread: Clicking a cell or button takes you to a certain sheet

  1. #1
    Board Regular
    Join Date
    Mar 2002
    Location
    Milwaukee, WI
    Posts
    105
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    A macro in my workbook produces anywhere up to 20 different sheets, and I would like to find a better way of navigating those sheets than the tabs at the bottom, as you can only see certain ones.

    I thought I might set up an additional worksheet with buttons that take you to the specified page, but I've had difficulty. I can produce a list in Column A of all the names of the sheets, but I'm not sure how to create buttons with those names, and furthermore have each button take you directly to that page. I'm thinking it would work by using the Button's name to find the sheet, but i don't know how to access the button's name.

    Alternatively, can i make a worksheet where just clicking on a cell takes you to the sheet with that cell's name? Any suggestions?

  2. #2
    Board Regular
    Join Date
    Feb 2002
    Location
    Perth Australia
    Posts
    1,584
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Hi there

    Right click your sheet tab, left click View Code and paste in this event macro code:

    Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
    On Error Resume Next
    Sheets(Target.Value).Select
    End Sub

    Type your sheet names on Sheet1 (or whatever). When you double click the sheet name the macro will select that sheet.

    (You can also just right click the sheet navigation buttons at the bottom left of your window and this opens a list of your sheets, click the one you want).

    Hope this helps
    regards
    Derek




    [ This Message was edited by: Derek on 2002-04-08 06:15 ]

  3. #3
    Board Regular
    Join Date
    Mar 2002
    Location
    Milwaukee, WI
    Posts
    105
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    That worked terrific! Now here's the next question: Since I can't copy that list on each sheet as some of them are just charts, is it possible to "freeze" a tab so it is always the first sheet? This way no matter what sheet they go to, they can easily get back to the first one with the list.

  4. #4
    Board Regular
    Join Date
    Feb 2002
    Location
    Perth Australia
    Posts
    1,584
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Hi again

    You could put this event macro into all your other sheets (works for chart sheets too). When you doubleclick anywhere on the sheet it will take you to the switching worksheet (change "switch" to the name of your worksheet)

    This is the code for Charts

    Private Sub Chart_BeforeDoubleClick(ByVal ElementID As Long, ByVal Arg1 As Long, ByVal Arg2 As Long, Cancel As Boolean)
    Sheets("switch").Select
    End Sub

    This is the code for normal sheets:

    Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
    Sheets("switch").Select
    End Sub


    Hope this works for you
    regards
    Derek


    [ This Message was edited by: Derek on 2002-04-08 07:38 ]

  5. #5
    Board Regular
    Join Date
    Mar 2002
    Location
    Milwaukee, WI
    Posts
    105
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    That works great. Is there a way to have a macro automatically insert that into a new sheet that the macro creates?

  6. #6
    Board Regular
    Join Date
    Feb 2002
    Location
    Perth Australia
    Posts
    1,584
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Ah, yes I think this is possible, only I'd only be guessing at how its done. Perhaps one of the other VB experts can help me out here.
    Good luck
    Derek

Some videos you may like

User Tag List

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •