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

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

Add Number to Multiple Cells in Excel

Add amount to multiple cells

If you’ve already entered a number in a cell, or a group of cells, what’s a quick way to add something to that amount?

For example, I keep track of my To Do list in a workbook, and one of my items is “Daily Admin tasks”. Sometimes, I start the day by answering client emails, posting links to my latest blog post, and doing the accounting for the previous day’s sales.

So, I enter the time spent – 0.75 hours – and move on to the next task.

Continue reading Add Number to Multiple Cells in Excel

Hide an Excel Worksheet Automatically

hide worksheet with vba

When you build a workbook for other people to use, there might be worksheets that can stay hidden some of the time. In this example, the workbook is used to create and print orders.

If the shipment is going to Canada, an Export Form needs to be printed too. For shipments to the USA, the form isn’t needed.

Continue reading Hide an Excel Worksheet Automatically

Excel Smart Art Family Tree

excel family tree

How far back can you trace your family tree? Prof. Lee Townsend, from University of Hartford, has found an interesting new way to draw her family tree -- in Excel, using Smart Art and some VBA.

The details are below, and I’m also happy to announce the winners in the Peltier Tech Chart Utility Giveaway.

Continue reading Excel Smart Art Family Tree

Modify Excel VBA Code in Sample Files

edit sample code

On my website, there are hundreds of Excel sample files that you can download and use. Some of the files contain macros, and you might need to adjust those macros, to use them in your own files.

There is a page on my Contextures site, that explains how to copy the sample code into your files, and where to paste it. There are written instructions, and a few videos, to help you get started.

Continue reading Modify Excel VBA Code in Sample Files

Warning For Grouped Sheets

grouped sheets warning

If you select more than one sheet in a workbook, and start typing or formatting, that data  or formatting will be entered in all the selected sheets, not just the active sheet.

That’s a great feature – if you want to type on all the sheets, or add formatting, or whatever. But it’s not so great if you don’t notice that the sheets are grouped, and accidentally work on all the sheets.

It’s easy to forget that you grouped a few sheets – you interrupt your work for a quick phone call, or grab another cup of coffee, and poof!

Continue reading Warning For Grouped Sheets

Change Characters to Superscript

superscript in cell

Do you ever use the Subscript or Superscript fonts formats in Excel? Maybe you’re reporting on chemical usage, and you need to enter CO2 as one of the row headings.

In Word, you can add those commands to the Quick Access Toolbar (QAT). Then, select a character, and click the Superscript command.

Unfortunately, it’s not that easy in Excel – those commands aren’t available when you customize the QAT.

Continue reading Change Characters to Superscript

Headers on Excel Combo Boxes

headers on combo box

You can use combo boxes to show a drop down list, either in a UserForm, or on a worksheet. Sometimes a list is easy to understand, like this one, which shows a part number and part name.

If the list has multiple columns, it might help if there is a heading for each column, like the one in the next screen shot.

You could probably figure it out without the headings, but it removes any potential confusion, if you show the labels at the top of the list.

Continue reading Headers on Excel Combo Boxes