Microsoft Excel - Message - Asked By Dan on 02-Aug-11 11:19 AM

Is it possibel to have a message display that another required workbook is not open?
Pichart Y. replied to Dan on 02-Aug-11 11:41 AM
Hi Dan,

Yes, it is possible, 

on error goto MsgAlert
workbooks("Workbookname").activate


MsgAlert:
msgbox("You did not open the file")


end sub
Riley K replied to Dan on 02-Aug-11 11:53 AM
Hi Dan,

 The function below can be used to determine if a workbook is open or not:

Function WorkbookOpen(WorkBookName As String) As Boolean
' returns TRUE if the workbook is open
  WorkbookOpen = False
  On Error GoTo WorkBookNotOpen
  If Len(Application.WorkBooks(WorkBookName).Name) > 0 Then
    WorkbookOpen = True
    Exit Function
  End If

WorkBookNotOpen:

End Function
Example:

If Not WorkbookOpen("MyWorkbookName.xls") Then
  Workbooks.Open "MyWorkbookName.xls"
End If

Regards
Dan replied to Riley K on 02-Aug-11 12:09 PM
Now of theses work.  I get am getting errors.
John D replied to Dan on 02-Aug-11 12:48 PM
Why not just have an event macro that opens the other workbook, something like this:
Private Sub Workbook_Open()
Workbooks.Open Filename:= _
      "C:\Users\Your name\Desktop\Documents\Excel files\This book.xls"
End Sub
Obviously you'll need the right path.
HTH
John
John D replied to John D on 02-Aug-11 02:46 PM
You're not replying so maybe you still want that message.
I tried it and it works...
Private Sub Workbook_Open()
On Error GoTo NoFile
Workbooks.Open Filename:= _
        "C:\Users\Your name\Desktop\Documents\Excel files\This book.xls"
If WorkbookOpen = True Then
Exit Sub

NoFile: MsgBox ("Your file is not open")
End If
 End Sub
John
John D replied to Dan on 02-Aug-11 03:01 PM
Hi Dan
You're not replying so maybe you still want that message.
I tried it and it works...
Private Sub Workbook_Open()
On Error GoTo NoFile
Workbooks.Open Filename:= _
        "C:\Users\Your name\Desktop\Documents\Excel files\This book.xls"
If WorkbookOpen = True Then
Exit Sub
NoFile: MsgBox ("Your file is not open")
End If
 End Sub

HTH
John