List All Excel Sheets With Used Range

List 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.

(adsbygoogle = window.adsbygoogle || []).push({});

Continue reading List All Excel Sheets With Used Range

Change Excel VBA Code to Improve Speed

Popup List on Click or Double-Click

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.

(adsbygoogle = window.adsbygoogle || []).push({});

Continue reading Change Excel VBA Code to Improve Speed

Show Message In Excel Status Bar

Show Message in 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.

Continue reading Show Message In Excel Status Bar

Excel Macro Buttons on Floating Form

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.

Continue reading Excel Macro Buttons on Floating Form

AutoFit Merged Cells Row Height Update 20151203

AutoFit Merged Cells in Excel

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

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).

Continue reading Customize Excel Context Menus

Resize Excel Comments With Macro

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.

Continue reading Resize Excel Comments With Macro

Mysterious Excel 2013 Filter Problems

A couple of days ago, I had problems while trying to run a simple macro in Excel 2013. Things didn’t go too well, and it took me quite a while to solve the problem. You can read the details below, and I hope it helps, if you ever run into a similar error.

I also have a couple of announcements, before you read the mystery of the misbehaving macro:

From Oct. 8-10, Jan Karel Pieterse is offering a 50% discount on his Formula auditing tool, RefTreeAnalyser. Enter the coupon code: MVP2014 Mynda Treacy’s Excel Dashboard course is open for registration.

Continue reading Mysterious Excel 2013 Filter Problems

Add Your Own Icon to an Excel Custom Ribbon Tab

Are you building custom tabs for your workbooks yet? Last year, I wrote a couple of articles about custom tabs:

Custom Ribbon Tab for Excel File – how to open and edit the Ribbon Code Add Custom Ribbon Tab to Excel Workbook – create a custom tab and add buttons

In the screen shot below, you can see the custom tab that appears on the Ribbon when the Order Form workbook opens. The buttons make it easy to clear the data entry cells, and print a completed order form.

Create an Icon

Today, you’ll see how to create your

Continue reading Add Your Own Icon to an Excel Custom Ribbon Tab

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.

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