Locked out of one spreadsheet while another sheets code is running

[GiraffetheGeek]

Hi guys and gals,

I work for a large organisation and as I am the only "geek" in my sub-organisation and regional area some of the more "geek" orientated tasks get assigned to me.

I am currently designing a sort of "menu" system using a shared excel worksheet that has buttons it it that will open other un-shared workbooks at act as an appointment schedule.

What I am trying to acheive is a shared workbook that everyone can have open and when they click one of the buttons it opens the other workbook. At this point I want the code on the first workbook to halt or wait, until the user has finished booking an appointment in the second workbook and then once it detects or sees that the second workbook is closed then continue running the remaining code.

I am not a systems enginner or administrator, but I can explain the structure of the network as best I can.

There are a number of different shared drives for each office that can be accessed by any office across the country. Users "My Documents" follow them wherever they log in so I assume this data is stored centrally somewhere.

I know how to reference each shared drives location. In the code below I have it access information on my users data and once I have it working I will change it to a shared drive location.

In the \\corp.xxx.yyyy.nz the xxx and yyyy are actually some other values but I need to keep the organisation anonymous in this forum.

My code is this:

Public OpenIndicator As Integer
Dim wBook As Workbook
 
Sub OpenAppointmentsOne()
    Btn_ScheduleOne.Visible = False
    Workbooks.Open FileName:="\\corp.xxx.yyyy.nz\usersg\gcarr009\Documents\IT\Site\Appt.xls"
5   Application.Wait Now + TimeValue("00:00:30")
    Call TestIfOpen
    If OpenIndicator = 1 Then
        GoTo 5
    End If
    Btn_ScheduleOne.Visible = True
    ActiveWorkbook.Save
End Sub
 
Sub TestIfOpen()
Dim wb As Workbook
For Each wb In Workbooks
    If wb.Name = "Appt.xls" Then
        OpenIndicator = 1
        Exit Sub
    End If
Next
OpenIndicator = 0
End Sub

Most of this works fine EXCEPT that in the 30 seconds wait while the code is waiting to check the status of the second sheet again the user cannot manipulate (add data, change data, save or close workbook) any of the other worksheets so it just endlessly loops until I ctrl+break it.

Anyone know how I can get around this issue - or code differently to reach my particular goal?