Neat trick to force users to enable macros.

[The Tamer]

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.

 

[Juan Pablo González]

Yep... (not trying to be rude), common tecnique:

http://www.danielklann.com/excel/force_macros_to_be_enabled.htm

 

[The Tamer]

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

 

[Zack Barresse]

... everything you know you had to learn once, right?

Sometimes two or three times!!

 

[Cbrine]

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

 

[RichardS]

.... Just look at Bill Gates, the billionare who's never had a unique idea in his life

I dunno, there weren't *that* many people didn't think the Internet would take off, were there?

 

[litrelord]

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

 

[klb]

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.

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.

 

[litrelord]

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.

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.

 

[The Tamer]

I sense a fight coming on! i'm not a war-monger - honest!