Page 1 of 2 12 LastLast
Results 1 to 15 of 21
  1. #1
    Lounger
    Join Date
    Nov 2006
    Posts
    48
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Organizing worksheet tabs (Excel 2003)

    I often end up with reports which involve numerous pairs of Pivot tables and charts. This may mean over twenty worksheets. While I can title them descriptively and color code them, the user has to scroll around at the bottom both to see their choices as well as to move from one to another if they're close enough to both be displayed at once. I am wondering if there are better ways to organize the tabs at the bottom. For example, can they be grouped into tabs within tabs (as one might organize files into folders, subfolders, etc.) or is it possible to display several rows of tabs at the bottom so all available worksheets are apparent?

    Thanks!

  2. #2
    WS Lounge VIP rory's Avatar
    Join Date
    Dec 2000
    Location
    Burwash, East Sussex, United Kingdom
    Posts
    6,280
    Thanks
    3
    Thanked 191 Times in 177 Posts

    Re: Organizing worksheet tabs (Excel 2003)

    Hi,
    The short answer is no, you can't do either of those things. The users can right-click on the sheet navigation buttons to get a list of sheets, or you could create a custom menu bar that provides a dropdown sheet list?
    Regards,
    Rory

    Microsoft MVP - Excel

  3. #3
    Lounger
    Join Date
    Nov 2006
    Posts
    48
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Organizing worksheet tabs (Excel 2003)

    Thanks, Rory, the right click method you've suggested is great. How difficult is it to create the custom menu bar you've described? And if I did so, would it function in all Excel files I opened, or have to be recreated for each? And if I sent a file with the menu bar to someone else, would the menu bar work fro them for that file?

    Thanks!

  4. #4
    WS Lounge VIP rory's Avatar
    Join Date
    Dec 2000
    Location
    Burwash, East Sussex, United Kingdom
    Posts
    6,280
    Thanks
    3
    Thanked 191 Times in 177 Posts

    Re: Organizing worksheet tabs (Excel 2003)

    It would depend. To have it available for all Excel files, you would need to make an add-in; to apply to one specific file, you would include the code in that file. The code would be largely the same whichever you chose to do, though the add-in would require some additional functionality to hook into different workbooks being activated/deactivated. If I get a minute today, I will try and knock up a demo of what I mean.
    Regards,
    Rory

    Microsoft MVP - Excel

  5. #5
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Organizing worksheet tabs (Excel 2003)

    You'd create the menu bar (or toolbar) in code each time the workbook is opened, and destroy it when the workbook is closed. It would be available in that workbook only, but for everyone who opens it.

  6. #6
    Lounger
    Join Date
    Nov 2006
    Posts
    48
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Organizing worksheet tabs (Excel 2003)

    Thanks, Rory, that would be much appreciated.

  7. #7
    Lounger
    Join Date
    Nov 2006
    Posts
    48
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Organizing worksheet tabs (Excel 2003)

    Sorry, I'm not quite following. If I have to recreate the menu bar each time I open the workbook, how would it then be available to others who opened it? And how long would it take to recreate it each time? Thanks!

  8. #8
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Organizing worksheet tabs (Excel 2003)

    The menu bar or toolbar would be created automatically when the workbook is opened, and it takes only a fraction of a second.

    See <post:=200,526>post 200,526</post:>.

  9. #9
    Lounger
    Join Date
    Nov 2006
    Posts
    48
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Organizing worksheet tabs (Excel 2003)

    Many thanks as always, I understand now.

  10. #10
    WS Lounge VIP rory's Avatar
    Join Date
    Dec 2000
    Location
    Burwash, East Sussex, United Kingdom
    Posts
    6,280
    Thanks
    3
    Thanked 191 Times in 177 Posts

    Re: Organizing worksheet tabs (Excel 2003)

    Here's a rough demo. Note that the list does not update if you rename a sheet (I haven't found a way of trapping that), but if you select a name that no longer exists, a message is displayed and then the list updates itself. The code is, I think, fairly self-explanatory.
    HTH
    Attached Files Attached Files
    Regards,
    Rory

    Microsoft MVP - Excel

  11. #11
    Lounger
    Join Date
    Nov 2006
    Posts
    48
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Organizing worksheet tabs (Excel 2003)

    Thanks, I'll give it a try!

  12. #12
    3 Star Lounger
    Join Date
    Jul 2002
    Location
    Ankeny, Iowa, USA
    Posts
    344
    Thanks
    8
    Thanked 0 Times in 0 Posts

    Re: Organizing worksheet tabs (Excel 2003)

    Hi!
    Silly question, but where are the sheet navigation buttons... I can't seem to find them??!
    Thanks,
    Lana

  13. #13
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Organizing worksheet tabs (Excel 2003)

    The sheet navigation tabs can be found in the lower left corner of the Excel window, to the left of the sheet tabs.
    Attached Images Attached Images
    • File Type: png x.png (3.7 KB, 7 views)

  14. #14
    3 Star Lounger
    Join Date
    Jul 2002
    Location
    Ankeny, Iowa, USA
    Posts
    344
    Thanks
    8
    Thanked 0 Times in 0 Posts

    Re: Organizing worksheet tabs (Excel 2003)

    Well... you learn something new everyday!! I've never used that before... awesome...thanks Hans!
    Lana

  15. #15
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Organizing worksheet tabs (Excel 2003)

    It's very handy if your workbook has a large number of sheets.

Page 1 of 2 12 LastLast

Posting Permissions

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