I use the worksheet codename extensively throughout my add-ins. However at
times I get errors because the codemame is not initialized at the point in
the code where it is being referenced.

For example, if I add something like this to my code....

sub Test (ws as Worksheet)

dim str as string

str = ws.CodeName

if str = "" then
debug.print "Missing Codename"
else
debug.print "CodeName = " & ws.Codename
end sub

I sometimes will get "Missing Codename", and other times (for the same
worksheet), it returns the code name. If I try stepping through the code, it
will always return a value for CodeName.

I have narrowed the occurrence of the problem down to being when the
worksheet uses the default code name (Sheet#) and the code is run either
during the workbook open event, or is run using a worksheet that was just
added to the workbook.

Any ideas on how to resolve this problem?

This happens in both Exccel 2000 and 2003.

I found the way to find out the sheet codename without going to VBAProject below is the code for your reference

Public Function sheet_codename() As String

Dim sht As Integer
'Dim sht_cdname As String
Dim actual_shtnm As String, temp_shtnm As String

For sht = 2 To Application.ActiveWorkbook.VBProject.VBComponents. Count
actual_shtnm = Application.ActiveWorkbook.ActiveSheet.Name
'ss = Application.ActiveWorkbook.VBProject.VBComponents( sht).Name
temp_shtnm = Application.ActiveWorkbook.VBProject.VBComponents( sht).Properties("name").Value
If Application.ActiveWorkbook.VBProject.VBComponents( sht).Type = 100 Then
If actual_shtnm = temp_shtnm Then
sheet_codename = Application.ActiveWorkbook.VBProject.VBComponents( sht).Name

End If
End If
Next

End Function




BigJimmer wrote:

Codename not set
13-Jan-10

I use the worksheet codename extensively throughout my add-ins. However at
times I get errors because the codemame is not initialized at the point in
the code where it is being referenced.

For example, if I add something like this to my code....

sub Test (ws as Worksheet)

dim str as string

str = ws.CodeName

if str = "" then
debug.print "Missing Codename"
else
debug.print "CodeName = " & ws.Codename
end sub

I sometimes will get "Missing Codename", and other times (for the same
worksheet), it returns the code name. If I try stepping through the code, it
will always return a value for CodeName.

I have narrowed the occurrence of the problem down to being when the
worksheet uses the default code name (Sheet#) and the code is run either
during the workbook open event, or is run using a worksheet that was just
added to the workbook.

Any ideas on how to resolve this problem?

This happens in both Exccel 2000 and 2003.

Previous Posts In This Thread:

On Wednesday, January 13, 2010 9:54 PM
BigJimmer wrote:

Codename not set
I use the worksheet codename extensively throughout my add-ins. However at
times I get errors because the codemame is not initialized at the point in
the code where it is being referenced.

For example, if I add something like this to my code....

sub Test (ws as Worksheet)

dim str as string

str = ws.CodeName

if str = "" then
debug.print "Missing Codename"
else
debug.print "CodeName = " & ws.Codename
end sub

I sometimes will get "Missing Codename", and other times (for the same
worksheet), it returns the code name. If I try stepping through the code, it
will always return a value for CodeName.

I have narrowed the occurrence of the problem down to being when the
worksheet uses the default code name (Sheet#) and the code is run either
during the workbook open event, or is run using a worksheet that was just
added to the workbook.

Any ideas on how to resolve this problem?

This happens in both Exccel 2000 and 2003.

On Friday, February 26, 2010 8:43 AM
Geeta Sonawane wrote:

Sheet code name not set
I found the way to find out the sheet codename without going to VBAProject below is the code for your reference

Public Function sheet_codename() As String

Dim sht As Integer
'Dim sht_cdname As String
Dim actual_shtnm As String, temp_shtnm As String

For sht = 2 To Application.ActiveWorkbook.VBProject.VBComponents. Count
actual_shtnm = Application.ActiveWorkbook.ActiveSheet.Name
'ss = Application.ActiveWorkbook.VBProject.VBComponents( sht).Name
temp_shtnm = Application.ActiveWorkbook.VBProject.VBComponents( sht).Properties("name").Value
If Application.ActiveWorkbook.VBProject.VBComponents( sht).Type = 100 Then
If actual_shtnm = temp_shtnm Then
sheet_codename = Application.ActiveWorkbook.VBProject.VBComponents( sht).Name

End If
End If
Next

End Function


Submitted via EggHeadCafe - Software Developer Portal of Choice
Generic Feed Parsers Redux
http://www.eggheadcafe.com/tutorials...sers-redu.aspx

The Worksheet object has a CodeName property that returns the code
name of the sheet. E.,g

Dim WS As Worksheet
For Each WS In ThisWorkbook.Worksheets
Debug.Print "Name: " & WS.Name, "CodeName: " & WS.CodeName
Next WS

Cordially,
Chip Pearson
Microsoft Most Valuable Professional,
Excel, 1998 - 2010
Pearson Software Consulting, LLC
www.cpearson.com



On Fri, 26 Feb 2010 05:47:19 -0800, Geeta Sonawane wrote:

I found the way to find out the sheet codename without going to VBAProject below is the code for your reference

Public Function sheet_codename() As String

Dim sht As Integer
'Dim sht_cdname As String
Dim actual_shtnm As String, temp_shtnm As String

For sht = 2 To Application.ActiveWorkbook.VBProject.VBComponents. Count
actual_shtnm = Application.ActiveWorkbook.ActiveSheet.Name
'ss = Application.ActiveWorkbook.VBProject.VBComponents( sht).Name
temp_shtnm = Application.ActiveWorkbook.VBProject.VBComponents( sht).Properties("name").Value
If Application.ActiveWorkbook.VBProject.VBComponents( sht).Type = 100 Then
If actual_shtnm = temp_shtnm Then
sheet_codename = Application.ActiveWorkbook.VBProject.VBComponents( sht).Name

End If
End If
Next

End Function




BigJimmer wrote:

Codename not set
13-Jan-10

I use the worksheet codename extensively throughout my add-ins. However at
times I get errors because the codemame is not initialized at the point in
the code where it is being referenced.

For example, if I add something like this to my code....

sub Test (ws as Worksheet)

dim str as string

str = ws.CodeName

if str = "" then
debug.print "Missing Codename"
else
debug.print "CodeName = " & ws.Codename
end sub

I sometimes will get "Missing Codename", and other times (for the same
worksheet), it returns the code name. If I try stepping through the code, it
will always return a value for CodeName.

I have narrowed the occurrence of the problem down to being when the
worksheet uses the default code name (Sheet#) and the code is run either
during the workbook open event, or is run using a worksheet that was just
added to the workbook.

Any ideas on how to resolve this problem?

This happens in both Exccel 2000 and 2003.

Previous Posts In This Thread:

On Wednesday, January 13, 2010 9:54 PM
BigJimmer wrote:

Codename not set
I use the worksheet codename extensively throughout my add-ins. However at
times I get errors because the codemame is not initialized at the point in
the code where it is being referenced.

For example, if I add something like this to my code....

sub Test (ws as Worksheet)

dim str as string

str = ws.CodeName

if str = "" then
debug.print "Missing Codename"
else
debug.print "CodeName = " & ws.Codename
end sub

I sometimes will get "Missing Codename", and other times (for the same
worksheet), it returns the code name. If I try stepping through the code, it
will always return a value for CodeName.

I have narrowed the occurrence of the problem down to being when the
worksheet uses the default code name (Sheet#) and the code is run either
during the workbook open event, or is run using a worksheet that was just
added to the workbook.

Any ideas on how to resolve this problem?

This happens in both Exccel 2000 and 2003.

On Friday, February 26, 2010 8:43 AM
Geeta Sonawane wrote:

Sheet code name not set
I found the way to find out the sheet codename without going to VBAProject below is the code for your reference

Public Function sheet_codename() As String

Dim sht As Integer
'Dim sht_cdname As String
Dim actual_shtnm As String, temp_shtnm As String

For sht = 2 To Application.ActiveWorkbook.VBProject.VBComponents. Count
actual_shtnm = Application.ActiveWorkbook.ActiveSheet.Name
'ss = Application.ActiveWorkbook.VBProject.VBComponents( sht).Name
temp_shtnm = Application.ActiveWorkbook.VBProject.VBComponents( sht).Properties("name").Value
If Application.ActiveWorkbook.VBProject.VBComponents( sht).Type = 100 Then
If actual_shtnm = temp_shtnm Then
sheet_codename = Application.ActiveWorkbook.VBProject.VBComponents( sht).Name

End If
End If
Next

End Function


Submitted via EggHeadCafe - Software Developer Portal of Choice
Generic Feed Parsers Redux
http://www.eggheadcafe.com/tutorials...sers-redu.aspx

This property is not useful still the VBA code window is not open for that workbook. Through addin we can't get the code name for activeworkbook. so the above solution is require.
you try to add your code in one workbook or addin and find out the sheet codename for new workbook without going in VBA code.



Chip Pearson wrote:

The Worksheet object has a CodeName property that returns the codename of the
26-Feb-10

The Worksheet object has a CodeName property that returns the code
name of the sheet. E.,g

Dim WS As Worksheet
For Each WS In ThisWorkbook.Worksheets
Debug.Print "Name: " & WS.Name, "CodeName: " & WS.CodeName
Next WS

Cordially,
Chip Pearson
Microsoft Most Valuable Professional,
Excel, 1998 - 2010
Pearson Software Consulting, LLC
www.cpearson.com

Previous Posts In This Thread:


Submitted via EggHeadCafe - Software Developer Portal of Choice
Generic Feed Parsers Redux
http://www.eggheadcafe.com/tutorials...sers-redu.aspx