Solved: Excel crashes when Force Macros VBA is used

[slarti]

Hi,

I'm relatively new to VBA, and have been learning via various forums over the last couple of months. I found Ken Puls' excellent "Force users to enable macros in a workbook" macro at vbaexpress.com/kb/getarticle.php?kb_id=379 ( you'll have to add the www as I can't yet post links). This is likely to be very useful in a workbook I'm creating, as it depends heavily on other macros to protect itself from corruption by users. However, I have found a small bug, and would like to know if other's suffer the same thing?

The bug can be replicated as follows:

1. Open new Excel workbook & type a small text entry
2. Open Ken's Force Macros workbook, and type a small text entry on Sheet2 (Sheet1 is protected)
3. Save Force Macros workbook and close it
4. Now first workbook has locked up, and will generally crash if attempts are made to select another sheet.

The crash throws up the standard "Excel has encountered a problem" window, with a Debug option. When this is pressed, Microsoft Visual C++ pops up in break mode, always highlighting the same line "302179A7 mov eax,dword ptr [eax+2B0h]". I know nothing of Microsoft Visual C++, so can anyone help?

Thanks - Slarti

[Bob Phillips]

Bloody hell, that is not good is it. Must be the way Ken is messing with events.

This may be overkill, something simpler might suffice

Option Explicit

Const Welcome_sheet As String = "Welcome"

Private Sub Workbook_BeforeClose(Cancel As Boolean)
Dim sh As Worksheet
Worksheets(Welcome_sheet).Visible = xlSheetVisible
    For Each sh In ThisWorkbook.Worksheets
        If sh.Name <> Welcome_sheet Then
            sh.Visible = xlSheetVeryHidden
        End If
    Next sh
End Sub

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

This is workbook event code.
To input this code, right click on the Excel icon on the worksheet
(or next to the File menu if you maximise your workbooks),
select View Code from the menu, and paste the code

[unmarkedhelicopter]

Was there a Bartfarce abouts here somewhere ?

[slarti]

Hi xld,

Thanks for your quick response. I've just had a quick test of your code, and it seems much more robust. However when you close the workbook, Excel always asks if you want to save changes, despite no obvious changes being made. This may confuse the users of my workbook, and I guess that's the reason that Ken's code messes with events. I don't suppose there's a work around for this problem?

Thanks - Slarti

[slarti]

Hi xld,

It looks like my last post was a little premature. I've just done some more testing with your code, and found that it won't show the welcome page when disable macros is selected, if another workbook is already open.

Sorry to be the bearer of bad tidings

Slarti

[unmarkedhelicopter]

Option Explicit 
Const Welcome_sheet As String = "Welcome"
 
Private Sub Workbook_BeforeClose(Cancel As Boolean) 
    Dim sh As Worksheet, boS As boolean
    boS = ThisWorkbook.Saved
    Worksheets(Welcome_sheet).Visible = xlSheetVisible 
    For Each sh In ThisWorkbook.Worksheets 
        If sh.Name <> Welcome_sheet Then 
            sh.Visible = xlSheetVeryHidden 
        End If 
    Next sh
    ThisWorkbook.Saved = boS
End Sub 
 
Private Sub Workbook_Open() 
    Dim sh As Worksheet
    For Each sh In ThisWorkbook.Worksheets 
        sh.Visible = xlSheetVisible 
    Next sh 
    Worksheets(Welcome_sheet).Visible = xlSheetVeryHidden
    ThisWorkbook.Saved = True
End Sub

[unmarkedhelicopter]

For this "force user to enable macros" Ideally you need to save the book with just the welcome sheet visible (EVERY TIME) not just when you close the book because if I open your book, do some changes, save it (manually) then save as (ANOther filename) then when I open the first book again everything is visible ))
So ANY saved book defaults to "Welcome ..."

[Bob Phillips]

The close issue is easily resolved

Option Explicit
Const Welcome_sheet As String = "Welcome"
 
Private Sub Workbook_BeforeClose(Cancel As Boolean)
    Dim sh As Worksheet
Worksheets(Welcome_sheet).Visible = xlSheetVisible
    For Each sh In ThisWorkbook.Worksheets
        If sh.Name <> Welcome_sheet Then
            sh.Visible = xlSheetVeryHidden
        End If
    Next sh
    ThisWorkbook.Save
End Sub
 
Private Sub Workbook_Open()
    Dim sh As Worksheet
For Each sh In ThisWorkbook.Worksheets
        sh.Visible = xlSheetVisible
    Next sh
Worksheets(Welcome_sheet).Visible = xlSheetVeryHidden
End Sub

I do not see the other problem. Ensure that you initialise it first by saving it with macros enabled.

[unmarkedhelicopter]

This just forces a save at the end regardless of whether you have made a change or not and could be dangerous if the user feels he has 'cocked up' and thus just wants to close the book without saving so it can be re-opened in pristine condition.

[Reafidy]

Hi All,

Leave Ken's workbook as is but change the close event to:

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
Application.EnableEvents = True
.Saved = True
If Not Workbooks.Count > 1 Then Application.Quit
Else
Application.EnableEvents = True
End If
End With
End Sub

This should prevent the look up.
You have an option to get rid of the flaw that ken mentioned in the thread about the workbook not closing excel as well (but it will close the application no matter which x is clicked so its up to you which flaw you'd rather have).
If you would rather have the flaw Ken mentioned then remove:

If Not Workbooks.Count > 1 Then Application.Quit

The problem with the lookup was events were being turned on and a close command given was causing a unwanted cycle.

[mperrah]

Is it hi-jacking a post to say thank you?
This topic is something I have been needing for a while...
Thanks for this post and this forum, very much.
Mark

[slarti]

Sorry it's taken so long for me to do this, but thank you very much to all the replies to my post. In particular, thanks to Reafidy, who I think (in my limited experience) has come up with the best solution.

No doubt you'll hear from me again whenever the next problem crops up!

Slarti

[tcsic]

Hi, new to forum but I find it an excellent resource. Everyone is so helpful.

MS Excel 2003 on Win7 and will be using this workbook on server with Excel 2003.

I have also used Kens Force to enable macros and it seems to work well, although I have not yet tested it with other workbooks open.

My previous version, before forcing users to enable macros, included code to force a save before close.

Private Sub Workbook_BeforeClose(Cancel As Boolean)
If Me.Saved = False Then Me.Save
End Sub

When I combine this code with Kens, I receive the "Compile Error"
"Ambiguous Name Detected: "Workbook Before_Close"

This thread seems to provide several options for combining the force macro scripts with workbook saving options, but I haven't been able to find a combination that will force both the macro enable and the save before close event.

Thank you for you help and patience.
Tim