Require Users to Enable Macros

**Dairy_Queen** · 11-28-2012, 12:24 PM

My thread on protecting data validation from being overwritten was solved, for which many thanks. But I now find that it only works if macros are enabled. Company policy is that they must be manually enabled when a workbook is opened. If macros are not enabled, the data validation can still be overwritten by the users, and will be, almost invariably.

So is there a way to not let them work on this workbook until/unless macros are enabled?

Thanks in advance.

**Miraun** · 11-28-2012, 12:30 PM

Hey Dairy_Queen.

Kind of... I had a similar problem.

What I did was create a splash page that people hit before they get to the actual data within the book, explaining how to enable macros. Every worksheet of the workbook was hidden, password protected, and locked down. The VBA was also password protected, so they couldn't get at the obvious password that way either.

Then just had a huge button, that just did a VBA script to unlock, unhide all of the worksheets within the workbook, and allow the user to get to the data as per normal excel.

What you need to remember to do is before each save (upon save event), to revert it back to it's 'locked' state, so that whomever next opens the book has to go through the same enabling of macros, etc.

**Dairy_Queen** · 11-28-2012, 01:29 PM

OK, many thanks. It appears to be a problem not responsive to subtle hints, I gather -- no surprise.

My last day on this contract is Friday, and I'm new to using VBA. Would you feel able to post the scripts and more detailed instructions; I'm running out of time and would so love to really "nail" this assignment.

Thank you in any case.

**jeffreybrown** · 11-28-2012, 01:33 PM

Hi DQ,

See if this help...

http://xl-central.com/force-users-to...-workbook.html

**Miraun** · 11-28-2012, 01:37 PM

Please Login or Register  to view this content.

I removed some irrelevant stuff from there... but that gives you an idea.

In the ThisWorkbook code:

Please Login or Register  to view this content.

Hope that works... or at least helps.

**Miraun** · 11-28-2012, 01:39 PM

Jeff's link code looks like it may work better?
Might be more robust?

May be overkill... I dunno, I didn't want to look into it too deeply. But either way, you should be set.