PDA

View Full Version : force enable macros



jiddings
03-05-2009, 08:02 AM
I am using the code from: :help
http://www.vbaexpress.com/kb/getarticle.php?kb_id=379
to force users to enable macros. It is implemented in an XLS (Excel 2003) file and had run OK with Excel 2003. We have now changed over to Excel 2007 and are using the same file with the same macro. The file appears to be causing the closing and restarting Excel 2007 when closing the file.

Is there something in the code that would be causing this issue in Excel 2007?
If so, what should I be changing to correct this problem??
Jack :banghead:

Simon Lloyd
03-05-2009, 08:06 AM
Please supply the code, that way it saves people having to go to the kb, download the article and then look through it!

jiddings
03-05-2009, 08:25 AM
My apologies .... the code is as follows:

Option Explicit

Const WelcomePage = "Macros"

Private Sub Workbook_BeforeClose(Cancel As Boolean)
'Turn off events to prevent unwanted loops
Application.EnableEvents = False

'Evaluate if workbook is saved and emulate default propmts
With ThisWorkbook
If Not .Saved Then
Select Case MsgBox("Do you want to save the changes you made to '" & .Name & "'?", _
vbYesNoCancel + vbExclamation)
Case Is = vbYes
'Call customized save routine
Call CustomSave
Case Is = vbNo
'Do not save
Case Is = vbCancel
'Set up procedure to cancel close
Cancel = True
End Select
End If

'If Cancel was clicked, turn events back on and cancel close,
'otherwise close the workbook without saving further changes
If Not Cancel = True Then
.Saved = True
Application.EnableEvents = True
.Close savechanges:=False
Else
Application.EnableEvents = True
End If
End With
End Sub

Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
'Turn off events to prevent unwanted loops
Application.EnableEvents = False

'Call customized save routine and set workbook's saved property to true
'(To cancel regular saving)
Call CustomSave(SaveAsUI)
Cancel = True

'Turn events back on an set saved property to true
Application.EnableEvents = True
ThisWorkbook.Saved = True
End Sub

Private Sub Workbook_Open()
'Unhide all worksheets
Application.ScreenUpdating = False
Call ShowAllSheets
Application.ScreenUpdating = True
End Sub

Private Sub CustomSave(Optional SaveAs As Boolean)
Dim ws As Worksheet, aWs As Worksheet, newFname As String
'Turn off screen flashing
Application.ScreenUpdating = False

'Record active worksheet
Set aWs = ActiveSheet

'Hide all sheets
Call HideAllSheets

'Save workbook directly or prompt for saveas filename
If SaveAs = True Then
newFname = Application.GetSaveAsFilename( _
fileFilter:="Excel Files (*.xls), *.xls")
If Not newFname = "False" Then ThisWorkbook.SaveAs newFname
Else
ThisWorkbook.Save
End If

'Restore file to where user was
Call ShowAllSheets
aWs.Activate

'Restore screen updates
Application.ScreenUpdating = True
End Sub

Private Sub HideAllSheets()
'Hide all worksheets except the macro welcome page
Dim ws As Worksheet

Worksheets(WelcomePage).Visible = xlSheetVisible

For Each ws In ThisWorkbook.Worksheets
If Not ws.Name = WelcomePage Then ws.Visible = xlSheetVeryHidden
Next ws

Worksheets(WelcomePage).Activate
End Sub

Private Sub ShowAllSheets()
'Show all worksheets except the macro welcome page

Dim ws As Worksheet

For Each ws In ThisWorkbook.Worksheets
If Not ws.Name = WelcomePage Then ws.Visible = xlSheetVisible
Next ws

Worksheets(WelcomePage).Visible = xlSheetVeryHidden
End Sub

jiddings
03-06-2009, 08:12 AM
Anyone have any thought as to what in this code may be causing incompatibility with Excel 2007?
Any guidance would be greatly appreciated.

Bob Phillips
03-06-2009, 08:36 AM
Jack,

I have just tried it and I don't get that problem at all.

However, there is something else to think about with Excel 2007. The security implementation is slightly different in Excel 2007. Unless the VBIDE is open, you don't get an enable/disable macros dialog, instead a little Options button is put just above the formula bar. This means that in most cases your users will open to the Macros page, and they will have to press that button. That may be better for you, but you probably have to change the message.

jiddings
03-06-2009, 01:08 PM
xdl,
The specific compatibility issue that I encounter is: I have the file with the above code in the ThisWorkbook object of the one workbook and I have another workbook open also (can be a blank workbook). When I save / close the file with the macro code, Excel 2007 consistantly shutdowns and restarts.
This same file was satisfactorly running with Excel 2003. It is an XLS (excel 2003 file) running compatibilty mode in Excel 2007.
Any help would be appreciated!

tpoynton
03-07-2009, 01:39 PM
I am able to reproduce what the OP asserts, with a caveat perhaps: two files need to be open.

If I have two files open and close the force macro workbook without saving changes, excel crashes. If I say yes to save changes, all appears well.

I have no idea why, unfortunately, but perhaps this will help Bob or Simon replicate and figure it out. I'm curious.

after a little digging, it seems to have to do with this bit:

'If Cancel was clicked, turn events back on and cancel close,
'otherwise close the workbook without saving further changes
If Not Cancel = True Then
.Saved = True
Application.EnableEvents = True
.Close savechanges:=False
Else
Application.EnableEvents = True
End If

GTO
03-08-2009, 07:48 AM
Greetings Jack,

At home, so old laptop/XP/xl2000, but I believe was able to fairly well replicate. In my case, Excel didn't crash straight out, but after closing the 'force enable' (FE) wb, the other workbook would become non-responsive/hung and until taking the cursor off the sheet, the hourglass would stay.

Now if I kept trying to click on the remaining worksheet or use the arrow keys to get a cell active, it would crash Excel deader'n a doornail. However, I did notice that (after closing FE wb) if I set the focus to another application (like IE6) and then came back to Excel, the remaining wb would now respond. (El strango indeed)

As to:

...after a little digging, it seems to have to do with this bit:

'If Cancel was clicked, turn events back on and cancel close,
'otherwise close the workbook without saving further changes
If Not Cancel = True Then
.Saved = True
Application.EnableEvents = True
.Close savechanges:=False
Else
Application.EnableEvents = True
End If


...I threw a debug.print in and as expected, it recurses through BeforeClose, as EnableEvents is turned back on (as of course it must be). While stepping thru, no problem; and it would seem to me to be a harmless recurse, but it seems that at speed she doesn't like something.

If you are still having the issue, you could try the below. Same concept of an 'AfterSave' event, but we used a Boolean rather than EnableEvents. Thus - shouldn't recurse into BeforeClose as the bol can be left to die on its own.

I stripped this down from its original, but think it should be okay. As mentioned, I do not currently have access to '07, so definitely try in a junk wb first.

In ThisWorkbook Module:
Option Explicit

Private Sub Workbook_BeforeClose(Cancel As Boolean)
Dim intResponse As Integer

Application.DisplayAlerts = False

'// bolClosing will be false by default, so we will initially pass this test. //
If Not bolClosing Then

'// IF the workbook is saved, it will close without further ado. ELSE, we will //
'// see what the user wants to do and control the results. //
If Not ThisWorkbook.Saved Then

intResponse = MsgBox("Do you want to save the changes you made to '" & _
Left(ThisWorkbook.Name, Len(ThisWorkbook.Name) - 4) & "'?", _
vbExclamation + vbYesNoCancel + vbDefaultButton1, _
"My Custom Project")

Select Case intResponse
Case vbYes
'// bolClosing is used in BeforeSave //
bolClosing = True
'// See function. We don't need to execute the save here, and in //
'// fact, due to a weird glitch in Excel (least 2000), this is //
'// better. //
Call Workbook_BeforeSave(False, False)

'//This is required, as even though the file saved while in //
'// BeforeSave, changes occurred post save. //
ThisWorkbook.Saved = True
Case vbNo
bolClosing = True

'// User doesn't want to save changes, so just mark file saved. //
ThisWorkbook.Saved = True
Case vbCancel
'// User cancelled closing, and least in Excel 2000, I found it //
'// necessary to reactivate stuff if I wanted the focus returned. //
ThisWorkbook.Activate
bolClosing = False
Cancel = True
Application.DisplayAlerts = True
ActiveCell.Activate
Exit Sub
End Select
End If
End If

Application.DisplayAlerts = True
End Sub

Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
Dim intCalculationSetting As Integer, _
wksWorksheet As Worksheet, _
wksLastActive As Worksheet, _
strSaveAs_Filename As String

If Not (bolInProcess _
And Not Cancel) Then

'// Set bolProcess to True, which will later prevent a recurse to this IF. //
bolInProcess = True

'// Let's see what the user's choices were, as to a few settings that we wish //
'// to temporarily control. //
intCalculationSetting = Application.Calculation

'// We'll stop screen updates and uneccessary calculations, which will give a //
'// better appearance and increase run speed. //
Application.ScreenUpdating = False
Application.Calculation = xlCalculationManual
DoEvents

'// Find the ActiveSheet, so we can redisplay it later. //
Set wksLastActive = ThisWorkbook.ActiveSheet

'// Display the prompt (the warning) sheet BEFORE hidng remaining sheets. //
shtForceEnable.Visible = xlSheetVisible

For Each wksWorksheet In ThisWorkbook.Worksheets
If Not wksWorksheet.CodeName = "shtForceEnable" Then
wksWorksheet.Visible = xlSheetVeryHidden
End If
Next

'// Set Cancel to True! If the user executed a "normal" save (clicking Save //
'// button or Ctrl+s...), then only the user's called Save is cancelled. A //
'// couple of lines down, we'll execute a Save that will not be cancelled. //
'// //
'// If on the other hand, BeforeSave is called programatically, the Cancel does //
'// nothing, and the .Save (or SaveAs) executes under our control. //
'// I would also note that excluding the Cancel results in Saving the file //
'// twice. //
Cancel = True
DoEvents

'// Now, Save workbook. If you step-thru this, you'll see that it immedietely //
'// recurses to Workbook_BeforeSave, but as bolInProcess is currently True, //
'// there is no true recurse. Thus - the statements below the .Save execute, //
'// and the user sheets are redisplayed. //
If SaveAsUI Then
ChDir ThisWorkbook.Path
strSaveAs_Filename = Application.GetSaveAsFilename( _
InitialFileName:=ThisWorkbook.FullName, _
FileFilter:="Excel Files (*.xls), *.xls", _
Title:="Are you sure you want to SaveAs?" _
)
'// Check to see if user cancelled; in which case reset bolInProcess and //
'// allow sheets to be redisplayed. Nothing is saved, so no harm, no foul. //
If strSaveAs_Filename = "False" Then
bolInProcess = False
Else
'// If user chose a filename and committed to the saveas, I strip the //
'// proposed name from the proposed fullname, so that we can saveas to //
'// the same folder we opened this workbook in. //
strSaveAs_Filename = "\" & Right(strSaveAs_Filename, _
Len(strSaveAs_Filename) _
- InStrRev(strSaveAs_Filename, "\", -1, _
vbTextCompare))

'// One last check. If user selects same name as current, then cancels //
'// the app alert msg (are your sure? there's already a workbook with //
'// this name...), an error ensues. //
On Error Resume Next
ThisWorkbook.SaveAs ThisWorkbook.Path & strSaveAs_Filename
Err.Clear
On Error GoTo 0
End If
Else
ThisWorkbook.Save
End If

'// IF we were closing the workbook when we decided to save, we'll leave //
'// everything hidden, reset settings, turn screen updating back on, //
'// and let the workbook close. //
If bolClosing = True Then
Application.Calculation = intCalculationSetting
Application.ScreenUpdating = True
Exit Sub
End If

'// Redisplay whichever sheets you want the user to be able to see. //
For Each wksWorksheet In ThisWorkbook.Worksheets
wksWorksheet.Visible = xlSheetVisible
Next

'// THEN hide Prompt sheet AFTER redisplaying desired Worksheets. //
shtForceEnable.Visible = xlSheetVeryHidden

If Not ActiveSheet.Name = wksLastActive.Name Then
wksLastActive.Select
End If

'// You can also scroll to the last sheet you had active before the save. //
'// This does not effect which sheet is actually selected; it just puts which //
'// tabs you want in view. //
ActiveWindow.ScrollWorkbookTabs Position:=xlFirst
Application.Calculation = intCalculationSetting
Application.ScreenUpdating = True
ThisWorkbook.Saved = True

'// Reset bolInProcess for the next time that user saves file. //
bolInProcess = False
End If
End Sub

Private Sub Workbook_Open()
Dim wksWorksheet As Worksheet

'// Redisplay whichever sheets you want the user to be able to see. //
For Each wksWorksheet In ThisWorkbook.Worksheets
wksWorksheet.Visible = xlSheetVisible
Next

'// Then hide Prompt sheet AFTER redisplaying desired Worksheets. //
shtForceEnable.Visible = xlSheetVeryHidden

'// Mark workbook as saved, in case the user decides to close w/o making any //
'// changes. This way, if the user opens and closes the workbook w/o making any //
'// changes, he isn't uneccessarily asked about saving changes. //
ThisWorkbook.Saved = True
End Sub

Public Sub Workbook_CallSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
'// I have always noted a weird glitch, at least when //
'// run in OS:XP / Excel: 9.0 (2000). //
'// //
'// In short, the 'force enable' workaround functions correctly when the workbook //
'// is saved 'normally' by the user; that is - the user presses the save button or //
'// keys in the CTRL + s shortcut key combination. //
'// //
'// However, if the running program initiates the save (let's say it runs into a //
'// ThisWorkbook.Save), then if you follow (step-through) the code, you will see //
'// that rather than immedietely jumping back to line the beginning of //
'// 'Workbook_BeforeSave' as it should, it just continues and doesn't actually //
'// save. I have no explanation for this... //
Call Workbook_BeforeSave(False, False)
End Sub

In a Standard Module:
Option Explicit

Public _
bolInProcess As Boolean, _
bolClosing As Boolean

'// To call a Save, instead of ThisWorkbook.Save: //
Sub ExecSaveProgramatically()
Call ThisWorkbook.Workbook_CallSave(False, False)
End Sub

@XLD:

I have just tried it and I don't get that problem at all...

Greetings Sir, hope all is well in your neck of the woods. Say, you tested in 2007 (which I do not have access to). Did you get to try this with another wb already opened?

A good Sunday to all,

Mark

mdmackillop
03-08-2009, 08:21 AM
Hi Mark,
Compile Error
How should this be dimmed?
shtForceEnable

GTO
03-08-2009, 08:35 AM
Hi Mark,
Compile Error
How should this be dimmed?
shtForceEnable

Thanks for the rescue Malcom :-)

Jack, please note:

I spaced mentioning that I use the codename of the "macro warning" sheet rather than the sheet (tab) name.

This way if you don't have the workbook protected, it doesn't go KABOOM(!) the first time a user opens it with the macros disabled and changes the sheet name.

Mark