Rename Sheet87(Dallas) to Dallas(Dallas)?

  • Thread starter Thread starter M Skabialka
  • Start date Start date
M

M Skabialka

Using VBA code I am importing new worksheets into my workbook, after
deleting old versions. I have noticed that every time I bring in a new
sheet the name of the sheet changes so that in the Project window it shows:
Sheet87(Dallas)
Sheet88(Houston)
Sheet89(Atlanta)

Since this happens several times a week, is there going to come a time when
Sheetxx exceeds some Sheet number maximum and crashes the code?
Under properties:
(Name) Sheet87
Name Dallas

When I create the sheet I use:
Sheets.Add.Name = "Dallas"
but I can't see a way to change the Sheet# also.

Will this cause propblems in the future - if so, how can I prevent it now?
Excel 2007
 
It shouldn't hurt at all.

If I recall correctly, there was a problem with length of codenames in xl97, but
that occurred when a sheet was copied over and over and over again--not
inserted.

If you're really concerned about this, you can change the codename (that's the
name to the left) inside the VBE.

Open your workbook
Hit alt-f11 (to get to the VBE
hit ctrl-r (to see the project explorer)
expand your workbook's project so that you can see the sheets.
Hit F4 to see the selected item's property.

Change the (Name) property to what you want.

The (Name) property (with the parens) is the codename for that sheet.

By using a nice codename, you can make your code (if you have any macros) easier
to read.

Instead of using:
with worksheets("Dallas")
.range("A1").value = "hi"
end with

you could change the CodeName to something meaningful, like: DallasInput
and use
with DallasInput
.range("A1").value = "hi"
end with

And since changing the codename of a sheet is beyond the ability of the typical
user, it makes your code a bit more robust.

The user may be able to easily change the name that they see on the worksheet
tab in excel. But I bet most won't know how to change this codename.
 
The users will never get to the Code or Properties Window, so I would like
to change the CodeName programmatically.

I found other code to do this but got the message:
Programmatic access to Visual Basic Project is not trusted

I have decided not to up the trust level on this spreadsheet so will not try
to change the codename.
Thanks for your help.
 
This setting is a user by user setting. It only affects your pc. It doesn't
travel with the workbook/file.

You can toggle that setting, change the code name via your macro, and turn that
setting off.

Or you can do it manually without changing the code.

Or you can choose not to change the codename.
 
"You can toggle that setting, --- and turn that setting off."

Can that be done via code?
Mich
 
Not really.

Since it's a security setting, it's up to each user to decide. If it could be
toggled (easily) by code, then anyone who wanted to write malicious code would
find it difficult to resist.

M said:
"You can toggle that setting, --- and turn that setting off."

Can that be done via code?
Mich
Click to expand...
 
Back
Top