Restrict BeforeClose Event to only run in the Workbook where it is stored

[MisterBill314]

I am relativley new to VBA and most of my code is fitting together various things I find on the internet. However, there is one thing I am having a problem with and haven't been able to find a solution. Maybe it something really simple that I keep overlooking.

I have a spreadsheet the runs a BeforeClose event. Everything works fine if I only I have the one workbook open. However, if multiple workbooks are open when I close Excel, the BeforeClose event runs for whatever workbook is active, not necessarily the workbook the BeforeClose event is stored in.

How can I restrict the BeforeClose event to only run in the workbook it is stored in? At some point, I may also have multiple workbooks with different BeforeClose events that would need run only in their own workbook.

Thanks

 

[GTO]

Hi there,

Could you post the code and say what module you have this in?

Private Sub Workbook_BeforeClose(Cancel As Boolean)

...should not be able to be called by another wb. Have you by chance, created a class and are using application level events?

 

[MisterBill314]

I maintain a list of usernames in the workbook who are allowed to Save the file, Range("Admins"). If the username is not in the list, they can only SaveAs. This works fine as long as only this file is open. The error occurs when:

1. Multiple files are open
2. A file other than this one is the ActiveWorkbook
3. I exit Excel (not just one workbook) with File->Exit or the X button

The actual error is that excel tries to find the Range("Admins") in the IsAdmin function for the ActiveWorkbook, which does not contain the VBA code.


This is the code the runs during the before close event.

Private Sub Workbook_BeforeClose(Cancel As Boolean)
 
'******************************************************************************************
'Checks if user is Admin and determine if save allowed
'******************************************************************************************
 
    Dim SaveAns As Integer
    Dim SaveIt As String
    SaveIt = "User " & Environ("USERNAME") & " is not an admin and cannot save this file." _
                & vbNewLine & "To close without saving, click OK.  Otherwise click Cancel, " _
                & " and then perform a Save As."
    If Not IsAdmin(Environ("USERNAME")) Then
        If Not IsTempAdmin(Environ("USERNAME")) Then
            SaveAns = MsgBox(SaveIt, vbOKCancel)
        End If
    End If
    If SaveAns = vbCancel Then
        Cancel = True
        Exit Sub
    ElseIf SaveAns = vbOK Then
        ActiveWorkbook.Saved = True 'Makes excel think that the WB was saved, so it won't ask
    End If
 
'******************************************************************************************
'Remove Custom Toolbar When Closing the File
'******************************************************************************************
Dim Symbol   As Object
Dim bar      As Object
On Error Resume Next
  For Each bar In Application.CommandBars
    If Not bar.BuiltIn Then
      If bar.Name = "FormatTimingSheet" Then
        Application.CommandBars("FormatTimingSheet").Delete
        Exit For
      End If
    End If
  Next
 
End Sub

The code for the IsAdmin Funtion is

Function IsAdmin(CurrentUser As String) As Boolean
'******************************************************************************************
'Determines if current user is part of the admin list
'******************************************************************************************
Dim cell As Object
    IsAdmin = False
    For Each cell In Range("Admins")
        If LCase(CurrentUser) = LCase(cell.Value) Then
            IsAdmin = True
            Exit Function
        End If
    Next cell
 
End Function

 

[GTO]

...The actual error is that excel tries to find the Range("Admins") in the IsAdmin function for the ActiveWorkbook, which does not contain the VBA code....

You lost me there a bit. Does the IsAdmin function reside in all workbooks open (thus ActiveWorkbook), or just ThisWorkbook?

Regardless, I believe you will find that the error is occurring due to the unqualified Range.

<font face=Courier New>    <SPAN style="color:#007F00">'// Unqualified fails if other wb active    //</SPAN><br>    <SPAN style="color:#00007F">For</SPAN> <SPAN style="color:#00007F">Each</SPAN> cell <SPAN style="color:#00007F">In</SPAN> Range("Admins")<br>    <br>    <SPAN style="color:#007F00">'// Using the worksheet's codename. //</SPAN><br>    <SPAN style="color:#00007F">For</SPAN> <SPAN style="color:#00007F">Each</SPAN> cell <SPAN style="color:#00007F">In</SPAN> shtSettings.Range("Admins")<br>    <br>    <SPAN style="color:#007F00">'// Using the worksheet's name (tab name) //</SPAN><br>    <SPAN style="color:#00007F">For</SPAN> <SPAN style="color:#00007F">Each</SPAN> cell <SPAN style="color:#00007F">In</SPAN> ThisWorkbook.Worksheets("My Sheet Name").Range("Admins")<br>    </FONT>

The first fails as you know. I would use the second one, as using the worksheet's CodeName will not fall down if the user changes the name on the sheet's tab.

I would also look at this:
<font face=Courier New>    ElseIf SaveAns = vbOK Then<br>        ActiveWorkbook.Saved = <SPAN style="color:#00007F">True</SPAN> <SPAN style="color:#007F00">'Makes excel think that the WB was saved, so it won't ask</SPAN><br>    <SPAN style="color:#00007F">End</SPAN> If<br>    <SPAN style="color:#007F00">'// ThisWorkbook is explicit    //</SPAN><br>    ElseIf SaveAns = vbOK Then<br>        ThisWorkbook.Saved = <SPAN style="color:#00007F">True</SPAN> <SPAN style="color:#007F00">'Makes excel think that the WB was saved, so it won't ask</SPAN><br>    <SPAN style="color:#00007F">End</SPAN> If</FONT>

Not tested, but it would at least seem possible that the current could mark the wrong wb as Saved. Even if not, I would suggest using ThisWorkbook wherever applicable; as it is explicit.

Finally, and only as a suggestion... Whenever I have used a wb specific temp toolbar, I find it better to use Workbook_Activate and Workbook_Deactivate for the creation/deletion of the commandbar. In short, this keeps the user from clicking a button that may make things go kaboom with the wrong wb having focus.

Hope that helps,

Mark

 

[MisterBill314]

Thanks. That took care of the problem.

 

[GTO]

You are most welcome and thanks for the feedback