Automatic Sub

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

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s