Shared workbook-VBA availability
 

Hi All,

I have a workbook with a significant amount of VBA code which needs to be
shared. Using the following code (but using a password on sheet protection)
l am able to make the workbook 'unshared' on opening, do various
initialisation tasks, re-protect the worksheets and make the workbook shared
again. Everything l have tested so far works fine. However l now need to
make some enhancements and find that a lot of the facilities in the VBE are
'greyed out' even after unsharing the workbook. ie the Edit menu can be
accessed but everything except 'Bookmark' is greyed out. I vaguely remember
reading somewhere that sharing a workbook with sheets protected by a
passwords can cause problems, is this true?

Does anybody know what is causing this and what the solution is ?

I am running Windows XP and Excel 2003.

All contributions / suggestions gratefully received.

Private Sub Workbook_Open()

Dim Filename As String
Filename = ActiveWorkbook.Name
'xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx
'Copy this code into the 'Shared' workbook - PART 1 of 2
'xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx
Application.DisplayAlerts = False
If ActiveWorkbook.MultiUserEditing Then
ActiveWorkbook.UnprotectSharing
ActiveWorkbook.ExclusiveAccess
End If
Application.StatusBar = ("Please wait initialising workbook.")
Application.DisplayAlerts = True
'xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx
'End of Part 1
'xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx
For Each Sht1 In Workbooks(Filename).Worksheets
Sht1.Unprotect
Next Sht1

MsgBox ("The sheets are unprotected")

For Each Sht1 In Workbooks(Filename).Worksheets
Sht1.Protect , userinterfaceonly:=True
Sht1.EnableAutoFilter = True
Next Sht1

MsgBox ("The sheets are protected")
'xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx
'Copy this code to 'Shared' workbook - PART 2 of 2
'xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx
Application.DisplayAlerts = False
If Not ActiveWorkbook.MultiUserEditing Then
ActiveWorkbook.SaveAs Filename:=ActiveWorkbook.FullName, _
accessMode:=xlShared
End If
Application.DisplayAlerts = True
Application.StatusBar = False
'xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx
'End of Part 2
'xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx
MsgBox ("The workbook is Shared")

End Sub

Regards

Michael Beckinsale