Help with VBA Code to to unlock and lock sheet when using a command a command

[JMC57]

I have these two commands that appeared to work previously . Any help will be appreciated Thank you

Private Sub CommandButton1_Click()
Range("A2:j6000").ClearContents
End Sub

Private Sub CommandButton1_Click()
Range("e2:e6000").ClearContents
End Sub

Other commands on the sheet are as follows

Private Sub CommandButton3_Click()

Dim sh As Worksheet, lr As Long, rng As Range
Set sh = ActiveSheet
lr = sh.Cells(Rows.Count, 2).End(xlUp).Row
sh.Unprotect "jmc"
sh.Rows.Hidden = False
sh.Range("D1:D" & lr).AutoFilter 1, "C", , , False
Set rng = sh.Range("D2:D" & lr).SpecialCells(xlCellTypeVisible).EntireRow
sh.Range("D1:D" & lr).AutoFilter
rng.Hidden = True
ActiveSheet.Protect "jmc"
End Sub



Private Sub Worksheet_SelectionChange(ByVal Target As Range)

If Intersect(Target, Range("a2:k6000")) Is Nothing Then Exit Sub
Application.EnableEvents = False
Me.sort.SortFields.Clear
Me.sort.SortFields.Add Key:=Range("b2:b6000"), SortOn:=xlSortOnValues, Order:=xlDescending, DataOption:=xlSortNormal
With Me.sort
.SetRange Range("A2:k6000")
.Header = xlGuess
.MatchCase = False
.Orientation = xlTopToBottom
.SortMethod = xlPinYin
.Apply
End With
Application.EnableEvents = True

End Sub

 

[Jeffrey Mahoney]

There is a couple different ways to handle this. You can either unlock the sheet every time you need a macro to change something or you can protect the sheet with the UserInterfaceOnly parameter.

If you unlock the sheet for a macro and the macro fails, you could leave the sheet unprotected. The UserInterfaceOnly parameter allows a macro to edit a sheet without unprotecting it. The only drawback is that each time you load the workbook and each time you reset the VBA project, you must protect the sheet with that parameter again. That's why I run the macros below each time the sheet is activated and each time the workbook is opened. Hope this helps.

Private Sub CommandButton1_Click()
    Activsheet.Unprotect "password"
   
    Range("A2:j6000").ClearContents
   
    Activsheet.Protect DrawingObjects:=False, Contents:=True, Scenarios:=False, _
    AllowFormattingColumns:=True, AllowFormattingRows:=True, AllowSorting:=True, _
    AllowFiltering:=True, AllowUsingPivotTables:=True, Password:="password"
End Sub

Private Sub Worksheet_Activate()
  Dim Sht As Worksheet
 
  Set Sht = ActiveSheet
 
  Sht.Protect DrawingObjects:=False, Contents:=True, Scenarios:=False, _
    AllowFormattingColumns:=True, AllowFormattingRows:=True, AllowSorting:=True, _
    AllowFiltering:=True, AllowUsingPivotTables:=True, UserInterfaceOnly:=True, Password:="password"
End Sub

Private Sub Workbook_Open()
  Dim Sht As Worksheet
 
  Set Sht = Sheets("MySheet")
 
  Sht.Protect DrawingObjects:=False, Contents:=True, Scenarios:=False, _
    AllowFormattingColumns:=True, AllowFormattingRows:=True, AllowSorting:=True, _
    AllowFiltering:=True, AllowUsingPivotTables:=True, UserInterfaceOnly:=True, Password:="password"
End Sub