Slick worksheet navigation without macros
June 22, 2009 • JP • No Comments • Rate This Article
• Links to this article
Here's a technique you can use to allow your users to navigate between worksheets, without burdening your workbook with macros. It uses drawing objects and takes advantage of the fact that they have a hyperlink property. We can use this property to jump anywhere on any worksheet!
I've created a sample workbook if you want to follow along.
Download sample workbook (46 KB)
First thing you want to do is right click on the menu bar and select "Drawing" to open the Drawing toolbar (or go to View » Toolbars » Drawing).
I go to Autoshapes » Basic Shapes » Bevel and use that for my nav buttons, but you can use whatever button style you want. Draw one and set up the colors you want, then right-click copy and paste it as many times as you need. Then right-click and set the text for each button. It should correspond to each worksheet or section you want to go to.
Now you want to right-click each button and go to "Edit Hyperlink" and set up the hyperlink for that button. You can go to another worksheet, or another spot on the same worksheet. In my sample workbook, each button is set up to go to cell A1 for a particular worksheet. I also grouped them to make it easier to move them around once I got them lined up perfectly.
The "slick" part comes in if you are setting up the workbook to navigate to different worksheets. What you want to do is right-click one of the buttons, then hold down the Ctrl key and left-click the rest of them. Then go to the next worksheet and paste the buttons in exactly the same spot. (It might take some practice to get it exactly right, but remember that holding the Alt key while moving an object will line it up with the cell gridlines.)
Go back to the first worksheet and change the fill color on the first button, then go to the second worksheet and change the fill color on the second button to exactly the same color, and so on for all the buttons. Now when you click on the buttons, whichever is the active sheet will have its button highlighted! A very cool, CSS-like trick. So you can identify which worksheet you are on by looking at the buttons. You can view all of this by downloading the sample workbook.
I set up five worksheets so you can see how it works.
Excel ·
Rate This Article:
(No Ratings Yet)
Loading ...What now?
Subscribe To The Blog
Email This Article
Follow Me On Twitter
My Facebook Page
Or just keep reading:
I'm just an average guy who writes VBA code for a living. This is my personal blog. Excel and Outlook are my thing, with a sprinkle of Access and Word here and there. Follow this space if you want to learn more about VBA. Keep Reading »
Previous Post: Case changing and checking in Excel
Next Post: Dynamic icons and images for your Access application
Very nice approach to this. One suggestion: Make sure you click on the "ScreenTip…" button and enter an explanation of what will happen when the user clicks the button. If you don't do that, the default ScreenTip is the file path, name and worksheet name; that can lead to a long screen tip and not be very helpful for the user.