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

Bring active cell back into view
Start at A1 and select to A9999 while writing a formula, you can't see A1 anymore. Press Ctrl+Backspace to bring active cell into view.

Lewiy

Well-known Member
Joined
Jan 5, 2007
Messages
4,284
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”
 

i8ig

Board Regular
Joined
Jul 17, 2007
Messages
122
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'?
 

Lewiy

Well-known Member
Joined
Jan 5, 2007
Messages
4,284
That is correct (although as long as the UnProtect code is before anything which amends the worksheet, it will be fine)
 

i8ig

Board Regular
Joined
Jul 17, 2007
Messages
122
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
 

Forum statistics

Threads
1,181,053
Messages
5,927,852
Members
436,573
Latest member
CMR237

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