Error Checking in Excel
Thanks Thanks:  0
Likes Likes:  0
Page 1 of 3 123 LastLast
Results 1 to 10 of 28

Thread: Macro that choose which worksheet to view that is on ALL wor

  1. #1
    Board Regular
    Join Date
    Feb 2002
    Location
    Houston, TX
    Posts
    359
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Is there a macro that choose which worksheet to view? Maybe a combo box on each page? BUT I need those combo boxes to appear automatically on each worksheet and be popoulated/updated with the worksheets automatically since this workbook is updated continually.

    And YES, I know you can use the tabs at the bottom, but that can be cumbersome with a lot of worksheets.

    Thanks!

  2. #2
    MrExcel MVP Al Chara's Avatar
    Join Date
    Feb 2002
    Location
    Newark, Delaware
    Posts
    1,701
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    How about creating a summary page...

    You can download ASAP at the following link:
    http://asap-utilities.com/
    And create a summary sheet.

    Or you can make your own. Try the following code in the Module for ThisWorkbook:
    Code:
    Private Sub Workbook_Open()
    Dim ws As Worksheet
    For Each ws In Worksheets
    Worksheets("Summary").Hyperlinks.Add Anchor:=Worksheets("Summary").Cells(ws.Index, 1), Address:="", SubAddress:=ws.Name & "!A1", TextToDisplay:=ws.Name
    Next
    End Sub
    Best regards,
    Allan Chara
    http://www.mrspreadsheets.com

  3. #3
    MrExcel MVP Damon Ostrander's Avatar
    Join Date
    Feb 2002
    Location
    Denver, Colorado USA
    Posts
    4,239
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Hi Cosmos75,

    There actually is a built-in capability that is not well advertised to do this. Simply right-click on the "Workbook tabs" toolbar and a worksheet list shortcut will pop up. This toolbar is the one that shows the worksheet tabs, but you have to click on the left end of the toolbar where the go right/left buttons are (i.e., right-click on one of these buttons).
    Keep Excelling.

    Damon

    VBAexpert Excel Consulting
    LinkedIn Profile http://www.linkedin.com/pub/damon-ostrander/7/79/a93
    AllExperts Profile http://www.allexperts.com/ep/1059-30...-Ostrander.htm

  4. #4
    Board Regular
    Join Date
    Feb 2002
    Location
    Houston, TX
    Posts
    359
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Damon Ostrander!!

    That's exactly what I wanted!! Is there code to call that from a Macro? Tried recording a macro but if only has a line to select the sheet I selected from the pop-up box.

    Al Chara,

    I actually have a summary page that is linked to all other worksheets. And the other worksheets are linked back to the summary page. I wanted a way to jump to any page from ANY other page. Hopefully, I can automatically add a button to each worksheet to be able to bring up that pop-up box that Damon Ostrander was talking about!

    PLEASE LET THE BE CODE FOR THAT!!

    Any ideas on automatically adding a button to each workshet to activate that pop-up box? (assuming there's code for that pop-up box)

  5. #5
    Board Regular
    Join Date
    Feb 2002
    Location
    Houston, TX
    Posts
    359
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Anyone know?

  6. #6
    MrExcel MVP Damon Ostrander's Avatar
    Join Date
    Feb 2002
    Location
    Denver, Colorado USA
    Posts
    4,239
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Hi again Cosmos,

    The code:

    CommandBars("Workbook tabs").ShowPopup

    will pop up the shortcut menu for worksheet selection.

    If you want to automatically add a button to each worksheet in your workbook that will pop up this menu, just use the following macro (AddButtons). This example adds each button in cell B4. Just change this reference (one place) to put the button in any cell you choose. It does not add buttons to other types of sheets (e.g., Chart sheets), but this would be easy to do as well.

    Sub SelectWorksheetMenu()
    CommandBars("Workbook tabs").ShowPopup
    End Sub

    Sub AddButtons()
    Dim WS As Worksheet
    Dim C As Range 'The cell where the button will be placed
    Dim Width As Single 'The button width
    For Each WS In Worksheets
    Set C = WS.[B4] 'put button in cell B4
    If C.Width > 60 Then Width = C.Width Else Width = 60
    With WS.Buttons.Add(C.Left, C.Top, Width, C.Height)
    .OnAction = "SelectWorksheetMenu"
    .Characters.Text = "Select Sheet"
    .Characters.Font.Size = 8
    End With
    Next WS
    End Sub
    Keep Excelling.

    Damon

    VBAexpert Excel Consulting
    LinkedIn Profile http://www.linkedin.com/pub/damon-ostrander/7/79/a93
    AllExperts Profile http://www.allexperts.com/ep/1059-30...-Ostrander.htm

  7. #7
    Board Regular
    Join Date
    Feb 2002
    Location
    Houston, TX
    Posts
    359
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Guru Damon Ostrander!!!!

    THANK YOU!!!!

  8. #8
    MrExcel MVP Al Chara's Avatar
    Join Date
    Feb 2002
    Location
    Newark, Delaware
    Posts
    1,701
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Since you have a summary page, why don't you put a combobox on each page with the ListFillRange equal to Summary!A:A. Then use the following code to activate the selected worksheet:

    Private Sub ComboBox1_Change()
    Worksheets(ComboBox1.Value).Select
    End Sub

    Edit as needed and post with further questions.
    Best regards,
    Allan Chara
    http://www.mrspreadsheets.com

  9. #9
    Board Regular
    Join Date
    Feb 2002
    Location
    Houston, TX
    Posts
    359
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Al Chara,

    That sounds like a good idea. The only thing is I need the combo-box to show up on every page and pages are added regularly. SO, I'd have to figure out how to automatically add a combo box to each sheet (except summary sheet) and I'm not very good at VBA so far.

    Also, would your code be more efficient or would Damon's be more efficient?

  10. #10
    Board Regular
    Join Date
    Feb 2002
    Location
    Houston, TX
    Posts
    359
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Damon,

    One question, if I have to click add button macro every time I add a sheet, the sheets that already have a button will have an extra button on top of the existing button.

    Is there a way to check if a button already exists as to not have a redundant button?

    I apologize about this. I don't mean to sound ungrateful cause believe me I AM VERY GRATEFUL for all the help.

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
  •