If you use the mouse most of the time, when you’re working in Excel, you probably right-click, to see the popup menus. For example, right-click a column heading to insert a new column. If your favourite commands aren’t on those popup menus, here’s how you can change Excel right-click menus, to add them. Also, if you have a minute, please take the 3-question Excel survey on my Debra D blog. Thanks!
Category: Excel VBA
List All Excel Sheets With Used Range
Last week I was updating one of my Excel sample files, and noticed that it was way bigger than it should be. Most of the sample files are just a few kilobytes in size, but this one was about 1.5 MB. What was going on?
Here’s how I found the problem, and a macro that you can use for troubleshooting in your workbooks.
Change Excel VBA Code to Improve Speed
In some of my files, macros run automatically when you select a cell on the worksheet. For example, if you’ve bought a copy of my Data Validation Multi-Select Premium (DVMSP) kit, it shows a pop-up list when you click on a cell with a drop down list.
I’ll show you how to quickly change that, so the pop-up appears when you double-click, and you could make a similar change in other worksheet code.
Show Message In Excel Status Bar
If a macro takes a long time to run, it can be frustrating to wait for it to finish. Usually the screen updating is turned off when a macro runs, so it’s hard to tell if anything is happening. Maybe it stopped, and you’ll be sitting here for the rest of the day, blissfully unaware of the problem.
To help you stay informed, you can show messages in the status bar, to let you know what progress is being made. It sounds like a technical challenge, but it’s easy to do.
Excel Macro Buttons on Floating Form
In the olden days (Excel 2003 and earlier), there were toolbars, and you could “float” those, and position them anywhere over the worksheet. You could also create custom toolbars, and add a few commands and macros to those. They could be moved around the worksheet too.
In the newer versions of Excel, those floating toolbars aren’t available, but you can create something similar, based on a UserForm. Build your own, or download my example, and customize it.
AutoFit Merged Cells Row Height Update 20151203
Way back in June 2012, I posted some sample code for adjusting the row height in merged cells. It’s been 3-1/2 years, and people are still commenting on that article!
Apparently it is a common problem, and even though I don’t like merged cells, sometimes we just have to deal with them.
Continue reading “AutoFit Merged Cells Row Height Update 20151203”
Customize Excel Context Menus
When you right-click in Excel, a pop-up menu appears, with a list of commands that you can use. The list changes, depending on where you’ve clicked, so it’s called a “Context Menu”.
But, even though those pop-up menus are helpful, they might not have all the commands that you like to use. Or, the commands might be there, but buried a few layers deep in the sub-menus.
Maybe you’d like to add a few commands, but there isn’t a built-in way to customize those menus, like there is for the Ribbon and Quick Access Toolbar (QAT).
Resize Excel Comments With Macro
Occasionally, for reasons unknown to anyone other than the centuries-old trolls who live in underground tunnels at Microsoft, Excel comments resize themselves. They get really wide, or narrow, and become almost impossible to read.
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.
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
__________________
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.
Continue reading “Worksheet Combo Box Problem in Excel 2013”