Strong-arm tactic for enabling Macro
If you want the workbook saved, why prompt the user to save it. Just save
it yourself and go on - you are going to have to save it anyway to save your
page settings.
I believe in at least one version of excel there was a bug with
ThisWorkbook.Saved when used in the beforeclose event. If a user had that
version, they would could end up in an endless loop.
--
Regards,
Tom Ogilvy
"Hari" wrote in message
...
Hi,
I came across a post in the group where one could hide sheets if macros
arent enabled and unhide them only if they are enabled, so that user is
forced to enable them.
I have a spreadsheet where Im getting data filed from different people. It
has 3 worksheets out of which worksheet "Raw Data" is for entering data,
"Instructions" is basically a guideline/help file sort of thing containing
text on how the data in Raw data worksheet is to be entered. Im using lots
of macros in Raw Data worksheet to check for internal data inconsistencies
so that in case of problems the person entering data could correct it
rather
than me sending back and asking for corrected version.
The third worksheet "Warning" is basically tells the user to enable
macros.
I wrote the following code and inserted it in workbook module.
Option Explicit
Private Sub Workbook_Open()
Sheets("Instructions").Visible = True
Sheets("Raw Data").Visible = True
Sheets("Warning").Visible = xlVeryHidden
ActiveWorkbook.Save
End Sub
Private Sub Workbook_beforeclose(Cancel As Boolean)
If ActiveWorkbook.Saved = True Then
Sheets("Warning").Visible = True
Sheets("Instructions").Visible = xlVeryHidden
Sheets("Raw Data").Visible = xlVeryHidden
ActiveWorkbook.Save
Cancel = False
Else
MsgBox "Please save the workbook before exiting"
Cancel = True
End If
End Sub
Please note it seems to work as I wanted but I wanted to know is whether
am
missing something in the above code (I mean logic wise).
Also is there a more efficient way of writing (or getting the macro
executed) in the above macro.
Regards,
Hari
India
|