Codename not set

[BigJimmer]

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.

 

[Geeta Sonawane]

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 a
times I get errors because the codemame is not initialized at the point i
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 strin

str = ws.CodeNam

if str = "" the
debug.print "Missing Codename
els
debug.print "CodeName = " & ws.Codenam
end su

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

I have narrowed the occurrence of the problem down to being when th
worksheet uses the default code name (Sheet#) and the code is run eithe
during the workbook open event, or is run using a worksheet that was jus
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:

Codename not set
I use the worksheet codename extensively throughout my add-ins. However a
times I get errors because the codemame is not initialized at the point i
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 strin

str = ws.CodeNam

if str = "" the
debug.print "Missing Codename
els
debug.print "CodeName = " & ws.Codenam
end su

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

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

Any ideas on how to resolve this problem

This happens in both Exccel 2000 and 2003.

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/tutorial...6-acd41f462063/generic-feed-parsers-redu.aspx

 

[Chip Pearson]

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

 

[Geeta Sonawane]

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 cod
name of the sheet. E.,

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

Cordially
Chip Pearso
Microsoft Most Valuable Professional
Excel, 1998 - 201
Pearson Software Consulting, LL
www.cpearson.com

Previous Posts In This Thread:


Submitted via EggHeadCafe - Software Developer Portal of Choice
Generic Feed Parsers Redux
http://www.eggheadcafe.com/tutorial...6-acd41f462063/generic-feed-parsers-redu.aspx