Codenames
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 |
Codenames
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 "Bill" wrote in message link.net... | 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 | | |
Codenames
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. Bill wrote: 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 |
Codenames
You could try forcing a recompile after naming it
Application.VBE.CommandBars.FindControl(ID:=578).E xecute -- HTH Bob Phillips (replace somewhere in email address with gmail if mailing direct) "Bill" wrote in message link.net... 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 |
All times are GMT +1. The time now is 02:24 PM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com