sidlet
09-22-2007, 07:44 PM
I've discovered a problem when using the CodeName. It happens at the point of adding a new sheet. The Codename is missing/blank but you'll have to make sure the VBE is not open otherwise the CodeName works fine. Here's the code which sits in Module1:
Sub ProduceSummarySheet()
Dim newWrkSheet As Worksheet
Dim capdesc As String
capdesc = "newone" & Format$(Now(), "hhmmss")
On Error GoTo err_ProduceSheet
ActiveWorkbook.Sheets.Add after:=Worksheets(Worksheets.Count)
'hold onto the newly added sheet
Set newWrkSheet = Worksheets(Worksheets.Count)
On Error Resume Next
Worksheets(Worksheets.Count).Name = capdesc
If Err = 1004 Then
'dup named sheet
'delete old sheet
'MasterSheet.DeleteFromMaster Worksheets(capdesc).CodeName
Worksheets(capdesc).Delete
newWrkSheet.Name = capdesc
End If
Err.Clear
On Error GoTo err_ProduceSheet
'reset the active to the newly added sheet
newWrkSheet.Activate
Debug.Print "ProduceSummarySheet:" & ActiveSheet.CodeName & "(" & ActiveSheet.Name & ")"
Exit Sub
err_ProduceSheet:
MsgBox "Problem detected." & Err & ": " & Error(Err) & " Contact your developer."
Exit Sub
End Sub
if you run (F5) this in the VBE it will work and display the CodeName correctly but if you run this from a Menu option then the CodeName will be blank.
Here's the code I run from a menu option which also sits in Module1:
Sub InitiateAdjSummary()
On Error GoTo err_initdata
ProduceSummarySheet
Debug.Print "AdjSummary:" & ActiveSheet.CodeName & "(" & ActiveSheet.Name & ")"
'MasterSheet.AddtoMaster ActiveSheet.CodeName, ActiveSheet.Name, "SUMMARY", MyTime, "NEW", AdjTypID
Exit Sub
err_initdata:
MsgBox "Problem detected." & Err & ": " & Error(Err) & " Contact your developer."
Exit Sub
End Sub
Again when you run this from an Excel Menu option you have to be sure that the VBE is not open otherwise it works as expected.
What the heck is going:banghead: on?
Any help is greatly appreciated,
Sid
PS. This is on Excel 2000
Sub ProduceSummarySheet()
Dim newWrkSheet As Worksheet
Dim capdesc As String
capdesc = "newone" & Format$(Now(), "hhmmss")
On Error GoTo err_ProduceSheet
ActiveWorkbook.Sheets.Add after:=Worksheets(Worksheets.Count)
'hold onto the newly added sheet
Set newWrkSheet = Worksheets(Worksheets.Count)
On Error Resume Next
Worksheets(Worksheets.Count).Name = capdesc
If Err = 1004 Then
'dup named sheet
'delete old sheet
'MasterSheet.DeleteFromMaster Worksheets(capdesc).CodeName
Worksheets(capdesc).Delete
newWrkSheet.Name = capdesc
End If
Err.Clear
On Error GoTo err_ProduceSheet
'reset the active to the newly added sheet
newWrkSheet.Activate
Debug.Print "ProduceSummarySheet:" & ActiveSheet.CodeName & "(" & ActiveSheet.Name & ")"
Exit Sub
err_ProduceSheet:
MsgBox "Problem detected." & Err & ": " & Error(Err) & " Contact your developer."
Exit Sub
End Sub
if you run (F5) this in the VBE it will work and display the CodeName correctly but if you run this from a Menu option then the CodeName will be blank.
Here's the code I run from a menu option which also sits in Module1:
Sub InitiateAdjSummary()
On Error GoTo err_initdata
ProduceSummarySheet
Debug.Print "AdjSummary:" & ActiveSheet.CodeName & "(" & ActiveSheet.Name & ")"
'MasterSheet.AddtoMaster ActiveSheet.CodeName, ActiveSheet.Name, "SUMMARY", MyTime, "NEW", AdjTypID
Exit Sub
err_initdata:
MsgBox "Problem detected." & Err & ": " & Error(Err) & " Contact your developer."
Exit Sub
End Sub
Again when you run this from an Excel Menu option you have to be sure that the VBE is not open otherwise it works as expected.
What the heck is going:banghead: on?
Any help is greatly appreciated,
Sid
PS. This is on Excel 2000