Macro: sheet protect/unprotect problem

blaksnm

Well-known Member
Joined
Dec 15, 2009
Messages
526
Office Version
  1. 365
Platform
  1. Windows
Hey Guys
I have made two OptionButtons to activate 2 different macros - both used in a protected sheet.

The macros unprotect the current sheet , do stuff and then protect it again.

Running macro1 works all right on 1st move.
However, when I run macro2 (by activating OptionButton2) after running macro1 (activated by OptionButton1) the macro will not unprotect the sheet as expected, and abend the macro.

The same result when reversing the actions.

The funny part is that when the procedure is done/macros are run by run directly in VBA - it seems to work well...!:confused:

I've obiously have missed something on the road here - but what?


Here is an extract of my macros:

Sub ActivateOptionButton1()
Application.ScreenUpdating = False
ActiveSheet.Unprotect

' Do stuff

ActiveSheet.Protect DrawingObjects:=True, Contents:=True, Application.ScreenUpdating = True
End Sub

Sub ActivateOptionButton2()
Application.ScreenUpdating = False
ActiveSheet.Unprotect

' Do stuff

ActiveSheet.Protect DrawingObjects:=True, Contents:=True, Scenarios:=True
Application.ScreenUpdating = True
End Sub
 

Excel Facts

Add Bullets to Range
Select range. Press Ctrl+1. On Number tab, choose Custom. Type Alt+7 then space then @ sign (using 7 on numeric keypad)

blaksnm

Well-known Member
Joined
Dec 15, 2009
Messages
526
Office Version
  1. 365
Platform
  1. Windows
UPS - problem solved ... :)
I was to quick on the trigger

Another question in this line:
Are there any simple way to unprotect/protect ALL sheets in the workbook as one block?
 

Forum statistics

Threads
1,144,679
Messages
5,725,738
Members
422,636
Latest member
GatorsBucs

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
Top