How to CHECK IF a Workbook is OPEN using VBA

To check if a workbook is open using a VBA code, you need to use FOR EACH loop that can loop in 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. So let’s write this code, but make sure to activate the developer tab to open the VBA editor.

Check IF a WORKBOOK is OPEN

  1. First, you need to declare variables to use in the code to create a loop.
    1-create-a-loop
  2. Use an input box to get the name of the workbook that you wish to search for.
    2-use-an-input-box
  3. Start the loop to loop through all the open workbooks.
    3-start-the-loop-to-loop
  4. 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.
    4-code-with-if-statement
  5. In the end, end the loop and use a message box to show a message box if nothing has been found.
    5-end-the-loop-and-use-amessage-box

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 Tutorials on Working with Workbooks in VBA

Quick Links: What is VBA | Understanding Excel Programing