ActiveSheet.CodeName?

[Charlotte E]

I'm trying to read the codename of a newly added worksheet, but for some
reason it keeps returning an empty string!


I've tried:

WS_CodeName = WB.Worksheets.Add.CodeName

and also

WB.Worksheets.Add
WS_CodeName = ActiveSheet.CodeName

and even

WB.Worksheets.Add
Set WS = ActiveSheet
WS_CodeName = WS.CodeName

No matter what, it keeps returning an empty string!


What am I doing wrong?!?


TIA

 

[mp]

I'm trying to read the codename of a newly added worksheet, but for some
reason it keeps returning an empty string!


I've tried:

WS_CodeName = WB.Worksheets.Add.CodeName

and also

WB.Worksheets.Add
WS_CodeName = ActiveSheet.CodeName

and even

WB.Worksheets.Add
Set WS = ActiveSheet
WS_CodeName = WS.CodeName

No matter what, it keeps returning an empty string!


What am I doing wrong?!?


TIA

dunno, try this...

Sub test()
Dim owb As Workbook
Set owb = ActiveWorkbook
Dim oWs As Worksheet
Set oWs = owb.Sheets.Add

MsgBox oWs.Name
MsgBox "(" & oWs.CodeName & ")"

End Sub

works here
hth
mark

 

[Clif McIrvin]

dunno, try this...

Sub test()
Dim owb As Workbook
Set owb = ActiveWorkbook
Dim oWs As Worksheet
Set oWs = owb.Sheets.Add

MsgBox oWs.Name
MsgBox "(" & oWs.CodeName & ")"

End Sub

works here
hth
mark

Is it possible there's a timing issue here and OP's code is accessing
the codename property before it's been initialized? If that's the case
the msgbox would mask the issue.

What version of Excel?

 

[Dave Peterson]

It's a bug in excel.

If you're testing and you have the VBE open, you'll see the codename assigned
right away. But if the VBE is closed (and never opened??? I forget the
details), then you won't see the codename until you try to do something with it.

I did go to google to search for the fix and found this:
https://groups.google.com/group/mic...dd+group:microsoft.public.excel.*&hl=en&pli=1

or
http://is.gd/B1yFfz

Chip Pearson posted one fix, but I don't recall if that was the easiest way.
You may want to look through google to find a better method.

 

[Charlotte E]

Thanks for all your suggestions

Thanks to Ron and GS, I found a workaround, since the only real issue
between 'Name' and 'CodeName' is the number - which was a minor issue for me


Once again, thanks guys - you're the best

CE

 

[Rick Rothstein]

since the only real issue between 'Name' and 'CodeName'

is the number - which was a minor issue for me

Which number are you talking about? I ask because you may not realize that
both the sheet's name AND its code name can be changed. Go into the VB
editor (press ALT+F11) and click on one of the sheets listed in the Project
Window (press CTRL+R if it is not visible), then look at the Properties
Window (press F4 if it is not visible)... the first item in the Properties
list is (Name)... the word Name with parentheses around it... that is the
Code Name... further down the list is the work Name (with no parentheses
around it)... that is the Sheet Name... both of these can be changed by the
user if so desired.

Rick Rothstein (MVP - Excel)

 

[Charlotte E]

I know

And that was the purpose of my code - to rename the codename to the proper
locale - thus actually not needing the number of the sheet...

CE