How to make certain cells uneditable unless macro enabled

[Lanny]

I am using a worksheet which uses vba to force users to enable macros when they first open the sheet, but once the sheet is saved and reopened it doesn't force macros anymore (on purpose).

Is there a way to make it so some cells can not be edited unless macros are enabled.

The worksheet needs macros to display the proper rows/sheets based on one cell value so if that cell value changes macros will need to be enabled.

I want to allow other cells to be edited if macros are not enabled however.

Any suggestions? The worksheet has sensitive info so I cannot share on here.

Thanks!!!!

 

[Ludwig]

From what I understand in your query ...

1. Your spreadsheet's default setup
Set up the spreadsheet so the cells you want to be edited when macros are not enabled are not locked - this means your 4th para requirement is met.
I would assume this means the ones referred to in para 2 would be locked.

2. When you open the workbook and user enables macros, workbook_open should run. Code into this unlocking those cells you want user to edit.

3. In para 1 you mention once the sheet is saved it doesn't force macros anymore. So part of that code lock those cells you don't want them to edit any more. Only thing to note .. the spreadsheet then falls back into "default setup" mode - not sure if that's what you want.
I'd do that by coding a macro with a boolean parameter to "lock" or "unlock" the specific cells. Call it with "false' when macros are enabled, and 'true' once sheet is saved (or whichever way you define the boolean to function). This way you know you're changing the same cells each time and if the list gets updated, it'll be updated for both occasions.

If you need something more complex, such as "detect" when macros are enabled", I don't know off the top of my head of any event that fires on that happening.