Codenames

[Bill]

Hello,
I am trying to use codenames of sheets to track sheet name changes. But
occassionally, I get nothing for a code. For example,

sname = activesheet.name
coden = sheets(sname).codename

coden comes up with nothing. What can cause that?

Thanks,

Bill

 

[Jim Rech]

A sheet module does not exist until it 'appears' in the VBE.

So, run this code with the VBE open and you get a name:

Sub a()
Dim sname, coden
Worksheets.Add
sname = ActiveSheet.Name
coden = Sheets(sname).CodeName
MsgBox coden
End Sub

But run it with the VBE closed and you do not.

--
Jim
| Hello,
| I am trying to use codenames of sheets to track sheet name changes. But
| occassionally, I get nothing for a code. For example,
|
| sname = activesheet.name
| coden = sheets(sname).codename
|
| coden comes up with nothing. What can cause that?
|
| Thanks,
|
| Bill
|
|

 

[uziel.jim]

I took a littlebit different approach.
I made userform1 with textbox1
In Thisworkbook module I put the following code in
the workbook SheetActivate and Deactivate events:

Private Sub Workbook_SheetActivate(ByVal Sh As Object)
UserForm1.Show vbModeless
End Sub

Private Sub Workbook_SheetDeactivate(ByVal Sh As Object)
UserForm1.Hide
End Sub

Then behind the userform module I put your code in 3 events:

Private Sub UserForm_Initialize()
sname = ActiveSheet.Name
coden = Sheets(sname).CodeName
UserForm1.TextBox1.Text = coden
End Sub

Private Sub UserForm_Activate()
sname = ActiveSheet.Name
coden = Sheets(sname).CodeName
UserForm1.TextBox1.Text = coden
End Sub

Private Sub UserForm_Click()
sname = ActiveSheet.Name
coden = Sheets(sname).CodeName
UserForm1.TextBox1.Text = coden
End Sub

Your workbook will need more than one sheet. As you change from sheet
to sheet,
the form will pop up and tell you the codename for the sheet you are
in.
Once you have started it you can click on another workbook and after
clicking once on the form, it will tell you the codenames of that
workbooks sheets as well.

Jim

As you change sheets your form will pop up and tell you the codename.

 

[Bob Phillips]

You could try forcing a recompile after naming it

Application.VBE.CommandBars.FindControl(ID:=578).Execute

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)