Show Specific Sheets in Excel

Show Specific Sheets in Excel With Drop Down List

In a workbook with lots of worksheets, you can have a hard time finding the sheets that you need, to do a specific task. Instead of scrolling through all the sheet tabs, or using the popup list of worksheets, use a drop down list to show specific sheets in Excel. Also, all other sheets are hidden, and you’ll be able to focus on what you need to do, and ignore everything else.

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

Continue reading Show Specific Sheets in Excel

List All Excel Sheets With Used Range

List Excel Sheets With Used Range http://blog.contextures.com/

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 http://blog.contextures.com/

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.

Continue reading Change Excel VBA Code to Improve Speed

Show Message In Excel Status Bar

Show Message in Status Bar http://blog.contextures.com/

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 http://blog.contextures.com/

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 http://blog.contextures.com/

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 http://blog.contextures.com/

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 http://blog.contextures.com/

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

http://blog.contextures.com/

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