Need a test to see if a workbook is already open

Wild Bill

Board Regular
Joined
Feb 20, 2006
Messages
125
I have a workbook_open macro that opens another workbook. It works great except when the second workbook is already open. I'm looking for a way to test for the other workbook being already open and if it is to prevent an attempt to open it again. Here's the existing macro:

Code:
Private Sub Workbook_Open()

Dim wbk As String

    wbk = ActiveWorkbook.Name
    ActiveWindow.WindowState = xlNormal

    Workbooks.Open Filename:= _
        "\\ST Budget May-06\Reference Data\STaR_Droplists.xls", ReadOnly:=True
    Workbooks("STaR_Droplists.xls").Activate
    ActiveWindow.Visible = False
    
    Workbooks(wbk).Activate
    ActiveWindow.Visible = True
    ActiveWindow.WindowState = xlNormal

End Sub
 

Some videos you may like

Excel Facts

Excel Can Read to You
Customize Quick Access Toolbar. From All Commands, add Speak Cells or Speak Cells on Enter to QAT. Select cells. Press Speak Cells.

Smitty

Legend
Joined
May 15, 2003
Messages
29,536
Here's some code to test if a wb is open:

<font face=Tahoma><SPAN style="color:#00007F">Sub</SPAN> IsWorkBookOpen()
<SPAN style="color:#007F00">''''''''''''''''''''''''''''''''''''''''''</SPAN>
<SPAN style="color:#007F00">'Written by www.ozgrid.com</SPAN>

<SPAN style="color:#007F00">'Test to see if a Workbook is open.</SPAN>
<SPAN style="color:#007F00">''''''''''''''''''''''''''''''''''''''''''</SPAN>

<SPAN style="color:#00007F">Dim</SPAN> wBook <SPAN style="color:#00007F">As</SPAN> Workbook

    <SPAN style="color:#00007F">On</SPAN> <SPAN style="color:#00007F">Error</SPAN> <SPAN style="color:#00007F">Resume</SPAN> <SPAN style="color:#00007F">Next</SPAN>
    <SPAN style="color:#00007F">Set</SPAN> wBook = Workbooks("Personal.xls")

        <SPAN style="color:#00007F">If</SPAN> wBook <SPAN style="color:#00007F">Is</SPAN> <SPAN style="color:#00007F">Nothing</SPAN> <SPAN style="color:#00007F">Then</SPAN> <SPAN style="color:#007F00">'Not open</SPAN>
            MsgBox "Workbook is not open", _
            vbCritical, "OzGrid.com"
            <SPAN style="color:#00007F">Set</SPAN> wBook = <SPAN style="color:#00007F">Nothing</SPAN>
            <SPAN style="color:#00007F">On</SPAN> <SPAN style="color:#00007F">Error</SPAN> <SPAN style="color:#00007F">GoTo</SPAN> 0
        <SPAN style="color:#00007F">Else</SPAN> <SPAN style="color:#007F00">'It is open</SPAN>
            MsgBox "Yes it is open", _
            vbInformation, "OzGrid.com"
            <SPAN style="color:#00007F">Set</SPAN> wBook = <SPAN style="color:#00007F">Nothing</SPAN>
            <SPAN style="color:#00007F">On</SPAN> <SPAN style="color:#00007F">Error</SPAN> <SPAN style="color:#00007F">GoTo</SPAN> 0
        <SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">If</SPAN>
<SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Sub</SPAN>

</FONT>

HTH,

Smitty
 

Watch MrExcel Video

Forum statistics

Threads
1,111,596
Messages
5,541,154
Members
410,543
Latest member
ExcelGlenn
Top