How to protect and unprotect sheet with VBA?

[LordVoldetort_IV]

Hello! i have a masterlist for some of our company's B-BBEE clients, and i do not want people to just delete rows or add rows to the table without using the button assigned to edit the table. i am guessing i need to protect the sheet and unprotect the sheet? I am very new to VBA as a whole and i have no idea how to do this!?‍ Would really appreciate the help! The below code is what i have at the moment linked to the button mentioned earlier. the sheet to protect and unprotect is named "Masterlist".

Private Sub CommandButton3_Click()

    Dim answer As Integer

        answer = MsgBox("You are about to edit the B-BBEE Masterlist, do you want to continue?", vbQuestion + vbYesNo + vbDefaultButton2, "Masterfile edit")
    
 If answer = vbYes Then
    
    Dim mysheet As Worksheet
    Dim LastCol As Integer
    
    Set mysheet = Worksheets("Masterlist")
        With mysheet
            LastCol = .Cells(4, .Columns.Count).End(xlToLeft).Column
            ThisWorkbook.Names.Add Name:=.Name & _
                                     "!Database", _
                               RefersToR1C1:="=OFFSET(" & _
                 .Name & "!R3C1,0,0,COUNTA(" & _
                                             .Name & "!C1)," & LastCol & ")"
        If .Range("Database").Rows.Count > 1 Then
            .ShowDataForm
        End If
            MsgBox "Masterlist updated", 64, "Masterfile edit"
         End With
    Call testforfail
    
Else: MsgBox "Masterlist not updated", 64, "Masterfile edit"
    End If
End Sub

 

[fadee2]

first off, protect your worksheet with a password, then use following piece of edited code ...

Private Sub CommandButton3_Click()

    Dim answer As Integer

        answer = MsgBox("You are about to edit the B-BBEE Masterlist, do you want to continue?", vbQuestion + vbYesNo + vbDefaultButton2, "Masterfile edit")
    
 If answer = vbYes Then
    
    Dim mysheet As Worksheet
    Dim LastCol As Integer
    
    Set mysheet = Worksheets("Masterlist")
        With mysheet
            .Unprotect Password:="myPassword"
            LastCol = .Cells(4, .Columns.Count).End(xlToLeft).Column
            ThisWorkbook.Names.Add Name:=.Name & _
                                     "!Database", _
                               RefersToR1C1:="=OFFSET(" & _
                 .Name & "!R3C1,0,0,COUNTA(" & _
                                             .Name & "!C1)," & LastCol & ")"
        If .Range("Database").Rows.Count > 1 Then
            .ShowDataForm
        End If
            MsgBox "Masterlist updated", 64, "Masterfile edit"
            .Protect Password:="myPassword"
         End With
    Call testforfail
    
Else: MsgBox "Masterlist not updated", 64, "Masterfile edit"
    End If
End Sub

Above code will unprotect the sheet and then perform update and then protect it again.

hth...