Macro added to QAT running/opening original workbook

marigold322

New Member
Joined
Mar 9, 2023
Messages
6
Office Version
  1. 365
Platform
  1. Windows
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:
VBA Code:
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:
Code:
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
 

Excel Facts

What does custom number format of ;;; mean?
Three semi-colons will hide the value in the cell. Although most people use white font instead.
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.

VBA Code:
Sub Marigold_Protect()

ActiveSheet.Protect Password:="Medavie"

End Sub


Sub Marigold_UnProtect()

ActiveSheet.Unprotect Password:="Medavie"

End Sub
 
Upvote 0
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.
 
Upvote 0
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.

VBA Code:
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
 
Upvote 0

Forum statistics

Threads
1,213,536
Messages
6,114,207
Members
448,554
Latest member
Gleisner2

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top