need help displaying Message Box if worksheet is not open

[SAThomas]

Hello,

I am trying to write a macro that looks for a worksheet to be open and if not then to display a message box telling the user to open it. The data file is generated by another system and has a different suffix evey time it is run and may not be saved on the users machine.

I have found some code that looks for the workbook using a partial file name. It works fine in finding the file, but if the file is not open nothing happens. I can't figure out the correct logic statement in combination with the loop.

If the file is not found i would like to provide the message box and terminate the macro.

Sub test1()
    Dim winAPP As Window
     
   For Each winAPP In Application.Windows
        If winAPP.Caption Like "Test*.xls" Then
            winAPP.Activate
            Exit For
        
    End If
    Next
    
    
   
    'MsgBox "The worksheet Test must be open"
    
    ' on error end macro
End Sub

[xLJer]

This may get you closer to what you are looking for.

Sub test1()
Dim winAPP As Window
Dim SheetOK As Boolean
     
   For Each winAPP In Application.Windows
        If winAPP.Caption Like "Test*.xls" Then
            winAPP.Activate
            SheetOK = True
            Exit For
        End If
    Next
    
    If Not SheetOK Then MsgBox "The worksheet Test must be open in this Excel session"
    
End Sub

[SAThomas]

xLJer

- absolutey perfect -

Thanks for the quick response and excellent solution!