Protect sheet stops Macro

i8ig

Board Regular
Joined
Jul 17, 2007
Messages
122
When I protect my worksheet, the macro will not run part of its function, Is this normal? Is there a workaround?
 

Excel Facts

Waterfall charts in Excel?
Office 365 customers have access to Waterfall charts since late 2016. They were added to Excel 2019.
If the worksheet is protected, the macro will not be able to perform functions which require changes in the worksheet. Use the following lines in your macro to unprotect and re-protect the sheet programmatically:
Code:
Worksheets(“Sheet1”).UnProtect “password”
Code:
Worksheets(“Sheet1”).Protect “password”
 
Upvote 0
I take it this should be the first line of code in the macro? and "Sheet 1" is actually the sheet name and 'password' is actually the password'?
 
Upvote 0
That is correct (although as long as the UnProtect code is before anything which amends the worksheet, it will be fine)
 
Upvote 0
Another question: Is this the first line Worksheet......UnProtect above or below: Private Sub Worksheet_Change(ByVal Target As Range)?

Is the Protect code the next code or does it go after the various scenarios the macro performs?

When I put it in under the "Private Sub Worksheet_Change(ByVal Target As Range)" then I receive a run time error '9' Subscript out of range message. If I press the Debug button, it takes me to the: ' Worksheets("December 2008").Unprotect "i8ig"" line of code. Any suggestions
 
Upvote 0

Forum statistics

Threads
1,213,532
Messages
6,114,177
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