Close Excel if macros not enabled

[philbennison]

Hi,

I have a Excel spreadsheet, that is used by lots of people and i have a very basic login screen, when the username is entered, the spreadsheet can be ammeded by a userform
As usual when the spreadsheet is opened Excel asks wether to enable or diable macros.
Is there any way that if the the user says to diasable the macros, the spreadsheet will not open ?

I think i already know the answer, but thought i would ask anyway

Thanks

Phil

[Leith Ross]

Hello Phil,

It's a catch-22. Excel checks for macros before it finishes loading, but you need macros to run the login screen. You could create an Add-In for the login. Provided the user has their security set to trust all Add-Ins and Templates, your Add-In could launch your program.

Sincerely,
Leith Ross

[royUK]

You can't, most ways involve hiding relevant sheets on closing & obviously these will remain hidden until macros are enabled & the code to unhide them runs.

[Big Chris]

What I do is to have a worksheet which says "You need to enable macros to enable data to be entered...." and then gives instructions on how to do this....click 'Enable macros' or go to 'Tools' > 'Marcos' > 'Security' and set to medium (I'd never encourage someone to set it to low), then close and reopen the workbook.

I then ensure that...

1. the file is saved with that screen showing and the 'Data Entry' screen hidden, and...
2. that an auto_close macro switches to the macro info screen before closing and hides the 'Data Entry' sheet, to ensure that if it's opened with macros disabled that is what the user will see.
3. install an auto_open macro which hides the macro info sheet and opens the 'Data Entry' sheet IF macros are enabled.

Make sense? It gets round it for me and informs the user about how what to select or what to do if their macro security level is set at High.

Hope that helps.

Chris.

[Big Chris]

Tell you what....I'll stop being lazy and just attach an example....

Just don't forget to protect the macros or someone could mess it up. Open it with macros disabled and enabled to see the difference.

[philbennison]

Big Chris,

Thanks, for the explanation, and a large thanks for the example. This is exactly what i need, however it raises other quetions

1 How did you hide the tabs ?
2 How can i gain access to the other work sheets
3 Can i nick your front sheet :-), and what is your password, as it is password protected

Once again thanks for all your help

Phil

[Big Chris]

No worries Phil - Glad to have helped.

Re...
1 How did you hide the tabs ?
Via Tools > Options > View and uncheck the tab box....alternatively, if you mean how do you make the other worksheets 'veryhidden'? then look at the macro via Visual Basic Editor....it should be pretty clear from the code.

2 How can i gain access to the other work sheets?....there's only one. You can go to Visual Basis Editor and set the hidden worksheet as 'visible'.

3 Can i nick your front sheet :-), and what is your password, as it is password protected.
Sure you can...the password is 'hello'.

Cheers,

Chris

[philbennison]

Big Chris,

Once again.

Thanks very much for your helps. Works like a charm :-)