Handling Before Close events in Excel 2003

[Img]

Hi there
I'm going mad!

I have a working script to capture the user name and date of modification before close. The script is saved as a event headed : Private Sub Workbook_BeforeClose(Cancel As Boolean) in the VB script Editor under This WorkBook.
It works perfectly when you open a fresh instance of Excel.

After the workbook is closed and if I reopen the same workbook again (without quitting Excel), it does not execute the code unless I quit Excel and relaunch it again. So I can get round this with Application.Quit

However, I cannot ensure that users will always open this workbook in a fresh instance of Excel. They could be openning this workbook after working on other workbooks with some other Before Close routines in the same instance of Excel.

Is this a bug in Excel? Can anyone help to find a way round this?

Thank You

 

[Smitty]

Welcome to the Board!

Are you by chance disabling events (Application.EnableEvents = False) in your code and not turning them back on?

 

[Img]

Thank You Smitty

This is the code I used. Could you, please, please take a look if anything looks odd?? that can cause it?

Really appreciate this. I spent hours trouble shooting but found no answers.

Thank You again !

Private Sub Workbook_BeforeClose(Cancel As Boolean)
UserName = Environ("USERNAME")
 
Dim XCount As Integer
XCount = Application.Sheets.Count
Revised = Format(Now(), "dd mmmm yyyy")
 
Dim i As Integer
Dim Str As String
i = 1
Str = Sheets(i).Name
 
Dim Msg, Style, Title, Response
Msg = "Is this a revision from the previous version? If Yes, Last Revised Date will be changed to today's date." ' Define message.
Style = vbYesNo
Title = "Last Revision" ' Define title.
Response = MsgBox(Msg, Style, Title)
If Response = vbYes Then ' User chose Yes.
Sheets(Str).Range("C3").Value = Revised
Sheets(Str).Range("F3").Value = UserName
MsgBox (UserName & ", " & "The Last Revised Date is now changed to " & Revised)
ActiveWorkbook.Save
 
If XCount > 1 Then
 
For i = i To XCount
Str = Sheets(i).Name
Sheets(Str).Range("C3").Value = Revised
Sheets(Str).Range("F3").Value = UserName
Next i
 
 
ActiveWorkbook.Save
End If
Else
MsgBox ("GoodBye")
End If
Application.Quit
 
End Sub

 

[Colin Legg]

How is the workbook being closed? Is the user closing it via the Excel interface, or are you closing it programmatically via code? In other words, please can you describe what action is raising the Workbook_BeforeClose event handler?

Colin

 

[Img]

I am really grateful for folks like you people on this Board!

User is closing it via Excel Interface.

Thank You Colin.

 

[Smitty]

This seems to be working fine for me. Note that you should avoid using reserved words like UserName as variables.

<font face=Tahoma><SPAN style="color:#00007F">Private</SPAN> <SPAN style="color:#00007F">Sub</SPAN> Workbook_BeforeClose(Cancel <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Boolean</SPAN>)<br>    <SPAN style="color:#00007F">Dim</SPAN> XCount <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Long</SPAN>, i <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Long</SPAN><br>    <SPAN style="color:#00007F">Dim</SPAN> CurrentUser <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">String</SPAN>, Revised <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">String</SPAN>, Str <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">String</SPAN><br>    <SPAN style="color:#00007F">Dim</SPAN> Msg <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">String</SPAN>, Style <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">String</SPAN>, Title <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">String</SPAN>, Response <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">String</SPAN><br>    <br>        XCount = Application.Sheets.Count<br>        CurrentUser = Environ("USERNAME")<br>        Revised = Format(Now(), "dd mmmm yyyy")<br>        i = 1<br>        Str = Sheets(i).Name<br> <br>            Msg = "Is this a revision from the previous version? If Yes, Last Revised Date will be changed to today's date." <SPAN style="color:#007F00">' Define message.</SPAN><br>            Style = vbYesNo<br>            Title = "Last Revision" <SPAN style="color:#007F00">' Define title.</SPAN><br>            Response = MsgBox(Msg, Style, Title)<br><br>                <SPAN style="color:#00007F">If</SPAN> Response = vbYes <SPAN style="color:#00007F">Then</SPAN> <SPAN style="color:#007F00">' User chose Yes.</SPAN><br>                    <SPAN style="color:#00007F">With</SPAN> Sheets(Str)<br>                        .Range("C3").Value = Revised<br>                        .Range("F3").Value = CurrentUser<br>                    <SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">With</SPAN><br>                <SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">If</SPAN><br>                <br>                MsgBox (CurrentUser & ", " & "The Last Revised Date is now changed to " & Revised)<br>                <br>                ActiveWorkbook.Save<br> <br>                <SPAN style="color:#00007F">If</SPAN> XCount > 1 <SPAN style="color:#00007F">Then</SPAN><br>                    <SPAN style="color:#00007F">For</SPAN> i = i <SPAN style="color:#00007F">To</SPAN> XCount<br>                        Str = Sheets(i).Name<br>                            <SPAN style="color:#00007F">With</SPAN> Sheets(Str)<br>                                .Range("C3").Value = Revised<br>                                .Range("F3").Value = CurrentUser<br>                            <SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">With</SPAN><br>                    <SPAN style="color:#00007F">Next</SPAN> i<br>                    ActiveWorkbook.Save<br>                <SPAN style="color:#00007F">Else</SPAN><br>                    MsgBox ("GoodBye")<br>                <SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">If</SPAN><br><br>               Application.Quit<br> <br><SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Sub</SPAN></FONT>

 

[Img]

Smitty,
YOU ARE A STAR!!
It works and didn't give me the problems.
Still can't figure out what was causing mine to behave the way it did.

But Very grateful for your time and advice!
THANK YOU!

Have a fantastic day! (ps can we have your Californian sunshine here instead of this cold rainny and dull weather? )

 

[Smitty]

(ps can we have your Californian sunshine here instead of this cold rainny and dull weather? )

I suppose...It was 32 and sunny when I left home this morning. Supposed to get into the 60's today. But we really need the rain!