Excel VBA Course
Excel VBA Course - From Beginner to Expert

200+ Video Lessons
50+ Hours of Video
200+ Excel Guides

Become a master of VBA and Macros in Excel and learn how to automate all of your tasks in Excel with this online course. (No VBA experience required.)

View Course

Run macro button on protected sheet

0

Hello,

I had created a simple macros for time monitoring but unable to run the macros button when the sheet is protected.

Attached is the excel file which I had created. Please let me know the procedure to locked cells & run macro. Also , please do in the attached excel sheet.

Answer
Discuss

Answers

0

I recommend a procedure like this one.

Sub SetProtection(Ws As Worksheet, _
                  ByVal Pw As String, _
                  ByVal LockMode As Boolean)
    ' 17 Oct 2017
    
    With Ws
        If .ProtectContents <> LockMode Then
            If LockMode Then
                .Protect Password:=Pw, _
                         DrawingObjects:=True, _
                         Contents:=True, _
                         UserInterfaceOnly:=True, _
                         AllowFiltering:=True
                         ' there are many more properties you can set
                .EnableSelection = xlNoRestrictions
            Else
                .Unprotect Pw
            End If
        End If
    End With
End Sub

It should be located in a standard code module. In the attached workbook I called the module 'Utilities'. Observe that you can specify many properties of the protection object, both more and different ones than included in my code. Call the sub with a line of code as below from anywhere in your workbook.

SetProtection Sheet1, Pw, True

The call has 3 arguments. The first is a worksheet object, specifying the worksheet to be [un]protected. The second one is the password. The last one can be either True (to protect the sheet) or False (to unprotect it). In the attached workbook the call is made from a Workbook_Open event procedure which must be in the 'ThisWorkbook' code module.

Now you can unprotect the sheet, run your macro and protect your sheet again. The call to unprotect might be at the beginning of your macro and its last line might be a call to reinstate protection.

BTW, I recommend that you check the checkbox "Require variable declaration" in VBE/Tools/Options -> Editor. If you do, VBA will add "Option Explicit" at the top of each code sheet automatically (you can also add it manually) which will let VBA point out to you whenever you forgot to declare a variable, mis-declared it or typed a variable name wrong. I promise you, if that won't prolong your life in absolute terms then the useful and enjoyable part of it. :-).

Discuss


Answer the Question

You must create an account to use the forum. Create an Account or Login