Check Open Workbooks Before Closing

[ezrizer]

Hello,

I have a workbook ("WBK1") containing data that other workbooks source information from. My thought is to place a certain value ("XX") in cell "A1" of the associated workbooks and check all open workbooks for "XX" in cell "A1". If any of the open workbooks contains "XX" in "A1" then a userform will display, otherwise "WBK1" will just close.

Any code suggestions?

[jewelsharma]

try playing with this:

Please Login or Register  to view this content.

[HaHoBe]

Hi, jewelsharma,

from what I remember you have got to use the extension for the workbook as well not the name solely.

If WBK1 is the workbook with code the line could read

Please Login or Register  to view this content.

Ciao,
Holger

[jewelsharma]

Hi Guru Holger,
I seem to be misinformed. Somehow, I had an understanding that an already opened workbook could be referred to simply by its extention-less name. Nevertheless, per the details provided by OP, it seems the code would rest in the named workbook; and

Please Login or Register  to view this content.

would be perfect here.
Many thanks for your review. Cheers!

[ezrizer]

Thanks for the replies however I'm having problems with

If .Worksheets("Sheet1").Range("A1") = "XX" Then

The debugger runs and highlights this line. I changed "Sheet1" to the subject sheet however no luck.

If I remove the "." before ".Worksheets" and change "Sheet1" to the name of a sheet in the workbook I want to close it works however that isn't the workbook I want to search. If I change "Sheet1" to the name of a sheet in another workbook the debugger runs.

Anymore ideas?

[HaHoBe]

Hi, ezrizer,

Please Login or Register  to view this content.

Ciao,
Holger

[ezrizer]

Holger,

Thanks for the reply. Below is what I was able to make work

[code]
Dim Wb As Workbook
Dim Ws As Worksheet
Dim blnCheck As Boolean
For Each Wb In Application.Workbooks
For Each Ws In Wb.Worksheets
If Ws.Range("A1") = "XServiceToolsX" Then
blnCheck = True
End If
Next Ws
Next Wb
If blnCheck = True Then
CloseProject.Show
Else
ThisWorkbook.Close True
End If
[code]