Create an action trigger (Open or Close a file) for a Visual Basic Module

msofficeshare logo

Requirement:  Excel Visual Basic Knowledge

File to download:  None

Related articles: Press ENTER key to trigger a Visual Basic Module

Excel Visual Basic modules can be triggered by an action like opening and closing a file.  It's handy when you are afraid that the users may forget to do a certain step before closing a file, or you just simply want certain repetitive tasks to be automated when a file is closed.  

 

Below is a simple Visual Basic module to be triggered when a file is opened:

The above VB module will initiate a greeting to the username when the file is opened.  The "Environ" command will get the username from the computer that a user is using.

 

Below VB module will do something more complicated when the file is closed. 

Please note that the above VB module must be placed in the workbook (ThisWorkbook), not the "Module" or any worksheet (Sheet1, Sheet2, ... etc.) otherwise it will not work.

Private Sub Workbook_Open()
  MsgBox "Welcome back, " & Environ("Username") & "!"
End Sub

Private Sub Workbook_BeforeClose(Cancel As Boolean)
    'Define Name Range when the Excel File is closed
    Dim EndRow As Long
    
    Sheets("Data").Select
    Range("A1").Select
    EndRow = ActiveCell.End(xlDown).Row
   
    ActiveWorkbook.Names("ExportRange").RefersToR1C1 = "=Data!R1C1:R" & EndRow & "C2"
    ActiveWorkbook.Save
End Sub

 I have already defined the data range called "ExportRange" in the "Data" worksheet of my file.  However, I append the data on a regular basis, so the data range will be changing every time I update my file.  The column does not change but the row number will be changing.

 

The above VB module will change the data range every time I close my Excel file.  What it does is to find the "Data" worksheet within the file, first go to cell A1, and then go to the bottom of the data range and find the row number of it and put the row number figure in the "EndRow" variable.  I can re-define the name range with the "EndRow" variable, then save the file before closing.

pic1 close trigger
close trigger logo