change code name
 

To prevent user changing sheet name i intend referencing with codenames.
Hence the code below, which creates new sheets from Templates. But it does
not work when VBA Project is locked.
I cannot lock workbook as the worksheets are interactive.

So how does on get round this problem?


Sub CREATESHEETS()
Dim sh As Worksheet
For Each sh In ActiveWorkbook.Sheets
If Right$(sh.NAME, 4) = "TEMP" Then
L% = InStr(sh.NAME, "TEMP")
NEWSHEETNAME = Left$(sh.NAME, L% - 1) & ROW%
NEWCODE = Replace(NEWSHEETNAME, " ", "")
Sheets(sh.NAME).Copy After:=Sheets(Sheets.Count)
OLDCODE = ActiveSheet.CODENAME
ThisWorkbook.VBProject.VBComponents(OLDCODE).NAME = NEWCODE
ActiveSheet.NAME = NEWSHEETNAME
End If
Next sh
End Sub

change code name
 

Hi

If you protect the workbook for structure, user can not change sheet
names...

ActiveWorkbook.Protect Structu=True, Windows:=False

Hopes this helps.

---
Per


On 10 Jun., 18:44, "sunilpatel" wrote:
To prevent user changing sheet name i intend referencing with codenames.
Hence the code below, which creates new sheets from Templates. But it does
not work when VBA Project is locked.
I cannot lock workbook as the worksheets are interactive.

So how does on get round this problem?

Sub CREATESHEETS()
Dim sh As Worksheet
For Each sh In ActiveWorkbook.Sheets
If Right$(sh.NAME, 4) = "TEMP" Then
L% = InStr(sh.NAME, "TEMP")
NEWSHEETNAME = Left$(sh.NAME, L% - 1) & ROW%
NEWCODE = Replace(NEWSHEETNAME, " ", "")
Sheets(sh.NAME).Copy After:=Sheets(Sheets.Count)
OLDCODE = ActiveSheet.CODENAME
ThisWorkbook.VBProject.VBComponents(OLDCODE).NAME = NEWCODE
ActiveSheet.NAME = NEWSHEETNAME
End If
Next sh
End Sub