How to make certain cells uneditable unless macro enabled

Lanny

New Member
Joined
Oct 12, 2018
Messages
4
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!!!!
 

Some videos you may like

Excel Facts

Pivot Table Drill Down
Double-click any number in a pivot table to create a new report showing all detail rows that make up that number

Ludwig

Board Regular
Joined
Apr 7, 2003
Messages
50
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.
 

Watch MrExcel Video

Forum statistics

Threads
1,122,886
Messages
5,598,660
Members
414,252
Latest member
Tejeci

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