¨ FrontPage ¨ Outlook ¨
¨ Miscellaneous ¨
Also see Ken's article for proper design of your workbook/worksheet.
In order to use these tips by following my directions, you have to create your workbook properly. What does that mean? It means Column Headings in Row 1 (if any) and Row Headings in Column A (if any).
For our example, we're going to keep sales data for our employees (I wish).
Want to make your workbook really easy to navigate?
When you select the Freeze Panes option, the cells above and to the left of the cursor position become "frozen". So if you want just the first row to be frozen, you place your cursor on A2 first. If you want just the first column to be frozen, place your cursor on B1 first. If you want both the first row and first column to remain frozen, place your cursor on cell B2 first. Then hit WindowàFreeze Panes.
While you may rarely manually type formulas, when you do, it's a lot easier if you don't have spaces in your sheet names. Look at the first formula compared to the second. The second was in a workbook with spaces in the sheet names.
So, if you must put spaces in your sheet names, do it after your workbook is built, so you don't have to worry about all those extra characters—Excel will convert them for you if you change the sheet name. However, if you use the methods described in this article, you don't have to worry about using the sheet names for reference anyway.
You can have your title and still maintain a "database" layout. Just make Row 1 about an inch high. Then, using the Drawing toolbar, choose the textbox and create a textbox at the top of your worksheet. You can fill the textbox with any color you like, change the color and size of your font, and the color and thickness of the border. If you don't want it to print when you print your report, then double-click the textbox's border and uncheck Print Object. You can also tell it not to move or size with the cells, so it'll stay put.
You end up with a decent looking header for your worksheet.
In the workbook shown in graphic above, I created a textbox "navigation" button. Just create the textbox as described previously, and then select it. Hit InsertàHyperlink. Choose Bookmark. Then you can choose any sheet or named range you like.
You can even add a screentip, as shown above, which looks like this.
Thanks to my friend Steve Lucas for providing the code I needed to show you how to make Back and Next buttons. These are created with textboxes for the buttons. The code is applied as Steve provides in his VBA Express knowledgebase entry.
The following tips make it so that no matter where the last user was in the workbook when they closed the workbook, it always opens up on the home page, and whenever any worksheet is activated, we're at the top of the data and not down on row 7,638 or wherever the last user left it.
We do this with worksheet events. Right-click each worksheet tab and paste the following code into the code window that appears at right:
Private Sub Worksheet_Activate()
While you're in the Visual Basic Editor, double-click ThisWorkbook at the left of the screen.
Paste the following code into the code window at right.
Private Sub Workbook_Open()
Now, on your Home worksheet, go to ToolsàOptions, View tab. Uncheck Gridlines and uncheck Row and Column headings. If you want, you can even uncheck Sheet tabs.
You can download the workbook used for the screen captures here. It does contain macros.
OfficeArtilces.com debuted on May 26, 2005.
MrExcel.com provides examples of Formulas, Functions and Visual Basic procedures for illustration only, without warranty either expressed or implied, including but not limited to the implied warranties of merchantability and/or fitness for a particular purpose. The Formulas, Functions and Visual Basic procedures on this web site are provided "as is" and we do not guarantee that they can be used in all situations.
Access®, Excel®, FrontPage®, Outlook®, PowerPoint®, Word® are registered trademarks of the Microsoft Corporation.
MrExcel® TM is a registered trademark of Tickling Keys, Inc.
All contents © 1998-2014 by MrExcel Consulting | All rights reserved