Error Checking in Excel
Thanks Thanks:  0
Likes Likes:  0
Page 3 of 3 FirstFirst 123
Results 21 to 28 of 28

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

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

    Default

    Found another tidbit that moves you through the sheets one at a time.

    Use Ctrl-Page Down and Ctrl Page Up to move through the worksheets one at a time.

    The equivalent VBA code is ActiveSheet.Previous.Select or ActiveSheet.Next .Select

    Hope this proves useful to you!

    "With Great Knowledge Comes Great Responsibility!" - Excel-Man

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

    Default Re: Macro that choose which worksheet to view that is on ALL

    OK, I know this has been answered before but I can't seem to find it.

    If I use
    Code:
    Application.CommandBars("Workbook tabs").ShowPopup
    then I get menu to choose which worksheet I want to activate. But if there are a lot of worksheets, it'll show the first 15 (in order that it is arranged in workbook), and the last one will be "More sheets..", which when selected brings up a a kind of listbox that list ALL the available sheets in the active workbook to choose from.

    Is there code to bring that menu (listbox)?

  3. #23
    Board Regular
    Join Date
    Feb 2002
    Location
    Ahmedabad Gujarat
    Posts
    303
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Macro that choose which worksheet to view that is on ALL

    Respected Excel Guru Damon (Namaskar)

    His solution:

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

    Is the best solution I have seen ever for managing worksheet.

    Regard to him.

    http://www.pexcel.com/samplefile/she...sheetnames.zip

    is a sheet example file i have on my website. Added little color and a normal routine, could not resist to post here...

    somebody has said. if you wanna be great you should "read between the lines."
    ni****h desai
    Mindful act is consecration only road to salvation.

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

    Default Re: Macro that choose which worksheet to view that is on ALL

    With a little more searching I found this.
    Code:
        x = ActiveWorkbook.Sheets.Count
        If x > 16 Then
            Application.CommandBars("Workbook Tabs").Controls("More Sheets...").Execute
        Else
            Application.CommandBars("Workbook Tabs").ShowPopup
        End If
    I guess you can't use the "listbox" unless you have more than 16 worksheets?

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

    Default Re: Macro that choose which worksheet to view that is on ALL

    nisht,

    NICE example!

    But I have no idea how that works since the code is protected.

  6. #26
    Board Regular
    Join Date
    Feb 2002
    Location
    Ahmedabad Gujarat
    Posts
    303
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Macro that choose which worksheet to view that is on ALL

    Sorry for inconviency,

    YOu can download now, I have remove the protection.


    Thank you for downloading.
    ni****h desai
    Mindful act is consecration only road to salvation.

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

    Default Re: Macro that choose which worksheet to view that is on ALL

    nisht,

    Wow!

    THANK YOU!

  8. #28
    New Member
    Join Date
    Sep 2016
    Posts
    2
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Macro that choose which worksheet to view that is on ALL

    Hello All,

    I'm using this code to engage the "Activate Sheet" pop-up.
    Code:
    Sub Select_Sheet()
    '
    ' select_sheet Macro
    '
    ' Keyboard Shortcut: ctrl+k
    '
        If ActiveWorkbook.Sheets.Count <= 16 Then
            Application.CommandBars("Workbook Tabs").ShowPopup 500, 225
        Else
            Application.CommandBars("Workbook Tabs").Controls("More Sheets...").Execute
        End If
    End Sub
    The Activate Sheet popup appears and brings me to the desired page when I use the macro pane ALT+F8, but when I use the keyboard shortcut the Activate Sheet pane appears, but it won't take me to the selected sheet. The pane just closes.

    Wondering if any of you have experienced this problem before. I've tried using several different keyboard shortcuts–it's not that.

    Thanks in advanced for any help you might have to offer...

    Best,
    m

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
  •