PDA

View Full Version : Solved: Excel crashes when Force Macros VBA is used



slarti
11-14-2007, 09:53 AM
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
11-14-2007, 11:41 AM
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
11-14-2007, 07:34 PM
Was there a Bartfarce abouts here somewhere ?

slarti
11-15-2007, 02:04 AM
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
11-15-2007, 02:28 AM
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
11-15-2007, 02:28 AM
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
11-15-2007, 02:33 AM
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
11-15-2007, 02:44 AM
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
11-15-2007, 02:53 AM
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
11-15-2007, 01:58 PM
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
12-03-2007, 08:45 PM
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
01-10-2008, 05:05 AM
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
12-22-2010, 11:14 AM
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