How to write code to test a cell for specific data, and if that data is present, don't run the-CodePudding

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

CodePudding user response：

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.