How to protect and unprotect sheet with VBA?

LordVoldetort_IV

New Member
Joined
Jun 10, 2021
Messages
19
Office Version
  1. 2016
Platform
  1. Windows
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".

VBA 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
            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
 

Excel Facts

How to find 2nd largest value in a column?
MAX finds the largest value. =LARGE(A:A,2) will find the second largest. =SMALL(A:A,3) will find the third smallest

fadee2

Active Member
Joined
Nov 7, 2020
Messages
367
Office Version
  1. 2019
Platform
  1. Windows
first off, protect your worksheet with a password, then use following piece of edited code ...

VBA 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...
 
Solution

Forum statistics

Threads
1,144,158
Messages
5,722,827
Members
422,460
Latest member
VBA_Noob01

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top