CODEWITHSUNDEEP
excelvbapassword-protection
confused_Zebra
confused_Zebra

Reputation: 17

I am having trouble running a macro on a protected sheet

I am trying to run a simple macro by button click, but when I click the button while the sheet is protected, nothing happens. When I unprotect the sheet and click the button, the macro runs without issue.

Here is the macro is question:

Sub ApproveAllPending()
    Columns("B").Replace What:="Pending", _
                            Replacement:="Approved", _
                            LookAt:=xlPart, _
                            SearchOrder:=xlByRows, _
                            MatchCase:=False, _
                            SearchFormat:=False, _
                            ReplaceFormat:=False
End Sub

I saw a few posts with me.protect UserInterfaceOnly:=True, but I wasn't able to get that working.

Edit:

I tried adding this as well, and it's not working either.

Sheet1.Protect Password:="password", UserInterFaceOnly:=True

Upvotes: 0

Views: 46

Answers (1)

taller
taller

Reputation: 18778

  • Update password as needed
Option Explicit
Sub ApproveAllPending()
    Sheet1.Unprotect Password:="mypw"
    Sheet1.Columns("B").Replace What:="Pending", _
                            Replacement:="Approved", _
                            LookAt:=xlPart, _
                            SearchOrder:=xlByRows, _
                            MatchCase:=False, _
                            SearchFormat:=False, _
                            ReplaceFormat:=False

    Sheet1.Protect Password:="mypw"
End Sub
  • If you prefer to use UserInterFaceOnly, the code should be placed in the Workbook_Open event. This is because the workbook loses the UserInterFaceOnly setting after reopening.
Option Explicit

Private Sub Workbook_Open()
    Sheet1.Protect Password:="mypw", UserInterFaceOnly:=True
End Sub

Upvotes: 0

Related Questions