Option to hide the sheet when "Disable Macros" is clicked

**sivdin** · 04-29-2009, 07:39 AM

Dear Experts,

Is there any option to deactivate the "Disable macros"

Or

When i open the userform it asks for enable and diable macros. If i click "Disable macros" then the users can manipulate the data inside the excel sheet. i need to avoid this problem.

So, is there any option that if i do "Disable macro" then the corresponding sheet for the which the Userform is linked should not be visible or hidden.

Please let me know the procedure!!!

Regards,
Alex

**dominicb** · 04-29-2009, 08:04 AM

Good afternoon sivdin

Originally Posted by sivdin

Is there any option to deactivate the "Disable macros"

So, is there any option that if i do "Disable macro" then the corresponding sheet for the which the Userform is linked should not be visible or hidden.

There is no foolproof way to force a user to enable macros, however there is a workaround technique. The entire workbook apart from one sheet needs to be hidden, and a set of macros will unhide all the necessary sheets on opening. If the user doesn't accept macros, only one sheet (which usually carries a warning) can be seen. When the user closes a workbook, a macro has to intercept this call, hide all but one of the sheets and force a save.

Not perfect, but it (kind of

) works.

There is a ready made solution here for you to use.

Alternatively, there is a really clever tip on the subject - a slightly different method - here from Chip Pearson.

HTH

DominicB

**sivdin** · 05-04-2009, 03:56 AM

Dear

The macros are not working in my workbook.
I copied the code. But when the click either "Enable Macros" or "Disable macros" only one sheet is visible for ,e.

My requirement is when i click "Disable macros" and open the workbook, the sheet for which i created the useform should not be visible. Only if i do "Enable macros" then only my required sheet should open

Solutions Please!!!!

**royUK** · 05-04-2009, 04:10 AM

See this example

**sivdin** · 05-04-2009, 05:50 AM

Dear Roy,

Thanks for your tips

But,the workbook which i am using is shared. The exaample is not working for me.

Is ther any other method to automatically hide the sheet when "Disable macros" is clicked. At the same time when i click "Enable macros" my required sheet should open.

Please tell me any other available procedures.

**royUK** · 05-04-2009, 05:52 AM

You need to unshare a workbook before adding code to it. Once added you can then share the workbook again

**sivdin** · 05-04-2009, 06:03 AM

Dear Roy,

I have unshared the workbook. then after saving changes i shared the workbook again.Even then its not working.

**royUK** · 05-04-2009, 06:28 AM

It works for me

**sivdin** · 05-06-2009, 06:58 AM

Hello Domini,
Thanks for your help.
The example works fine. But Macros work for both the conidtions.
I.e when "Disabled or " Enabled" the sheets gets hidden.

But my rerquirement is the sheet should be hidden only when i click "Disable macros".

Any solutions!!!!

Alex

**shg** · 05-06-2009, 11:07 AM

You can't hide a sheet via a macro if macros are disabled; that's why it has to be hidden beforehand, and then unhidden (by a macro) when macros are enabled.