Need a test to see if a workbook is already open

[Wild Bill]

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:

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

 

[Smitty]

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