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)
Then I use that name in these functions:
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
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)
dermotb,
After checking out worksheet custom properties from your suggestion, I found a newsgroup posting from Chip Pearson about the intricacies required to retrieve the custom properties in 2003.
(http://groups.google.com/group/microsoft.public.excel.programming/browse_thread/thread/b2ef5188ad35e66b/25ef257fb30dfb19?lnk=st&q=#25ef257fb30dfb19)
I think I like Dick’s approach better.
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
Else
VerifySheetName = True
End If
‘{Error Trapping Here}
Exit Function
Never knew about custom sheet props, very useful!
Dick: And of course you use Name Manager to hide the range name, not?
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.
I’d add a IsAnInvoice boolean function to the invoice sheets that returns true then change your call to
On Error Resume Next
IsInvoice = sh.IsAnInvoice
End Function
That way its all upfront in the VBA for the maintainers that follow
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?
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.
I never put any code in my templates, but if it’s all in the sheet’s class module I may be persuaded to make an exception.
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.
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.
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.
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…).