Protection issues with marco and vba running

[amcmlt]

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!

 

[dUBBINS]

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]

If you don't want your code to be bothered by the locked cells in the sheet, then protect sheet with UserInterfaceOnly argument:

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

 

[dUBBINS]

@V_Malkoti...Are there advantages to using your solution over the one I posted? Just trying to learn.

 

[V_Malkoti]

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.

 

[dUBBINS]

@V_Malkoti...Very much appreciate the detailed reply. Thank you kindly.

 

[amcmlt]

Many thanks to you both for sharing your knowledge!! Best wishes...