Can someone please reply with example code on how to determine if a workbook is shared

Thanks much in advance...

quartz

The only way I know is to check the Title bar for [Shared]. The problem is
that if the ActiveWindow is not maximized with Excel, [Shared] doesn't show
up there - and, for some reason, it doesn't show up in ActiveWindow.Caption
either. I came up with maximizing the ActiveWindow, reading the title bar
and checking for [Shared]. Also, you can't turn off ScreenUpdating because
the title bar won't update. Here's what I have:

Sub CheckShared()

Dim OldState As XlWindowState
Dim Msg As String

OldState = ActiveWindow.WindowState

ActiveWindow.WindowState = xlMaximized

If InStr(1, Application.Caption, "[Shared]") 0 Then
Msg = ActiveWorkbook.Name & " is shared"
Else
Msg = ActiveWorkbook.Name & " is not shared"
End If

ActiveWindow.WindowState = OldState

MsgBox Msg

End Sub

--
Dick Kusleika
MVP - Excel
www.dicks-clicks.com
Post all replies to the newsgroup.



"quartz" wrote in message
...
Can someone please reply with example code on how to determine if a
workbook is shared?

Thanks much in advance...

Hi Dick,

How about using the MultiUserEditing property?

Function IsShared(Bookname As String) As Boolean
IsShared = Workbooks(Bookname).MultiUserEditing
End Function

Cheers
Andy

Dick Kusleika wrote:

quartz

The only way I know is to check the Title bar for [Shared]. The problem is
that if the ActiveWindow is not maximized with Excel, [Shared] doesn't show
up there - and, for some reason, it doesn't show up in ActiveWindow.Caption
either. I came up with maximizing the ActiveWindow, reading the title bar
and checking for [Shared]. Also, you can't turn off ScreenUpdating because
the title bar won't update. Here's what I have:

Sub CheckShared()

Dim OldState As XlWindowState
Dim Msg As String

OldState = ActiveWindow.WindowState

ActiveWindow.WindowState = xlMaximized

If InStr(1, Application.Caption, "[Shared]") 0 Then
Msg = ActiveWorkbook.Name & " is shared"
Else
Msg = ActiveWorkbook.Name & " is not shared"
End If

ActiveWindow.WindowState = OldState

MsgBox Msg

End Sub


--

Andy Pope, Microsoft MVP - Excel
http://www.andypope.info

Andy

Well, that sure makes things easy.

--
Dick Kusleika
MVP - Excel
www.dicks-clicks.com
Post all replies to the newsgroup.

"Andy Pope" wrote in message
...
Hi Dick,

How about using the MultiUserEditing property?

Function IsShared(Bookname As String) As Boolean
IsShared = Workbooks(Bookname).MultiUserEditing
End Function

Cheers
Andy