Restricting worksheet names

[cbrown6305]

I'm looking for a way to restrict the naming of a certain worksheet so that when a copy is made of the worksheet the name of the new worksheet must begin with "WF-". Basically a data validation for the worksheet names.

 

[Yongle]

(In principle) the solution is to force the user to do this via macro so that the renaming can be controlled

Please explain exactly what happens now

Do users manually select "Master" , copy it and name the copy ?
Is user triggering a macro ?
(please post the code)
Do users need to select "Master" for any other reason ?
etc

 

[Yongle]

Here is a way to copy the sheet and verify the name etc

Sub CopyMasterSheet()
    Dim newSheet As Worksheet, newName As String, msg As String
    Application.ScreenUpdating = False
'copy the sheet
    Sheets("Master").Copy after:=Sheets(Sheets.Count)
    Set newSheet = Sheets(Sheets.Count)
'ask user for new name
    newName = "WF-" & InputBox("do NOT begin with WF-" & vbCr & "which is automatically inserted", "Enter New Sheet Name")
    msg = Rejected(newName)
'test the name
    msg = NameBad(newName, newSheet)
    If Len(msg) > 0 Then Call DeleteSheet(newSheet, msg)
End Sub

Private Function NameBad(newName As String, newSheet As Worksheet) As String
    On Error Resume Next
    newSheet.Name = newName
    If Err.Number <> 0 Then NameBad = "Cannot use that sheet name"
    On Error GoTo 0
End Function
Private Function Rejected(newName As String) As String
    If MsgBox(newName, vbOKCancel, "Confirm name") = vbCancel Then Rejected = "name rejected by user"
End Function

Private Sub DeleteSheet(newSheet As Worksheet, msg As String)
    Application.DisplayAlerts = False
    newSheet.Delete
    Application.DisplayAlerts = True
    MsgBox msg, , ""
End Sub

You also need to prevent the sheet being copied the manual way.
I can help with that when you answer questions in post#2

 

[cbrown6305]

Thanks Yongle!

Users do copy a master template and rename it. The master template is named "WF-TEMPLATE". The user will copy that worksheet and replace the word "TEMPLATE" with a more appropriate name. I have a series of macros, formulas and dynamic ranges that populate a list of worksheets within the workbook that have names that begin with "WF-". That list is referenced by data validation cells. If a user doesn't appropriately name the new worksheets starting with "WF-", it won't appear in the drop-down list in the data validation cells.

I think the user's inclination will be to copy the worksheet manually (i.e., right click on worksheet), so any solution will have to consider that.

Ideally, I envision some VBA code that I can put in the worksheet that just restricts the user from renaming the worksheet to something that doesn't begin with "WF-". When the user copies the worksheet, the code will remain. Not sure if that is possible though.

If the solution is a macro, then I suppose I can just map that macro to a button and force the user to copy the sheet using the button rather than manually.

 

[Yongle]

Here is a very simple piece of code
- test on a copy of your workbook
- to test select any sheet other than "WF-TEMPLATE
- next select "WF-TEMPLATE" and try to copy it

Place code in ThisWorkbook module

Private Sub Workbook_SheetActivate(ByVal Sh As Object)
    If Sh.Name = "WF-TEMPLATE" Then ThisWorkbook.Protect
End Sub

Private Sub Workbook_SheetDeactivate(ByVal Sh As Object)
    If Sh.Name = "WF-TEMPLATE" Then ThisWorkbook.Unprotect
End Sub

 

[Yongle]

a late thought
- you could trigger macro to copy the template as the FIRST line in Workbook_SheetActivate and protect the workbook as its 2nd line

That combination would probably achieve everything that you want