How do I force users to enable macros on open?

You can't

EDIT: I thought I'd better qualify my brief statement a bit more - my reply was along the lines of http://j-walk.com/ss/excel/tips/tip100.htm

_________________
Chris


This message was edited by Chris Chattin on 2002-12-13 09:11

Can I have the workbook close down if a user chooses not to enable?

Can I have the workbook close down if a user chooses not to enable?

Download the enable_macros.zip from here

http://www.ozgrid.com/download/default.htm

Hi Mary,

Your last point is a bit like the chicken and egg situation. If you haven't enabled macros how can you force the workbook to be closed?

The best alternative is the one that you have already discovered, hiding all but one of the sheets in the workbook BeforeClose event and showing them again with the Open event.

Something along these lines:

In the ThisWorkbook object:<pre>Private Sub Workbook_BeforeClose(Cancel As Boolean)
Module1.HideAll
End Sub

Private Sub Workbook_Open()
Module1.ShowAll
End Sub</pre>
And in a general module, eg Module1:<pre>Sub HideAll()
Dim ws As Worksheet
'hide all sheets bar one, named "Warning"
'call from Workbook_BeforeClose
Application.ScreenUpdating = False
ThisWorkbook.Worksheets("Warning").Visible = xlSheetVisible
For Each ws In ThisWorkbook.Worksheets
If ws.Name<> "Warning" Then ws.Visible = xlSheetVeryHidden
Next
Application.ScreenUpdating = True
End Sub

Sub ShowAll()
Dim ws As Worksheet
'show all sheets, except Warning
'call from Workbook_Open
Application.ScreenUpdating = False
For Each ws In ThisWorkbook.Worksheets
If ws.Name<> "Warning" Then ws.Visible = xlSheetVisible
Next
ThisWorkbook.Worksheets("Warning").Visible = xlSheetVeryHidden
Application.ScreenUpdating = True
End Sub</pre>
Keep in mind that with Excel if the user is knowledgeable and really 'wants in' then they will find a way. The above should deter the casual user though.

EDIT: Sorry Roy. Yes, or download Dave's example workbook (which I suspect will b ealong the same lines).
_________________<font color="blue"> «««<font color="red">¤<font color="blue"><font size=+1>Richie</font><font color="red">¤<font color="blue"> »»»</font>

</gif>
This message was edited by Richie(UK) on 2002-12-13 08:56

Can I have the workbook close down if a user chooses not to enable?

Click to expand...

No, if Macros are not enabled, you cannot get the workbook to do anything.

But your original idea...

I’ve read that you can write code and make all worksheets VeryHidden except for a sheet informing users that they must enable macros. If macros are enabled, then the warning sheet becomes Very Hidden. One place tells me it can be done successfully, another posting I read says it is difficult to implement properly. Any thoughts/ideas? Is there a best way to do it?

Click to expand...

Instead of "writing code" to make all the other sheets besides the warning sheet "VeryHidden", why not just Hide the sheets (Format>Sheet>Hide) and leave only the warning sheet visible?

Then your macro, if enabled, would simply make the Warning Sheet VeryHidden, and all the others visible.

I think I ran across an example of that, lemme see if I can dig it up...

Richie, If you close the file and don’t save changes and then open it the sheets are visible, is there a way around this?

Hi Tim,

My apologies - a hastily written example (well thats my excuse anyway!).

Add the following amendments (based on Dave's example which you could download for a 'working' example).

1. Add a public variable in the general module: Public bIsClosing As Boolean
2. Add bIsClosing = False to the start of the ShowAll module.
3. Replace the event code with:
<pre>Private Sub Workbook_BeforeClose(Cancel As Boolean)
bIsClosing = True
End Sub

Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
If Cancel = True Or bIsClosing = False Then Exit Sub
Module1.HideAll
End Sub

Private Sub Workbook_Deactivate()
If bIsClosing = False Then Exit Sub
Module1.HideAll
End Sub

Private Sub Workbook_Open()
Module1.ShowAll
End Sub</pre>
HTH

On 2002-12-13 08:19, Maryz wrote:
I’ve read that you can write code and make all worksheets VeryHidden except for a sheet informing users that they must enable macros. If macros are enabled, then the warning sheet becomes Very Hidden. One place tells me it can be done successfully, another posting I read says it is difficult to implement properly. Any thoughts/ideas? Is there a best way to do it?

Click to expand...

Yes, there is a better way to do it, Maryz. Put the code in an add-in. If you are serious about software development in the MS Office environment, learning about add-ins (both application level and the more advanced COM add-ins) will be one of the best investments you will ever make.
Here's what I wrote on the same subject a couple of weeks ago (http://www.mrexcel.com/board/viewtopic.php?topic=31338&forum=2&start=10)
--- begin quote ---
I've been meaning to write a tutorial on the subject. Just haven't gotten around to it. In the meantime, check msdn.microsoft.com. Specifically,
Microsoft Office XP Developer
Building Application-Specific Add-ins
http://msdn.microsoft.com/library/d...ml/deovrbuildingapplicationspecificaddins.asp
and
Microsoft Office XP Developer
Add-ins, Templates, Wizards, and Libraries
http://msdn.microsoft.com/library/d...html/deovraddinstemplateswizardslibraries.asp.

Concentrate on the 'Application specific add-ins' and not the 'COM Add-Ins.'

Basically, in XL, after saving the workbook with the code, save it as an add-in (File | Save As...) Close the file, then load the add-in through Tools |Add-Ins...

A key point to remember is that code in the add-in that refers to ThisWorkbook refers to the add-in workbook, while a reference to ActiveWorkbook refers to the open workbook, whatever that might be.

***** Please make very very sure you always save the file as a normal workbook before saving it as an add-in. Otherwise, you may be unable to access the worksheets / source in the add-in file! *****
--- end quote ---