Excel VBA: What Sheet is that?

When you’re working in Microsoft Excel Visual Basic for Applications, you may need to refer to a worksheet by name. This can be confusing because the sheet names are not necessarily what are displayed in the sheet tabs at the bottom of the screen.

There is only one way to know exactly what a sheet’s name is so you can refer to it by that name regardless of what the tab says. That is to view the Visual Basic Editor and select your project in the Project Explorer. Here you will see each sheet listed by name with the sheet tab name in brackets after it.

If you want to refer to a sheet by name in your VBA code use the sheet name at the left (not the one in brackets) in the Project Explorer.

This will ensure that you always use the exact sheet that you expect to be using in your Visual Basic application.

Excel macro – Format By Contents

You can do so much with Excel macros – they can be so powerful.

Here is a macro that formats a cell depending on its contents when you type something in it.

If you type a number, or a formula that returns a number, it is formatted one way, if you type a date it is formatted another way and if you type a word it is formatted a different way.

The macro uses the OnEntry event which fires whenever something is entered into a cell. If you attach the macro to an Auto_Open macro you'll ensure it is run whenever the workbook is opened.

To create the macro, choose Tools > Macro > Visual Basic Editor and, choose Insert > Module to add a module to the current worksheet. Type the code into the dialog.

Sub Auto_Open()
ActiveSheet.OnEntry = "formatCell"
End Sub

Sub formatCell()
If IsNumeric(ActiveCell) Then
ActiveCell.Font.Name = "Verdana"
ActiveCell.Font.Size = 12
ActiveCell.Font.ColorIndex = 46
ElseIf IsDate(ActiveCell) Then
ActiveCell.Font.Name = "Verdana"
ActiveCell.Font.Size = 10
ActiveCell.Font.ColorIndex = 50
Else
ActiveCell.Font.Name = "Times New Roman"
ActiveCell.Font.Size = 12
ActiveCell.Font.ColorIndex = 5
End If
End Sub

Sub Auto_Close()
ActiveSheet.OnEntry = ""
End Sub

Back in Excel choose Tools > Macro > Auto_Open to run the macro the first time to test it. Provided you have Excel configured to run macros, it will run automatically every time you open the workbook in future.

To learn more about Auto_open, AutoOpen and other fun macro naming conventions in VBA, visit this blog post:

What's in a name? Auto_Open or AutoOpen What's in a name? Auto_Open or AutoOpen
http://www.projectwoman.com/labels/Auto_Open.html

What's in a name? Auto_Open or AutoOpen

Sometimes you wonder if the folks up at Redmond are laughing at us behind our backs. Really, do they deliberately set out to confuse us or are they just that plain disorganised?

Today my quandary involves Auto_Open and AutoOpen. These are two special macro names. The first, Auto_Open is Excel's special named macro that runs automatically when the workbook containing it is opened. AutoOpen is the Word equivalent. It makes no sense that one has an underscore and the other doesn't - it just makes life for us VBA folk a little more confusing than it should be.

The other macros Auto_Close and AutoClose work the same way, Auto_Close is the Excel macro name - call a macro by this name and save it in your workbook and it will run whenever you close the workbook. In Word, the name is AutoClose.

To add to the confusion, PowerPoint doesn't support either of the naming conventions, in fact, you can't create auto running macros in PowerPoint the same way you do in Word and Excel. The workaround is cumbersome, you need to create a PowerPoint add-in that includes the Auto_Open subroutine. Load the Add-in and PowerPoint will run the code in Auto_Open it loads and ditto for subroutine called Auto_close - it runs when the add-in is unloaded - which happens automatically when you exit PowerPoint. Learn more about how to do this in this KnowledgeBase article.

Thanks Redmond, we are now officially confused!