To check if a workbook is open using a VBA code, you need to use FOR EACH loop that can loop through all the workbooks that are open at the moment and verify each workbook’s name with the name you have mentioned. You can use a message box to get the result of the loop. Or you can also make the code to enter the result in a cell.
Check IF a WORKBOOK is OPEN
- First, you need to declare variables to use in the code to create a loop.
- Use an input box to get the name of the workbook that you wish to search for.
- Start the loop to loop through all the open workbooks.
- Write code with IF STATEMENT to verify the name of the workbook with the name you have entered in the input box, and once the name matches, activates the workbook, show a message box that workbook is found, and exit the procedure.
- In the end, end the loop and use a message box to show a message box if nothing has been found.
Helpful Links: Run a Macro – Macro Recorder – Visual Basic Editor – Personal Macro Workbook
Here’s the full code.
Sub vba_check_workbook() Dim WB As Workbook Dim myWB As String myWB = InputBox(Prompt:="Enter the workbook name.") For Each WB In Workbooks If WB.Name = myWB Then WB.Activate MsgBox "Workbook Found!" Exit Sub End If Next WB MsgBox "Not Found" End Sub
More on VBA Workbooks
VBA Save Workbook | VBA Close Workbook | VBA Delete Workbook | VBA ThisWorkbook | VBA Rename Workbook | VBA Activate Workbook | VBA Combine Workbook | VBA Protect Workbook (Unprotect) | VBA Open Workbook | VBA Check IF an Excel Workbook Exists in a Folder| VBA Create New Workbook (Excel File)