Neat trick to force users to enable macros.

The Tamer

The Tamer

Well-known Member
Joined
Jun 10, 2004
Messages
520
I put this post in the Questions section, but then figured it wasn't a question... What do you think?

I have a spreadsheet that initiates security measures on openng my workbook - but of course, a user can bypass this by simply disabling macros on workbook open.

Here's my solution (if it has been said before I apologise!):

1. On the workbook you wish to protectcreate a "splash screen" on a blank page saying "You cannot use this sheet if you have disable macros"
Then in a " Sub Workbook_BeforeClose " macro, set all the sheets (apart from the Splash sheet) to " xlVeryHidden ".

2. In the " Private Sub Workbook_Open " part set the sheets to " visible "

This way all the sheets will be hidden once the workbook is closed, and if a user clicks " Disable Macro " on opening the workbook - he won't be able to see anything.
 

Excel Facts

What do {} around a formula in the formula bar mean?
{Formula} means the formula was entered using Ctrl+Shift+Enter signifying an old-style array formula.
No offence taken Juan!

I just got a similar reply on the questions board (which post I was going to delete after I posted it in here, but couldn't cos someone replied). Actually, the reply recommended using an add-in instead.

I'm grateful to you guys for the knowledge I receive - but I'm not embarrased, after all, everything you know you had to learn once, right?

Cheers
 
Well the good thing about it, is like me, you stumbled across the solution on your own. That means your smart enough to figure it out, and generous enough to share it. Just because someone invented it before you doesn't mean you can't invent it again. Just look at Bill Gates, the billionare who's never had a unique idea in his life :diablo:
 
Cbrine said:
.... Just look at Bill Gates, the billionare who's never had a unique idea in his life :diablo:
Click to expand...

I dunno, there weren't *that* many people didn't think the Internet would take off, were there? :twisted:
 
One thing to remember if distributing a workbook that's been set up like this is that you are forcing the user to save changes when they quit. If, like me, you sometime bugger up what you're doing completely and decide to pick up from where you last left off you won't be able to because you'll have saved over the previous version just by closing it.

What you need is an Workbook_AfterSave event so that you can set the sheets to hide/unhide as you save and then go back to normal afterwards ready for you to carry on.

Anyone know a way to do that?

Nick
 
One thing to remember if distributing a workbook that's been set up like this is that you are forcing the user to save changes when they quit. If, like me, you sometime bugger up what you're doing completely and decide to pick up from where you last left off you won't be able to because you'll have saved over the previous version just by closing it.
Click to expand...

Not so: You can close the workbook without saving it and the next time you open it, if you do not enable macros, the sheets will be veryhidden.
 
Not so: You can close the workbook without saving it and the next time you open it, if you do not enable macros, the sheets will be veryhidden.
Click to expand...

yes, but it will save when you exit whether you want it to or not. If I don't want to save the last changes I made I've got no way of exiting without saving. Unless I undo all of my changes and THEN save. My point is that, although this can be useful, the times I've tried to use it I've found it takes less explanations to just say to people "you need to enable macros" rather than get people to undo everything they don't want saved before quitting.
 
You must log in or register to reply here.

Forum statistics

Threads
1,223,707
Messages
6,174,000
Members
452,542
Latest member
Bricklin

Share this page

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
Back
Top