Last week, AlexJ sent me a tip for making better worksheet buttons, and I'll be using it from now on, instead of my old method. I'll show you both button types, and maybe you'll switch too.
Big Bold Buttons
If I'm making a workbook with several sheets, I usually add a menu sheet at the front, with buttons that link to the other sheets. In the screen shot below, you can see a typical menu, with buttons for two of the worksheets. Each button has a hyperlink to cell A1 on the named sheet.
- The buttons are wide enough to fit the longest sheet name
- The font is bold, and big enough to read on the dark background.
They work well, but look a bit big and clunky.
Clear and Compact Buttons
Then, AlexJ sent me his tip for worksheet buttons. Make the buttons small, and let the text flow out to the right side.
- With this method, all the buttons can be compact – just big enough to see, and click easily
- The text doesn't need to be big and bold, because we'll use black font on the white worksheet.
The captions are clickable too, so that is an extra advantage.
Set Up the Text
Here are the steps for creating a compact button and caption:
- Add a small shape to the worksheet, and type a caption
- Right-click on the shape, and click Size and Properties
- Click the Text Box heading, to see the options
- Add a check mark to Allow Text to Overflow Shape
- Remove the check mark for Wrap Text in Shape
- Add a few spaces at the start of the text, so it moves to the right of the button shape
- Change the Font colour to black
Download the Sample File
To see how the buttons work, you can download the sample file from AlexJ's Sample Files page on my Contextures website.
In the Excel Tips section, look for ET0001 – Compact Buttons With Captions. The zipped file is in xlsx format, and does not contain macros.
Watch the Video
To see the steps for creating compact buttons with captions, please watch this video tutorial.
Or watch on YouTube: Save Space With Compact Excel Buttons And Captions