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

"Charlotte E" wrote in message
...
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

"mp" wrote in message
...

"Charlotte E" wrote in message
...
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




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?

--
Clif McIrvin

(clare reads his mail with moe, nomail feeds the bit bucket :-)

Clif McIrvin presented the following explanation :
"mp" wrote in message
...

"Charlotte E" wrote in message
...
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




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?

Clif,
Excel assigns the CodeName when the sheet is inserted/copied. Both Name
and CodeName can be accessed right away!

--
Garry

Free usenet access at http://www.eternal-september.org
ClassicVB Users Regroup! comp.lang.basic.visual.misc

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/micr....*&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.



On 03/09/2011 12:06, Charlotte E wrote:
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



--
Dave Peterson

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


"Charlotte E" wrote in message
...
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

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)

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



"Rick Rothstein" wrote in message
...
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)