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