Detect whether macros are enabled? RESOLVED

[artslave]

I've been searching the board and the help files for this, and although I've found many other useful things, I haven't found the answer yet!

I don't want to disable the "macros must be enabled" warning when Excel opened -- I happen to find it useful! I just want to detect whether the user clicked "No" so I can give him a message explaining why the workbook is now such a dismal failure...

Can anyone tell me if/where this information is stored that I can detect it with VBA?

Thanks!
Catherine
This message was edited by artslave on 2002-06-28 09:43

 

[Richie(UK)]

Hi Catherine,

I don't know whether it is possible to do what you ask - I'll let somebody else answer that. I do, however, have a workaround.

Create a worksheet and call it 'Macros Disabled'. Write your message for the user on this sheet. Then when the workbook is closed, hide all sheets except this one. Open them again when the workbook is opened, and hide the message sheet. But, if the user doesn't enable macros all they get is the message sheet.

If this is of any use to you the code is below.

Option Explicit
Dim sht As Object, wbpwd As String
'
' Closing routine to hide all sheets except "Macros Disabled"
' Call from ThisWorkbook - Workbook_BeforeClose
'
Sub MacrosDisClose()
wbpwd = "letmein"
ActiveWorkbook.Unprotect Password:=wbpwd
Application.ScreenUpdating = False
Sheets("Macros Disabled").Visible = xlSheetVisible

For Each sht In ThisWorkbook.Sheets
If sht.Name <> "Macros Disabled" Then _
sht.Visible = xlSheetVeryHidden
Next

ActiveWorkbook.Protect Password:=wbpwd
ActiveWorkbook.Save ' force Save option
End Sub
'
' Opening routine to unhide all sheets if Macros Enabled
' Call from ThisWorkbook - Workbook_Open
'
Sub MacrosDisOpen()
wbpwd = "letmein"
ActiveWorkbook.Unprotect Password:=wbpwd
Application.ScreenUpdating = False

For Each sht In ThisWorkbook.Sheets
sht.Visible = xlSheetVisible
Next

Sheets("Macros Disabled").Visible = xlSheetVeryHidden
ActiveWorkbook.Protect Password:=wbpwd
Application.ScreenUpdating = True
End Sub

HTH

 

[Legacy 98055]

Hi Catherine.
If macros are diabled, how will you be able to tell your user?
One way I have found helpful is to hide every sheet except one when the workbook is closed. On this sheet, type a message telling the user to close the workbook and reopen with macros enabled or the workbook will not be usable. Here is an example:
Lets call the visible sheet "Warn".

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

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

</pre>
The code in the Workbook_BeforeClose will hide every sheet except "WARN". You must always leave one sheet visible anyway.
The code in Workbook_Open will make all the sheets visible and therefor usable. If macros are disabled then this code cannot run leaving the user with only one usable worksheet being sheet "WARN" which cointains your message about how useless your awesome workbook is if macros are disable.
You follow me?
Try it out.
Tom

 

[Richie(UK)]

Great minds think alike!

(Or is it 'Simple minds rarely differ'? :wink: )

 

[klb]

I like TsTom's way of doing this. I have a question though:

How can I modify the macro to hide my Charts also?

Thanks

 

[artslave]

Doh! Of course I can't use a macro to detect if macros are disabled, if macros are disabled -- if you follow me.

What can I say, it's been a long week....

Thanks to both of you for the simple and elegant solution; I'll be implementing it today.

I love this place!
Cath

 

[klb]

I like TsTom's way of doing this. I have a question though:

How can I modify the macro to hide my Charts also?

Thanks

 

[Mark O'Brien]

I think to modify TsTom's code in encompass charts as well you would simply change both instances of this code:

<pre>
Dim sh As WorkSheet
For Each sh In WorkSheets</pre>

To this:
<pre>
Dim sh As Sheet
For Each sh In Sheets</pre>

The reason for this is that the "Sheets" collection is basically a list of all of the sheets in the workbook including ChartSheets. The "Worksheets" collection is a pared down version of the Sheets collection in that it only contains the Worksheets. NOTE:: There is a similar collection for chartsheets called "Charts". I guess it's not called "ChartSheets" because charts can be embedded as well as being individual sheets.

HTH

 

[klb]

Thanks for your help. I finally came up with the following which hides both sheets and charts if macros are not enabled.

Private Sub Workbook_Open()
Dim sh As Worksheet
Dim ch As Chart
For Each sh In Worksheets
sh.Visible = xlSheetVisible
Next
For Each ch In Charts
ch.Visible = xlSheetVisible
Next
End Sub

Private Sub Workbook_BeforeClose(Cancel As Boolean)
For Each sh In Worksheets
If sh.Name <> "WARN" Then sh.Visible = xlSheetVeryHidden
Next
For Each ch In Charts
ch.Visible = xlSheetVeryHidden
Next
End Sub