How to write code to test a cell for specific data, and if that data is present, don't run the macro. If not present, then run macro?

Question

I am trying to test a cell for specific data. If it contains that data, I do not want my code to run (because that would leave my worksheet and workbook Unprotected). If the cell contains data that does not match the test specifics, then I want the code to run. My code is to unprotect the active workbook, then unprotect the active worksheet, then fill the value of cell N41 as the "sheet name", then protect the active worksheet, then protect the active workbook. I want to add the test to the top of the code to avoid security failures.

The data that I want to test the cell for is:

• The cell does not contain more than 31 characters (including spaces between charaters)
• The cell does not contain any of the following characters: \ / : ? * [ or ]
• The cell is not blank (empty)

If any of the above data/characters are in the cell I want the code to not run and leave my password protection in place for both the protected worksheet and protected workbook.

If the cell contains less than 31 characters (including spaces), does not contain any of the unwanted characters, and has at least 1 character in it (not a blank cell) then I want the code to run. Any help would be greatly appreciated.

Private Sub CommandButton16_Click()
    ThisWorkbook.Unprotect Password:="Password1"
    ActiveSheet.Unprotect Password:="Password2"
    ActiveSheet.Name = Range("N41").Value
    ActiveSheet.Protect Password:="Password2"
    ThisWorkbook.Protect Password:="Password1"
End Sub

Answer 1

I guess the real question is "How to check if some value is the correct name for a worksheet?" in order to minimize the period when the document is not protected, and to eliminate an error when renaming.

From the full list of naming conventions we can learn two additional rules. The name shouldn't be "History" and it shouldn't begin or end with an apostrophe '. Also, there shouldn't be other sheets with that name.

In my opinion, the easiest way to accomplish the main task is to wrap renaming with On Error statements.

Private Sub CommandButton_Click()
Const BookPass = "Password1"
Const SheetPass = "Password2"
Dim NewName as String
Dim ErrCode&, ErrDesc$, ErrMessage$

    NewName = Range("N41").Value

    With ThisWorkbook
        .Unprotect BookPass
        With ActiveSheet
            .Unprotect SheetPass
            On Error Resume Next
            ' ------ Main Part -------
            .Name = NewName
            ' ------------------------
            ErrCode = Err.Number
            ErrDesc = Err.Description
            On Error GoTo 0
            .Protect SheetPass
        End With
        .Protect BookPass
    End With
    If ErrCode <> 0 Then
        ErrMessage = "NewName=" & NewName & vbNewLine & _
                     "Error=" & ErrCode & vbNewLine & _
                     "Description: " & ErrDesc
        MsgBox ErrMessage, vbCritical
    End If
End Sub

p.s. I suppose, this code will be placed in the worksheet object module. In this case, it is better to replace ActiveSheet with Me for readability.

Answer 2

If you are prepared to weaken the Workbook protection, you can add use this code when protecting the Workbook.
Your code can then change the sheet name without unprotecting the WorkBook, but so can your users.

ActiveWorkbook.Protect Password:="Password1", Structure:=False

The WorkSheet can be protected to allow changes from your code but not by your users.
This way you protect the WorkSheet and never have to unprotect it.

ActiveSheet.Protect Password:="Password2", UserInterfaceOnly:=True

In your code, you can set a boolean value to true if a test passes and exit the sub with a custom message if a test fails. Then test the boolean value and if it is true, unprotect the Workbook, make the update and reprotect the Workbook.

Option Explicit

Private Sub ProtectAll()
 
    ActiveWorkbook.Protect Password:="Password1"
'    ActiveWorkbook.Protect Password:="Password1", Structure:=False
'Optional: Allow changes to sheet names and order, not ideal
'but allows you to not have to protect and unprotect the workbook
 
    ActiveSheet.Protect Password:="Password2", UserInterfaceOnly:=True
'Allow changes to the active worksheet by VBA code, remains protected via the UI

End Sub

Private Sub UnprotectAll()

    ActiveSheet.Unprotect Password:="Password2"
    ThisWorkbook.Unprotect Password:="Password1"

End Sub

Private Sub ProtectWB()
 
    ActiveWorkbook.Protect Password:="Password1"

End Sub

Private Sub UnprotectWB()

    ThisWorkbook.Unprotect Password:="Password1"

End Sub

Private Sub Change()
  
Dim CellValue As String
Dim OKtoChange As Boolean
Dim ErrorMessage As String
  
CellValue = vbNullString
OKtoChange = False
  
    CellValue = ActiveSheet.Range("N41").Value
    
    If Len(CellValue) < 32 Then
        OKtoChange = True
    Else
        ErrorMessage = "The WorkSheet name is more than 31 characters."
        GoTo ErrorHandler
    End If
    'Other tests here to set the OKtoChange variable based on results
'If any test fails the code exits
    
    If OKtoChange = True Then
        Call UnprotectWB
            ActiveSheet.Name = CellValue
        Call ProtectWB
    End If

Exit Sub

ErrorHandler:
    MsgBox "Invalid name for the WorkSheet" & vbLf & ErrorMessage, vbCritical, "Invalid name"

End Sub