Solved: CodeName Property missing

[sidlet]

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 on?
Any help is greatly appreciated,
Sid
PS. This is on Excel 2000

[Norie]

Sid

Why are you trying to use the CodeName in the first place?

In fact why are you trying to use a name anyway, you've already created a reference to the new worksheet.
[vba]
Set newWrkSheet = Worksheets(Worksheets.Count)[/vba]
By the way you could do that like this.
[vba]
Set newWrkSheet = ActiveWorkbook.Sheets.Add(After:=Worksheets(Worksheets.Count))
[/vba]
PS This is an Access forum.

[sidlet]

Norie,
Solve the problem. That's the point here! I didn't want to get into the whole application and why I need the CodeName but the fact remains that what I stated is what I need. Please take a look at the code and provide answers that solve the original question and not offer code for something off topic.

And I'm sorry that this ended up on the Access forum. Please free feel to give me instructions on moving this to the Excell forum

Sid

[sidlet]

link from another forum explains the problem:
http://www.mrexcel.com/archive2/42500/49267.htm

[Norie]

Sid

The code I offered wasn't off topic as far as I can see.

Also what I suggested wasn't either.

You've created a reference to the new sheet, so use it.

By the way I don't see anything in your code that is trying to add code which is what that link is concerned with.