If a Workbook is already open?

[Mick]

Hi

I have a macro that opens a workbook for the user to modify. However, I
need to stop the user from opening the workbook for a second time. I have
used the normal read only attribute which works to a degree, but if the user
chooses "No" I get an error in the macro. Also I don't want the user to be
able to say "Yes" and loose all the modifications made by opening the
workbook again.

 

[Ken Loomis]

I use this function to test if a file is open before I let the user try to
open it.

Function WorkbookIsOpen(wbname) As Boolean
' Returns TRUE if the workbook is open
Dim x As Workbook
On Error Resume Next
Set x = Workbooks(wbname)
If Err = 0 Then WorkbookIsOpen = True _
Else WorkbookIsOpen = False
End Function


I call that function with this statement:

SummaryFileOpen = WorkbookIsOpen(summaryFileName)

and declare 'summaryFileName' this way

Private Const summaryFileName As String = "Summary.xls"

I got that function from the JW book on Excel Power Programming with VBA.

KL

 

[Mick]

Thanks Ken
Ken

Thanks for the code unfortunately my knowledge is limited and I can't get it
to work. I have put my code below which I have modified with what you sent,
in the hopes you can assist me further.

I'm not sure if I have all the elements in the correct place or not.
Basically I open OpenPRM.xls which this code is in and that in turn opens
Toolbox.xls, which is hidden. It then opens MasterRoster.xls and then
OpenPRM.xls then closes, leaving MasterRoster.xls in view. In essence all I
want to do is stop this process running again if MasterRoster.xls is already
open.

If I run it as it stands I get a Run-time error 91 at the
"ActiveWindow.Visible = False" line. If I rem it out it loads OK the first
time. If I then open it a second time I get the message "do I want to reopen
or not" If I answer Yes it does If I answer No it gives Run-time error 1004
and stops at the line "Workbooks.Open FileName:=ThisWorkbook.Path &
"\MasterRoster.xls", UpdateLinks:=3".

Is it something to do with where I have put "SummaryFileopen =
WorkBookIsOpen(MasterRoster)"

Private Const SummaryFileName As String = "MasterRoster.xls"

Regards and thanks again

Mick

Function WorkBookIsOpen(MasterRoster) As Boolean

'returns TRUE if the workbook is open

Dim WB As Workbook

On Error Resume Next

Set WB = Workbooks(MasterRoster)

If Err = 0 Then WorkBookIsOpen = True _

Else WorkBookIsOpen = False

End Function

Private Sub CommandButton1_Click()

Workbooks.Open FileName:=ThisWorkbook.Path & "\Toolbox.xls", UpdateLinks:=3,
ReadOnly:=True, ignorereadonlyrecommended:=True

ActiveWindow.Visible = False

SummaryFileopen = WorkBookIsOpen(MasterRoster)

Workbooks.Open FileName:=ThisWorkbook.Path & "\MasterRoster.xls",
UpdateLinks:=3

Workbooks("OpenPRM.xls").Close SaveChanges:=False

End Sub



Regards

Mick