Certain procedures, having specific names relating to actions in the Excel tool, allows you to be launch automatically.
You just have to complete the VBA macro code inside this specific procedure and you task will be execute.
Sub automatically launched when opening the workbook
The procedure named Auto_open is automatically triggered when the workbook with this code is opened. It allows you to automatically launch VBA code at the opening.
Sub Auto_Open() 'This name of Sub run automaticaly the macro 'at the opening of the workbook MsgBox "Bitcoin price is " End Sub
You can also use the name Workbook_Open for this procedure :
Sub Workbook_Open() 'This name of Sub run automaticaly the macro 'at the opening of the workbook MsgBox "Game of thrones" End Sub
Sub automatically launched when closing the workbook
The procedure named Workbook_BeforeClose is triggered automatically just before closing the workbook with the following code. It allows you to automatically launch VBA code at the closing.
In the example below, the code checks the value of cell A1. If this is equal to “Wait” then the closing of the workbook is canceled, otherwise the workbook closes while saving.
Private Sub Workbook_BeforeClose(Cancel As Boolean) 'This name of Sub run automatically the macro 'at the closing of the workbook If Range("A1") = "Wait" Then MsgBox "The game is not over." 'Cancel the closing of the workbook if "Wait" in A1 Cancel = True Else 'If different of "Wait" in A1 save and close the wbk ThisWorkbook.Save End If End Sub
Sub launched at a specific time
The procedure below allows you to automatically launch a macro at a specific time. It only works if the worbook with the macro is already open.
In the example below, the SpecificTime procedure will launch at 10 p.m, and will call the CloseWbk procedure which will close the workbook.
Sub SpecificTime() 'At the specific hour the Sub CloseWbk is called h = "22:22:00" Application.OnTime EarliestTime:=TimeValue(h), Procedure:="CloseWbk" End Sub Sub CloseWbk() 'Close and save the current workbook ThisWorkbook.Close savechanges = True End Sub