Save Space With Compact Worksheet Buttons

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.

buttoncaptions02

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.

buttoncaptions01

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

buttoncaptions03

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

__________________

Worksheet Combo Box Problem in Excel 2013

On of my favourite Excel tricks is to show a combo box, when you click a cell that has a drop down list. The combo box can have larger font, and it autocompletes, so it’s easier for people to use.

There is only one ActiveX combo box on the worksheet, and it is hidden until you click on a drop down cell. When it appears, it shows the data validation list for the selected cell.

combo box with drop down list

Continue reading “Worksheet Combo Box Problem in Excel 2013”

Excel VBA Problem With Step Into F8

After you record or write a macro in Excel, you can run the macro, or go slowly through it, line by line, to see if it is working correctly. I use the F8 key, in the Visual Basic Editor, if I’m troubleshooting a macro, to see where something is going wrong. You can use also use the Step Into command, in the VBE’s Debug menu.

f8problemfix04

However, for the past few months, something was going wrong with the “Step Into” command in Excel 2010, whether I used the menu, or the F8 key.

Continue reading “Excel VBA Problem With Step Into F8”

Add Number to Multiple Cells in Excel

If you’ve already entered a number in a cell, or a group of cells, what’s a quick way to add something to that amount?
For example, I keep track of my To Do list in a workbook, and one of my items is “Daily Admin tasks”. Sometimes, I start the day by answering client emails, posting links to my latest blog post, and doing the accounting for the previous day’s sales.
So, I enter the time spent – 0.75 hours – and move on to the next task.
Continue reading “Add Number to Multiple Cells in Excel”

Modify Excel VBA Code in Sample Files

On my website, there are hundreds of Excel sample files that you can download and use. Some of the files contain macros, and you might need to adjust those macros, to use them in your own files.
There is a page on my Contextures site, that explains how to copy the sample code into your files, and where to paste it. There are written instructions, and a few videos, to help you get started.
Continue reading “Modify Excel VBA Code in Sample Files”

Warning For Grouped Sheets

If you select more than one sheet in a workbook, and start typing or formatting, that data  or formatting will be entered in all the selected sheets, not just the active sheet.
That’s a great feature – if you want to type on all the sheets, or add formatting, or whatever. But it’s not so great if you don’t notice that the sheets are grouped, and accidentally work on all the sheets.
It’s easy to forget that you grouped a few sheets – you interrupt your work for a quick phone call, or grab another cup of coffee, and poof! That memory is gone, and the worksheet damage can begin.
Continue reading “Warning For Grouped Sheets”

Change Characters to Superscript

Do you ever use the Subscript or Superscript fonts formats in Excel? Maybe you’re reporting on chemical usage, and you need to enter CO2 as one of the row headings.
In Word, you can add those commands to the Quick Access Toolbar (QAT). Then, select a character, and click the Superscript command.
superscript02
Unfortunately, it’s not that easy in Excel – those commands aren’t available when you customize the QAT.
Continue reading “Change Characters to Superscript”