Macro added to QAT running/opening original workbook

[marigold322]

Hi there,

I have been using macros to protect/unprotect all the sheets in my workbook. I'll leave both codes below for your reference.

I just recently learned that macros can be added into the quick access toolbar (QAT), and so I thought this would be perfect to save me time opening the developer and running the macros manually. Unfortunately I discovered that running macros from the QAT is just opening the first workbook I tried this on (tried adding the macros to the QAT). So when I open a workbook and use the icon, it runs the macro on an original workbook.

Is there a way to edit these macros so that they can be run from my QAT but will only protect/unprotect the sheet i am working on?

Any advice is appreciated, please let me know if I can clarify any part of my inquiry or if anything is unclear. Thank you kindly for your time.

PROTECT:

Sub ProtectAllSheets()
Dim ws As Worksheet
Dim password As String
password = "Medavie"
For Each ws In Worksheets
ws.Protect password:=password
Next ws
End Sub

UNPROTECT:

Sub unprotect_all_sheets()
On Error Goto booboo
unpass = InputBox("Please enter the password:")
For Each Worksheet In ActiveWorkbook.Worksheets
Worksheet.Unprotect Password:=unpass
Next
Exit Sub
booboo: MsgBox "There is s problem - check your password, capslock, etc."
End Sub

 

[Wookiee]

If I understand it correctly, you want two macros to enable you to toggle password-protection on your active sheet. I believe these two will do the trick.

Sub Marigold_Protect()

ActiveSheet.Protect Password:="Medavie"

End Sub


Sub Marigold_UnProtect()

ActiveSheet.Unprotect Password:="Medavie"

End Sub

 

[marigold322]

I would like the macro to protect/unprotect all of the sheets in the active workbook, and i would like to be able to run the macro from my QAT if at all possible.

 

[Wookiee]

I would like the macro to protect/unprotect all of the sheets in the active workbook, and i would like to be able to run the macro from my QAT if at all possible.

I don't think you can modify the QAT using VBA (at least I haven't discovered that ability so far). But I put these macros together for you.

Put them in your Personal macros file and run MarigoldsMacroBar; that will create a 2-button section in the Add-Ins section of the ribbon.

Sub AddMacroButton _
  (cBar As Variant, lIcon As Long, _
   sMacro As String, sCaption As String)
'Adds Macro Button To Toolbar
'Crafted  3 May 2018 by Jason B White


Dim cbcButton  As Variant

Set cbcButton = cBar.Controls.Add

With cbcButton

   .FaceId = lIcon
   .OnAction = sMacro
   .Caption = sCaption
   .Visible = True

End With

Set cbcButton = Nothing

End Sub


Sub MarigoldsMacroBar()
'Crafted by Wookiee at MrExcel.com

'Declare Variables
Dim cBar      As CommandBar
Dim cControl  As CommandBarControl

Const STRBARNAME As String = "Pass Protect"

On Error Resume Next
  Application.CommandBars("Worksheet Menu Bar").Controls(STRBARNAME).Delete
  Application.CommandBars("Worksheet Menu Bar").Delete
On Error GoTo 0

Set cBar = Application.CommandBars.Add
cBar.Name = STRBARNAME
cBar.Visible = True

Call AddMacroButton _
   (cBar, 225, "Protect_All", _
   "Password Protect All Sheets In Active Workbook")

Call AddMacroButton _
   (cBar, 277, "UnProtect_All", _
   "Remove Password Protection From All Sheets In Active Workbook")

End Sub


Sub Protect_All()

Dim wksSheet As Worksheet

For Each wksSheet In ActiveWorkbook.Worksheets

  wksSheet.Protect Password:="Medavie"

Next wksSheet


End Sub


Sub UnProtect_All()


Dim wksSheet As Worksheet
Dim strPass  As String

strPass = InputBox("Please provide password:")

On Error GoTo BooHoo
For Each wksSheet In ActiveWorkbook.Worksheets

  wksSheet.Unprotect Password:=strPass

Next wksSheet

Exit Sub

BooHoo:
MsgBox "Password problem detected!" & _
  vbNewLine & "Please double-check your password.", vbCritical

End Sub