change code name

[sunilpatel]

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

 

[Per Jessen]

Hi

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

ActiveWorkbook.Protect Structure:=True, Windows:=False

Hopes this helps.