Forcing Macros to Run (Else Deny Access to Workbook)

  • Thread starter Thread starter Alan
  • Start date Start date
A

Alan

Hi All,

I seem to recall occasionally opening a workbook that has macros in it and,
upon asking Excel to 'disable macros', being informed that the workbook
contained macros written in Excel 4 which cannot be disabled.

In that situation, if I recall correctly, I was given two choices:

1) Open the workbook with the macros enabled; or
2) Don't open it at all.

At the time I was quite annoyed, but now it seems to me to be a good way to
avoid staff from disabling the macros within a shared workbook where I
really need for them to be active.

Is there still a way, using Excel 2000 (or Excel 97), to create an Excel 4
macro that will ensure users have to enable macros?

The macro does not need to do anything in particular I guess, just being
there should be enough?

Thanks in advance,

Alan.
 
One possible solution

A good way is to hide all sheets except one and unhide them in
the workbook open event.
Place a message on that sheet with "you must enabled macro's to work with this file"
And hide the sheets in the beforeclose event.
So the user can't use the workbook if he disable macro's.
If he do the workbook open event don't run so there are no sheets
to work with

Some example code to do this
Sheet 1 stay always visible

Sub HidealmostAll()
Dim a As Integer
For a = 2 To Sheets.Count
Sheets(a).Visible = xlVeryHidden
Next a
End Sub

Sub ShowAll()
Dim a As Integer
For a = 2 To Sheets.Count
Sheets(a).Visible = True
Next a
End Sub
 
Hi Alan

If you protect your project also he can't with xlVeryHidden

Alt-F11
Tools>VBA project properties
See the protection tab
 
Back
Top