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

Hi

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

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

Hopes this helps.

---
Per


On 10 Jun., 18:44, "sunilpatel" <sunilpa...@talktalk.net> 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