Excel VBA - ThisWorkbook Introduction (Code Included)
**Check out the BLOG
https://chrisjterrell.com/blog/223147...
**Grab the Free VBA Quick Reference Guide
https://www.chrisjterrell.com/excel-v...
In this video, we look at how ThisWorkbook works in VBA. Although this is similar to the ActiveWorkbook, This workbook is always the workbook that is currently running code. You can write a macro that opens another workbook, and when the workbook opens, the ActiveWorkbook is the workbook that was opened, and ThisWorkbook is the original workbook with the running macro. Additionally, if you have an Addin, it will never be the ActiveWorkbook, and you will always need to reference ThisWorkbook.
I this video, we have code that shows the name (ThisWorkbook.name), the FileFormat, the Path, and the FullName (the Path plus the name of the workbook)
Finally, we go into the Thisworkbook code window to write three event procedures that trigger when a workbook opens, before it closes, and before it saves. These are simple message boxes, and hopefully, you will see the potential of how these can be used powerfully for your code.
'====================
'====Module Code======
'====================
Sub ThisWorkbookIntro()
'Properties
MsgBox ThisWorkbook.Name
MsgBox ThisWorkbook.FileFormat
MsgBox ThisWorkbook.Path
MsgBox ThisWorkbook.FullName
'Methods
ThisWorkbook.Save
'ThisWorkbook.SaveAs ThisWorkbook.Path & "/Save As file", 52
End Sub
'====================
'==Thisworkbook Code==
'====================
Private Sub Workbook_BeforeClose(Cancel As Boolean)
MsgBox "Run Before Close"
End Sub
Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
MsgBox "Run Before Save"
End Sub
Private Sub Workbook_Open()
MsgBox "Run on Open"
End Sub
