Identifying Sheets

I ran into a little problem with Excel 2007 this week. When I have an Excel 2003 add-in loaded in 2007, it exhibits some strange behavior when copying sheets. Namely, in certain circumstances the CodeName property of the sheet gets changed. I’m not sure what all those circumstances are, but I do know one of them.

If the VBE is visible, the CodeName is preserved. If not, the CodeName is changed to Sheet1 (or whatever it would be if a new sheet was added).

I rely on the CodeName quite a bit in my code. For instance I only allow the user to do certain things when a certain sheet is active, usually triggered by an application-level event.

For the time being, I’m using the sheet name or the value in a particular cell to identify the sheet. But as Jon Peltier pointed out, that’s dangerous. So I’m going to rewrite the code to use a hidden range name.

First, I create a sheet-level range name with a Value of TRUE. The value isn’t really important as the mere existence of the name identifies the sheet, but I use the TRUE value to simplify the function that comes later.

To hide the name, I execute this VBA statement in the Immediate Window (via the SpreadsheetPage)

Sheet1.Names(“IsInvoice”).Visible = False

Then I use that name in these functions:

Public Function IsInvoice(sh As Worksheet) As Boolean
    On Error Resume Next
        IsInvoice = Evaluate(sh.Names(“IsInvoice”).Value)
End Function
Public Function GetInvoiceSheet(wb As Workbook) As Worksheet
    Dim ws As Worksheet
    For Each ws In wb.Worksheets
        On Error Resume Next
            If IsInvoice(ws) Then
                Set GetInvoiceSheet = ws
                Exit Function
            End If
        On Error GoTo 0
    Next ws
End Function
Posted in Uncategorized

13 thoughts on “Identifying Sheets

  1. Dick, a simpler and tidier solution may be to set a custom property for the sheet, which is accessible only from VBA (see sheet CustomProperties in help)

  2. CustomProperties are straight forward. I put together the following from the VBA Help file:

    Copy Sheet Name to a tag:

    wksSheet1.CustomProperties.Add Name:=”SheetName”, Value:=ActiveSheet.Name

    Test Sheet for Name Change:

    Function VerifySheetName(cpItem As Integer) As Boolean
    Dim wksSheet1 As Worksheet
    Dim cpValue As String

    Set wksSheet1 = Application.ActiveSheet

    cpValue = wksSheet1.CustomProperties.Item(cpItem).Value

    If cpValue ActiveSheet.Name Then
    VerifySheetName = False
    VerifySheetName = True
    End If

    ‘{Error Trapping Here}

    Exit Function

  3. When I last looked at custom sheet properties, they weren’t copied across when you copied a sheet – defeating the point of Dick’s requirement. I use hidden sheet-level defined names to identify worksheets and workbook document properties for books.

  4. I’d add a IsAnInvoice boolean function to the invoice sheets that returns true then change your call to

    Public Function IsInvoice(sh As object) As Boolean
        On Error Resume Next
            IsInvoice = sh.IsAnInvoice
    End Function

    That way its all upfront in the VBA for the maintainers that follow

  5. Simon,that’s cool, I never thought of doing something like that. Although I’m not sure it’s less work for the maintainer – it might just make my head spin an extra orbit!

    Can you explain why your function requires sh to be declared as object, not worksheet?

  6. Doug, yeah, they’re a hard lot to please maintainers. Me, I like to try and keep logic blocks either code side or grid side.

    sh needs to be an object so the code will compile, as a normal worksheet doesn’t have an IsAnInvoice method. Just those with the added VBA method (or it could be a property).

    Actually I think I would use a value in a cell where possible because then the logic is clear for everyone. That would depend on how meddlesome the users are though.

  7. On occasion I’ve put code into my templates, including code that really should live in a regular module. But it’s easier to distribute a few templates with code, especially code that works specifically on the template’s worksheet, than to distribute an add-in widely.

  8. I’ve been trying to put all my ThisWorkbook and Worksheet code in regular modules, with event driven calls from ThisWorkbook or Worksheet. This way I can easily swap out an old module for a new one without copying class module code to the sheet or ThisWorkbook.

    Obviously not using an addin.

  9. AlexJ, I do that to. Something that I learned a while back from one of the newsgroup experts I think. It makes it easier to test code too. I do it in addins as well, for instance, AddinInstall or WorkBookOpen.

  10. Sounds like this CodeName instability adds a bunch of annoyance, no matter which approach one takes.

    Hope this bug will be fixed before Excel 2007 is deployed here (wishful thinking…).

Posting code? Use <pre> tags for VBA and <code> tags for inline.

Leave a Reply

Your email address will not be published.