Excel tips and tutorials
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.
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.
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.
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.
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”
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).
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.
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.
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.
They work well, but look a bit big and clunky.
Then, AlexJ sent me his tip for worksheet buttons. Make the buttons small, and let the text flow out to the right side.
The captions are clickable too, so that is an extra advantage.
Here are the steps for creating a compact button and caption:
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.
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
__________________
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”
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.
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.