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
‘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
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)

2 thoughts on “Run VBA Code Macro on Protected Sheet

  1. Hello sir I just trying a macro code to copy and paste data from selected cells range to selected range and just want my data keep protected same time by itself every time after enter it

Leave a Reply

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

This site uses Akismet to reduce spam. Learn how your comment data is processed.