Annie,
As others have pointed out, you were missing the quotes around the
code names. Just FYI, if you are using code names in your code in
order to prevent problems that might occur if the user renames a
sheet, you can use the code name anywhere you would normally use the
worksheet's tab name. The code name doesn't change when the user
changes a tab name. For example
Sheet1.Range("A1").Value =1234
Moreover, you can change the code name of a worksheet to something
more meaningful that "Sheet3". In VBA, select the module in question
in the Project window, press F4 to display the Properties window if it
is not already visible, and change the Name property to the desired
name. Note that unlike a worksheet's tab name, the code name cannot
contains spaces or punctuation characters. Alpha-numeric only. Then
you can reference the sheet like
SummarySheet.Range("A1").Value = 1234
where SummarySheet is the new code name. This is obviously more
meaningful that just 'Sheet3'.
You can change code names via code. If you want to access the
worksheet via its tab name, use code like the following:
With ThisWorkbook
.VBProject.VBComponents( _
.Worksheets("Sheet Two").CodeName).Name = "NewCodeName"
End With
Or, if you know the current code name, use code like
ThisWorkbook.VBProject. _
VBComponents("CurrentCodeName").Name = "NewCodeName"
And, just for completeness, you can also use
ThisWorkbook.VBProject.VBComponents("MainSheet"). _
Properties("Name") = "AnotherName"
but this last method has no advantage over the other methods. Just yet
another way to do the same thing.
I often use code names rather than tab names in my projects to prevent
run time errors if the user changes a tab name. No matter how many
times you tell a user not to change the tab name, someone will always
do just that, screwing things up.
Cordially,
Chip Pearson
Microsoft MVP 1998 - 2010
Pearson Software Consulting, LLC
www.cpearson.com
[email on web site]
