Protection issues with marco and vba running

amcmlt

Board Regular
Joined
Feb 2, 2015
Messages
82
Good afternoon,

Having an issue writing/finding the correct code that would allow a protected worksheet to work as normal until VBA is run that generates a report that would override the protected sheet.

The worksheet contains unlocked and locked cells that require user input to build a database of line items. I have used the Form Control Check Box as the users method to include or exclude line items. A TRUE check box will become a 1 in the same row as the check box. Rows A1 to A1382 there is either a 0 or a 1 based on the need of the user. 0's are not needed in the final report.

Final report will not need Column's A:D

I have the code written (poorly sorry to state) and was working to hide rows and columns but when I password protect the sheet, "Project Price List", I get the error. I think I need code that would allow the check boxes to work as expected, all locked cells to remain locked but allow the report macro to run.

Is it possible to write code that would allow this to happen and if so, please let me know how to write it.

Thank you!
 

Some videos you may like

Excel Facts

Waterfall charts in Excel?
Office 365 customers have access to Waterfall charts since late 2016. They were added to Excel 2019.

dUBBINS

Well-known Member
Joined
Feb 9, 2015
Messages
504
Okay, I think you are just wanting your code to unprotect your sheet, run the code, and then protect the sheet again. If so, add this to the beginning of your code somewhere:

ActiveSheet.Unprotect(password)

*replace "password" with your actual password

Then at the end of your code:

ActiveSheet.Protect(password)
 

V_Malkoti

Well-known Member
Joined
Jun 10, 2015
Messages
898
If you don't want your code to be bothered by the locked cells in the sheet, then protect sheet with UserInterfaceOnly argument:

Code:
Sheets("MySheet").Protect Password:="pass", UserInterfaceOnly:=True
 

dUBBINS

Well-known Member
Joined
Feb 9, 2015
Messages
504
@V_Malkoti...Are there advantages to using your solution over the one I posted? Just trying to learn. :)
 

V_Malkoti

Well-known Member
Joined
Jun 10, 2015
Messages
898

ADVERTISEMENT

Advantage:
1. No need to unlock the sheet for the macro to run.
2. In case of a misplaced protect/unprotect call or situation where macro crashes/halts between protect and unprotect calls, the sheet is still locked in UI.
3. No need to store password in code - you can run this code in VBA immediate pane just once and sheet is protected. And since there is no need to unprotect the sheet to run macro, no need to write function statements which may reveal the password.

Disadvantage:
1. Potentially macro code from other files/projects can also access/alter the sheet.
2. If you write protect/unprotect code in functions, then you technically have a (backup) place where you have the password saved, which might come in handy in case you forget it.
 
Last edited:

dUBBINS

Well-known Member
Joined
Feb 9, 2015
Messages
504
@V_Malkoti...Very much appreciate the detailed reply. Thank you kindly.
 

amcmlt

Board Regular
Joined
Feb 2, 2015
Messages
82
Many thanks to you both for sharing your knowledge!! Best wishes...
 

Watch MrExcel Video

Forum statistics

Threads
1,123,307
Messages
5,600,869
Members
414,411
Latest member
Snowmanaus

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