There are a few quick ways to check if a workbook is open when running a macro.
One method is by using error handling:
Dim bCustom As Workbook On Error Resume Next Set bCustom = Workbooks("My Workbook 1.xlsx") If bCustom Is Nothing Then MsgBox "Could not find the workbook you are looking for.", vbCritical Exit Sub End If On Error Goto 0
This sets the workbook to the variable; if the variable is still set to nothing, then the workbook is not open.
Under normal circumstances, with no error handling, this code would stop running if the workbook is not open; the error handling means the error is suppressed and then an additional check is added to ensure that something was found.
However, a better method is to check every open workbook specifically – without making error handling necessary to the routine.
Dim wb As Workbook Const MyWBName As String = "My Workbook 1.xlsx" For Each wb In Workbooks If wb.Name = MyWBName Then Set bCustom = wb Exit For End If Next If wb Is Nothing Then MsgBox "Could not find an open copy of " & MyWBName & "." & vbNewLine & vbNewLine & "Please open the file and try again.", vbCritical Exit Sub End If
It checks every workbook open in this instance of Excel (for up to versions 2010, for 2013, all open workbooks) and ensures the name matches that which we are expecting. Once it has found the workbook, it sets it to the variable and then jumps out of the loop.
Next, it checks the variable is set, if not then it provides a user prompt and ends the process.