Creating a Menu using Macros in Excel 2003

I wanted to share with everyone something that I found very useful and that was how to create a Menu for a spreadsheet using macros. What we will do is create buttons from Macros and use our design skills to create a pretty impressive looking layout. If you need any assistance refer to the pictures as they are very detailed and if you have any further question just leave me a comment and I'll try to get back to you. The setup is that we are setting up a salary report an we're adding menu so lets get started:

Tools:
Excel 2003

Step 1.) Highlight all of the cells in the viewing area.


2.) If you have the Drawing Toolbar, which is a default, at the bottom of Excel Window, you will see a fill bucket. Click on that and select white.


3.) Next select the rectangle in the same Drawing bar, and create a rectangle. Make the size of the rectangle, the size that you want your overall interface to be.


4.) Then create a Textbox. Notice that it is colored white and has a black border. We do not want this. So Right click on the textbox and select Format TextBox.


5.)Then in Fill, select No Fill. And in Line select No Line.


6.) I have added more textboxes that include the author and a little information about the Sales Report.


7.) Add another rectangle and make it the hieght of the interface, and change the color to distinguish the two.


8.) Next in the at the bottom of the window, select AutoShapes-> Basic Shapes-> Rounded Rectangle. And depending on your preferences you may want to change the color like we did the previous shaes.


9.) Next make sure that the Visual Basic is active. So near the top, right click and place a check next to Visual Basic.


10.) Then select the Control Toolbox in the VB Toolbar.


11.) Then Select the label button and make a label over your first rounded rectangle.


12.) Notice again it has a white background and the font may not be want you want so lets change it. So Right+Click and select properties. Notice a Window pops up.

The settings that you need to worry about are:
BackStyle- Select Transperancy to remove the white space.
Caption- Changes the Wording of the box
TextAlign- Changes the Alignment. It is recommended that you center the text.
Font- Changes the Font.

13.) Now lets Rename the sheets at the bottom of the page. Right+Click on Sheet1 and select Rename.


14.) To add more worksheets, choose Insert from the menu, and then worksheet. This is more than likely going to change the arrangement of the sheets. So just rename them accordingly. To things cannot be the named the same thing at the exact same time so change one to a temporary name while renaming the other.


This is my result for the Sheet Tabs and for the overall Spreadsheet. Now lets get coding, don't worry it won't be that bad.



15.) Right+Click on one of the labels, and select View Code.



This is the page you should arrive at.


Now between th Private and the Sub portion of the code enter

ActiveWorkbook.Sheets("Last Quarter").Activate

Between the quotes, is where you put the page title that you want the label to direct the viewer too. And do not worry about spaces, as it accepts those.




This is what mine looked like after I finished the complete code for all four labels.

"X" out of the Code window and finally Turn on off the Design Mode by select the triangle in the VB toolbar.


Test out your new application by clicking on each Label. Now your ready to impress your boss.