I like the message that says

Wbook.xls is locked for editing
by 'Username'
Click 'Notify' to open a read-only copy of the document and
receive notification when the document is no longer in use.

But if I open a workbook via

Workbooks.Open Filename:="Wbook.xls"

...it just opens Read-only, which is not what I want.

Can I get VBA to do this, so the user would be notified? I could
duplicate the text in VBA, but it wouldn't notify anybody.

Dan Williams
danwPlanet

Dan,

1. Test to see if workbook is already open, if yes, let users know via
msgbox...
Private Function IsFileOpen(FileName As String)
Dim iFileNum As Integer
Dim sErrDescr As String
Dim iErrNum As Integer
On Error Resume Next
iFileNum = FreeFile()
Open FileName For Input Lock Read As #iFileNum
Close iFileNum
iErrNum = Err
sErrDescr = Err.Description
Select Case iErrNum
Case 0
IsFileOpen = False
Case 70 'Permission Denied
IsFileOpen = True
Case 53
IsFileOpen = False
Case Else
IsFileOpen = "Error #: " & iErrNum & " - " & sErrDescr
End Select
End Function



2. When you open a workbook, you have several optional parameters:
Workbook.Open(FileName, UpdateLinks, ReadOnly, Format, Password,
WriteResPassword, IgnoreReadOnlyRecommended, Origin, Delimiter, Editable,
Notify, Converter, AddToMru, Local, CorruptLoad).

So your code would read something like:
Workbooks.Open Filename:="Wbook.xls", Notify:=True

Good Luck!!
--
Thx
MSweetG222



"Dan Williams" wrote:

I like the message that says

Wbook.xls is locked for editing
by 'Username'
Click 'Notify' to open a read-only copy of the document and
receive notification when the document is no longer in use.

But if I open a workbook via

Workbooks.Open Filename:="Wbook.xls"

...it just opens Read-only, which is not what I want.

Can I get VBA to do this, so the user would be notified? I could
duplicate the text in VBA, but it wouldn't notify anybody.

Dan Williams
danwPlanet

Thanks!

(I had deleted my posting when I realized I should have gone back to
Help and looked at Workbooks.Open. Then I saw your reply, which in
Google Groups now appears as a reply by itself. I replied with a
thank-you, but now when I look in Google Groups, my thank-you didn't
take, so here's another try. Thanks again!)
Dan

MSweetG222 wrote:
Dan,

1. Test to see if workbook is already open, if yes, let users know via
msgbox...
Private Function IsFileOpen(FileName As String)
Dim iFileNum As Integer
Dim sErrDescr As String
Dim iErrNum As Integer
On Error Resume Next
iFileNum = FreeFile()
Open FileName For Input Lock Read As #iFileNum
Close iFileNum
iErrNum = Err
sErrDescr = Err.Description
Select Case iErrNum
Case 0
IsFileOpen = False
Case 70 'Permission Denied
IsFileOpen = True
Case 53
IsFileOpen = False
Case Else
IsFileOpen = "Error #: " & iErrNum & " - " & sErrDescr
End Select
End Function



2. When you open a workbook, you have several optional parameters:
Workbook.Open(FileName, UpdateLinks, ReadOnly, Format, Password,
WriteResPassword, IgnoreReadOnlyRecommended, Origin, Delimiter, Editable,
Notify, Converter, AddToMru, Local, CorruptLoad).

So your code would read something like:
Workbooks.Open Filename:="Wbook.xls", Notify:=True

Good Luck!!
--
Thx
MSweetG222



"Dan Williams" wrote:

I like the message that says

Wbook.xls is locked for editing
by 'Username'
Click 'Notify' to open a read-only copy of the document and
receive notification when the document is no longer in use.

But if I open a workbook via

Workbooks.Open Filename:="Wbook.xls"

...it just opens Read-only, which is not what I want.

Can I get VBA to do this, so the user would be notified? I could
duplicate the text in VBA, but it wouldn't notify anybody.

Dan Williams
danwPlanet

No problem. Thx for the reply.
--
Thx
MSweetG222