How to Show Excel Table Name on the Sheet

show Excel table name on sheet

If you have an Excel workbook with lots of tables and pivot tables, it can be hard to remember what they’re named, or what source data they're using. To help you keep track of them, AlexJ is sharing the User Defined Function (UDF) that he uses to help manage his files. With AlexJ's code in your file, just add a formula, click on a cell, and show Excel table name, or pivot table name, on the sheet.

The code is shown below, and there is also a link for downloading a sample file with the code installed in it. You

Continue reading How to Show Excel Table Name on the Sheet

Hide Sheets Based on Tab Color

hide sheets based on tab color

Last month, I showed you my code that hides worksheets, based on the text in their names. Someone asked we could hide sheets based on tab color instead, so that’s how today’s example works.

Just select a sheet type from the drop down list, and any sheets with matching tab color are visible. All other sheets are hidden, except the Menu. Then, select "(All)" to see all the sheets again. Watch the video, and get the details, below.

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

Continue reading Hide Sheets Based on Tab Color

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.

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.

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