Check if multiple workbooks are open

[Ignition1]

I have a spreadsheet with a block of VBA code which updates the data by pulling information for other workbooks.

At first I used to make the code open the workbooks (using the file locations - C:\Windows etc etc) - but then I realised the file location won't be the same for everyone who uses this. The only alternative I could think of is if they have the workbooks all open before they run the updating macro.

So to check if the workbooks are all open I tried this -

Dim Prospectswb, Workloadwb, TGCwb As Workbook
On Error Resume Next

Set Prospectswb = Workbooks("Prospects.xls")
Set Workloadwb = Workbooks("Workload.xls")
Set TGCwb = Workbooks("TGC.xls")

If Prospectswb Or Workloadwb Or TGCwb Is Nothing Then

MsgBox ("Please make sure all workbooks are open and that they are named 'Prospects', 'Workload' and 'TGC' only")

Else: Call UPDATE

End Sub

UPDATE is a sub-procedure which I'd like it to run If all the workbooks above are open. If they are not open then I'd like the message box above to appear.

Any help would be much appreciated!

 

[VoG]

Perhaps

If Prospectswb Is Nothing Or Workloadwb Is Nothing Or TGCwb Is Nothing Then

 

[Ignition1]

Ah - perhaps should have explained.

I get a "Expected procedure, not variable" error with the above code - highlighting the Else: Call UPDATE

 

[VoG]

It should also be

Dim Prospectswb As Workbook, Workloadwb As Workbook, TGCwb As Workbook

Perhaps paste your UPDATE code.

 

[Ignition1]

I just pasted the code instead.

 

[VoG]

You are also missing an End If

Dim Prospectswb As Workbook, Workloadwb As Workbook, TGCwb As Workbook

Set Prospectswb = Workbooks("Prospects.xls")
Set Workloadwb = Workbooks("Workload.xls")
Set TGCwb = Workbooks("TGC.xls")

If Prospectswb Is Nothing Or Workloadwb Is Nothing Or TGCwb Is Nothing Then

    MsgBox ("Please make sure all workbooks are open and that they are named 'Prospects', 'Workload' and 'TGC' only")

Else
    Call Update
End If
End Sub