Changing inherent sheet name NOT tab name

[michael.beckinsale]

Hi All,

Is there a way to change the sheet name Excel assigns to a sheet using
VBA? If so would anybody be kind enough to provide a code snippet?

The code below copy's a sheet(s) and renames the tab but l want to
also rename the name assigned by Excel. A further problem is that l
dont know how to identify the sheet name that Excel has assigned to
the newly copied sheet!


For i = 1 To NoSheetsReqd
Sheets("My Template").Copy Before:=Sheets("2")
Sheets("My Template (2)").Select
Sheets("My Template (2)").Name = "New Sht Name"
Next i

All help gratefully appreciated.

For the curious l am building a solution where complete control of the
worksheets order & positioning is needed but it is desirable for the
end-user to be able to change the tab name to make it easily
identifiable to them, ie not just a number, so l can then use Excels
assigned names to control the order, positioning etc.


Regards

MB

 

[Mike Fogleman]

Mike, there are 3 ways to reference a sheet:
by tab name - Sheets ("New Sht Name").Activate
by the index number of the Sheets Collection - Sheets(2). Activate
by the code name of the sheet - Sheet2.Activate
It is not a good idea to change the code name of a sheet for Excel's sake,
but instead work with the other 2 properties. The Sheet.Name is the name you
see on the tab, and the index number is the order in which they appear from
left to right. Using these properties you can position any sheet wherever
you want.
Study the following code which will move the first tab to the end tab each
time it is ran,

Sub shts()
Dim i As Integer
Dim ws As Worksheet, wsName As String

i = Worksheets(1).Index
Set ws = Worksheets(i)
wsName = ws.Name
MsgBox ("The first sheet's name is " & wsName)
Sheets(1).Move after:=Sheets(Sheets.Count)
Set ws = Worksheets(i)
wsName = ws.Name
MsgBox ("The first sheet's name is " & wsName)
End Sub

Mike F

 

[Peter T]

I'm not quite sure how to interpret the subject line but perhaps you might
look into changing the CodeName. This is the name you see in the VBE that
precedes the 'tab' name in brackets.

There are problems though returning the codename of a newly inserted sheet
while the VBE is closed and until the wb has been saved. There are more
issues to address renaming the codename without security access to Visual
Basic Project. The codename can only be changed in the VBE or with code. You
probably don't need to change the codename, just record and store the
'given' codename for future reference in case user renames sheet or changes
tab-order.

For what I think is the main purpose of your thread try something like this

Sub test()
Dim wsCopy As Worksheet, wsNew As Worksheet
Dim wsAfter As Worksheet

' assumes the wb contains a sheet named "My Template"

Set wsAfter = Worksheets(2) 'Sheets("2") ? change to suit
NoSheetsReqd = 3

For i = 1 To NoSheetsReqd
Worksheets("My Template").Copy Before:=wsAfter

Set wsNew = Worksheets(wsAfter.Index - 1)

On Error Resume Next
n = 0
Do
Err.Clear
n = n + 1
wsNew.Name = "New Sht Name_" & n
Loop Until Err.Number = 0
On Error GoTo 0
Next i

End Sub

The copied sheet will exist in the index position it was copied to, so can
be referenced by its known index as above. Also if pasting into the
ActiveWorkbook, the newly copy/pasted sheet will be made the ActiveSheet,
which would be a simpler way to reference it.

Regards,
Peter T

 

[Jon Peltier]

Note that changing the codename requires security settings that allow access
to the VB Project (Tools menu > Macro > Security... > Trusted Publishers tab

check 'Trust access to Visual Basic project'.

- Jon

 

[michael.beckinsale]

Hi All,

Many thanks for all your suggestions. I will play around with the code
examples and by applying your thoughts & suggestions l am sure l can
now successfully write a solution.

I was referring to the sheet 'codename'

Jon - Your comment worried me, are you saying that if the solution is
distributed with the VBA project protected you cannot change the sheet
'codename' via VBA? If so that totally rules my plan out as l intended
the user to click on macro button to create the sheets from a list
generated by imported data. Currently the sheets names are as per the
imported list and are pretty meaningless so l need to allow the user
to rename to something meaningful. However l need somehow to retain
the original name for various reasons, hence l thought rename the
'codename'. I am not sure that using the index property is going to do
the job. Perhaps l should use a helper cells to 'remember' the
original name but that is a bit of a cop out.

Regards

MB

 

[Peter T]

If you mean you want to change codenames of sheets in a VB code protected
project, you can't as you'd need to do something like this -

With ActiveSheet
Set vbp = .Parent.VBProject
..Parent.VBProject.vbcomponents(.CodeName).Name = "newCodeName"
End With


Both Mike & I had previously tried to suggest it's unlikely necessary to
change the codename, hence I only briefly alluded to the security issue that
Jon expanded on.

If you are copying your template sheets unknown times, that you also want to
rename codenames, you will need to devise new names with some index as I
demonstrated and store them for recall in your code. But you might just as
well store the new codenames as given by default.

Another way to identify sheets that user may renamed is to install a hidden
worksheet name with some permanent unique id value. More code involved as
you will need to loop through sheets looking for your name and its value.
But easy to relate say the activesheet to your known id. To get you
started -

Dim nm As Name
Dim sID As String

sID = "ID_00123" ' generate something unique for each sheet and store
Set nm = ActiveSheet.Names.Add("nameCode", sID)
nm.Visible = False ' hide the name the names dialog

Regards,
Peter T