How do I close an excel file in the Workbook_Open event when I have multiple files open?

Question

In the Workbook_Open event I am running code to check the serial number to ensure it is matching data I am pulling from another data source. Then I am trying to close the file if it does not match.

When only one file is open the file will close as I would like it to. But when multiple files are open the file doesn't close.

Here is the code I am using. I only run into an issue that the file doesn't close if there are other files open.

Private Sub Workbook_Open()

Dim wb As Workbook: Set wb = ThisWorkbook

Application.ScreenUpdating = False

'Pull User_Name
    Range("U_Nm").Value = Application.UserName

'Pull Serial Number from Computer
    Dim fsObj   As Object
    Dim drv     As Object
    Set fsObj = CreateObject("Scripting.FileSystemObject")
    Set drv = fsObj.Drives("C")

    Range("SN").Value = Left(Hex(drv.SerialNumber), 4) _
         & "-" & Right(Hex(drv.SerialNumber), 4)

'Sheets("Security").Visible = xlSheetVeryHidden
Sheets("Security").Visible = True


If Range("Full_Validation").Value <> "Valid" Then

    'you must be connected to the internet to proceed

    Sheets("Security").Range("A1").ListObject.QueryTable.Refresh BackgroundQuery:=False

    If Range("User_Validation").Value <> "Valid" Then

        MsgBox "Not a Valid User!"


        '*******THIS LINE OF CODE IS NOT WORK WHEN I HAVE A SECOND FILE OPEN AT THE SAME TIME********
        wb.Close SaveChanges:=False

    ElseIf Range("Expiry_Validation").Value <> "Valid" Then

        Set objShell = CreateObject("Wscript.Shell")

        intMessage = MsgBox("Your Investment Calculator Subscription has Expired!" & vbCr _
            & vbCr _
            & "You will need to renew your subscription to the Investment Calculator." & vbCr _
            & vbCr _
            & "Would you like to renew your subscription?", _
            vbYesNo, "There is a problem...")

        If intMessage = vbYes Then
            objShell.Run ("https://google.com")
        Else
            'Wscript.Quit
        End If

        ThisWorkbook.Close SaveChanges:=False

    ElseIf Range("SN_Validation").Value <> "Valid" Then

        MsgBox "You must first register your computer with Doug & Mark!"
        ThisWorkbook.Close SaveChanges:=False

    End If

    'Sheets("Security").VeryHidden = True
    Application.ScreenUpdating = True

End If

Range("U_Nm").ClearContents
Range("SN").ClearContents

End Sub

I would expect the file to close whether there is 1 or many files open at the time.

ThisWorkbook.Close SaveChanges:=False should work, no matter how many files are open. Something else is wrong. Could be that some other file have a Macro that have set Application.EnableEvents = False . Does it give you an error, or nothing happens ? — Mikku
You say _ I am running code to check the serial number to ensure it is matching data I am pulling from another data source_. It is likely that code is the source of your problem (as Mikku said, the Thisworkbook.Close code looks ok) — chris neilsen

Pᴇʜ Pᴇʜ · Accepted Answer · 2019-08-02T06:20:25

In general you first need to understand the difference between ThisWorkbook and ActiveWorkbook.

Using ThisWorkbook is a good practice because it refers to a unambiguous workbook (exactly the workbook the code is written in). While using ThisWorkbook is a very safe way, using ActiveWorkbook is (in most cases)^*(1) a bad practice because it refers to the workbook that has focus (is on top) while the code is running. This can be any workbook the user clicked on (to bring it to top) and therefore is not very reliable, you can actually never rally say which one it exactly is. The only fact you know is, it is the workbook which is on top and everyone can easily change that workbook.

^{(1) Actually there is one case where ActiveWorkbook becomes handy and this is when coding an add-in for Excel (or something with a similar function).}

So using ThisWorkbook.Close SaveChanges:=False will always close the workbook that is running this code.

If you have handle multiple workbooks it is a good practice to open them with VBA and refer a variable to them, which you can use later to directly access the distinct workbook (eg. without hard coding its file name).

For example:

Dim SourceWorkbook As Workbook
Set SourceWorkbook = Workbooks.Open(Filename:="C:\My\Path\source.xlsx")

Now you can use SourceWorkbook in your procedure to access the workbook and its worksheets like

SourceWorkbook.Worksheets("SheetName").Range("A1").Value

and if you need to close it you can easily do it using:

SourceWorkbook.Close SaveChanges:=False

How do I close an excel file in the Workbook_Open event when I have multiple files open?

2 Answers