Create an action trigger (Open or Close a file) for a Visual Basic Module
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.
