I've put a button on a spreadsheet that opens another workbook, works
fine if the other workbook isn't already open.

What I need to do, is to modify this so that it checks to see if this
workbook is already open, and if it is pop-up a message box saying the
workbook is already open, otherwise it can just go ahead and open the
closed workbook...

TIA for any suggestions.

The box still comes up saying the workbook is already open...

With this code??? -- Not for me.

Sub Tester11()
Dim wkbk As Workbook
On Error Resume Next
Set wkbk = Workbooks("BigData1.xls")
On Error GoTo 0
If wkbk Is Nothing Then
Set wkbk = Workbooks.Open("C:\Data\BigData1.xls")
End If
wkbk.Activate

End Sub

I can't believe that - it doesn't try to open the workbook if it is already
open.

Sub Tester11()
Dim wkbk As Workbook
Dim sName as String, sPath
sName = "BigData1.xls"
sPath = "C:\Data\"
On Error Resume Next
Set wkbk = Workbooks(sName)
On Error GoTo 0
If wkbk Is Nothing Then
Set wkbk = Workbooks.Open(sPath & sName)
End If
wkbk.Activate

End Sub

The above minimizes the posibility of typing in different names.

--
Regards,
Tom Ogilvy




"sbharbour" wrote in message
...
The box still comes up saying the workbook is already open...

It does try to open the book that is already open, but what I want to do
is suppress the message box telling that it's already open reopening
will cause changes to be discarded, do you want to reopen Yes No

???

The original code did that - the new code I posted does not.

If you suppress that message, then I am not sure whether it re-opens it or
not. Generally suppressing the message takes the default action which
appears to be to reopen the workbook - which could result in loss of data.

To suppress messages

Application.DisplayAlerts = False

' code that would cause the alert

Application.DisplayAlerts = True

But like I said, the second set of code does not try to open the workbook if
it is already open.

--
Regards,
Tom Ogilvy


"sbharbour" wrote in message
...
It does try to open the book that is already open, but what I want to do
is suppress the message box telling that it's already open reopening
will cause changes to be discarded, do you want to reopen Yes No

???

Thanks I've been playing with Application.DisplayAlerts = Flase

But I can't seem to get the code that caused the alert part...

I still get the alert

I've tried Workbooks.Open

and have been hunting for a list of Excel Alert boxes, thinking that
would solve the problem, but I'm still straching my head on this one...