Force user to enable macros, or close file if they don't?

[taffy-cats]

I have a workbook_beforesave macro that *must* be enabled. If it's not, I don't want users to open my spreadsheet.

Is there a way to a) force users to enable macros, b) change the enable macro dialog box to tell them they have to, or c) force the spreadsheet to close itself if they don't enable macros?

 

[Legacy 98055]

Here's how I do it...
Hide all of your sheets except one.
You have to leave one visible, I think...
Anyway, place a message on this sheet telling your users that macros must be enabled to use your wb.
If they enable, then unhide all of your sheets.

Let's assume that the sheet with the "You must
Enable Macros Message" is called "Message"
Try this:

<pre>

Private Sub Workbook_BeforeClose(Cancel As Boolean)
Dim s
For Each s In Worksheets
If s.Name <> "Message" Then _
s.Visible = xlSheetVeryHidden
Next
End Sub

Private Sub Workbook_Open()
Dim s
For Each s In Worksheets
s.Visible = xlSheetVisible
Next
End Sub

</pre>


Tom

 

[Rocky E]

On 2002-04-30 10:33, taffy-cats wrote:
I have a workbook_beforesave macro that *must* be enabled. If it's not, I don't want users to open my spreadsheet.

Is there a way to a) force users to enable macros, b) change the enable macro dialog box to tell them they have to, or c) force the spreadsheet to close itself if they don't enable macros?

Hi Taffy!
I was raised in Strongsville, Ohio.

This is one of those I think I understand, but I can only sudgest where to look for the answer.

If you can set you stuff up in an XLA file with an AUTOEXEC the user will never see the Enable Macros prompt. That will mean doing it as an Add-In. Poke around the startup options for Excel. I really hope this helps.

Rocky...

 

[Legacy 98055]

Raised in Cincy...
Rocky's answer would be a more eloquent and complete solution. My solution is easily defeated, but if the data is not real sensitive, it suffices for the everyday user.
Tom

 

[taffy-cats]

Thanks for the great suggestions - will try them both as soon as I'm out of meetings today!

Don't know if the add-ins will work since I'm at a large site & have little control over what gets published to everyone, but it's definitely worth a try.

And what, is everyone from Ohio now?!?
:~)

 

[copleyr]

This is very helpful. Is there any way to do this in Powerpoint as well?!