Run VBA Code Macro on Protected Sheet

We can run a macro or VBA code on a protected sheet by using the ‘unprotection’ and ‘protection’ in the macro itself. Also the process of protecting a worksheet is not really a secure method to protect your data. The strategy to be adopted to protect your data well is to protect the sheet using VBA code and also lock the VBA project from viewing so that the macro can be viewed only by authorized personnel.
The following code will work only if the Excel worksheet is unprotected:
Sub Button1_Click()

Set myRange = ActiveSheet.Range(“A2:A22″)
‘Key1 – Specifies the first sort field, either as a range name (String) or Range object; determines the values to be sorted
‘Order1 – Determines the sort order for the values specified in Key1
myRange.Sort Key1:=myRange, Order1:=xlDescending

End Sub

Here we insert a command button as an ActiveX control on our work and then code it as above. But as soon as we protect our worksheet using a password we cannot access the button or for that matter any worksheet cell that is locked. We will need to unprotect the sheet before using the ActiveX control or the worksheet and in that process we may sometimes forget to protect our worksheet because the process is manual.

Now if you use the following code to which only authorized personnel will have access since the VBA project is locked for viewing, the process becomes more secure and the protecting and unprotecting of the worksheet is automated.

Sub sortmydata()

‘First we unprotect thw worksheet
ActiveSheet.Unprotect Password:=”asdf1234”
‘Next we perform the relevant actions like sorting the data
Set myRange = ActiveSheet.Range(“B2:B22″)
myRange.Sort Key1:=myRange, Order1:=xlAscending
‘Finally we protect the worksheet automatically
ActiveSheet.Protect Password:=”asdf1234″

End Sub

You can also use the following code if you don’t wish to use the ‘Set’ keyword in your macro:

Sub sortData()
‘First we unprotect thw worksheet
ActiveSheet.Unprotect Password:=”asdf1234”
‘Select Range
Range(“A2:A22”).Select
ActiveWorkbook.Worksheets(“Sheet1”).Sort.SortFields.Clear
‘Define sort
ActiveWorkbook.Worksheets(“Sheet1”).Sort.SortFields.Add Key:=Range(“A2”), _
SortOn:=xlSortOnValues, Order:=xlDescending, DataOption:=xlSortNormal
‘Use the ‘With’ to perform more actions
With ActiveWorkbook.Worksheets(“Sheet1”).Sort
.SetRange Range(“A2:A22″)
.Header = xlNo
.MatchCase = False
.Orientation = xlTopToBottom
.SortMethod = xlPinYin
.Apply
End With
‘Finally we protect the worksheet automatically
ActiveSheet.Protect Password:=”asdf1234”

End Sub

Watch the Excel VBA training video to see how we can run VBA code or macro on a protected worksheet:


Further reading:
Range.Sort Method (Excel)

One thought on “Run VBA Code Macro on Protected Sheet

  1. Pingback: Automatically Track Changes in Excel Worksheet Without Sharing Workbook | Excel VBA Training Videos

Leave a Reply

Your email address will not be published. Required fields are marked *