VBA availability AFTER unsharing workbook

[Michael Beckinsale]

Hi All,

Reposted after thinking that people would think that l have not read that
the VBA code is unavailable in a shared workbook.

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
'xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx
'Copy this code into the 'Shared' workbook - PART 1 of 2
'xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx
Application.DisplayAlerts = False
If ActiveWorkbook.MultiUserEditing Then
ActiveWorkbook.UnprotectSharing
ActiveWorkbook.ExclusiveAccess
End If
Application.StatusBar = ("Please wait initialising workbook.")
Application.DisplayAlerts = True
'xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx
'End of Part 1
'xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx
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")
'xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx
'Copy this code to 'Shared' workbook - PART 2 of 2
'xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx
Application.DisplayAlerts = False
If Not ActiveWorkbook.MultiUserEditing Then
ActiveWorkbook.SaveAs Filename:=ActiveWorkbook.FullName, _
accessMode:=xlShared
End If
Application.DisplayAlerts = True
Application.StatusBar = False
'xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx
'End of Part 2
'xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx
MsgBox ("The workbook is Shared")

End Sub

Regards

Michael Beckinsale

 

[Jim Rech]

I ... find that a lot of the facilities in the VBE are 'greyed out' even
I do not have this problem, at least not after I open a code module for
editing. I suspect you're looking at the menus without doing that.

--
Jim
| Hi All,
|
| Reposted after thinking that people would think that l have not read that
| the VBA code is unavailable in a shared workbook.
|
| 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
|
'xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx
| 'Copy this code into the 'Shared' workbook - PART 1 of 2
|
'xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx
| Application.DisplayAlerts = False
| If ActiveWorkbook.MultiUserEditing Then
| ActiveWorkbook.UnprotectSharing
| ActiveWorkbook.ExclusiveAccess
| End If
| Application.StatusBar = ("Please wait initialising workbook.")
| Application.DisplayAlerts = True
|
'xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx
| 'End of Part 1
|
'xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx
| 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")
|
'xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx
| 'Copy this code to 'Shared' workbook - PART 2 of 2
|
'xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx
| Application.DisplayAlerts = False
| If Not ActiveWorkbook.MultiUserEditing Then
| ActiveWorkbook.SaveAs Filename:=ActiveWorkbook.FullName, _
| accessMode:=xlShared
| End If
| Application.DisplayAlerts = True
| Application.StatusBar = False
|
'xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx
| 'End of Part 2
|
'xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx
| MsgBox ("The workbook is Shared")
|
| End Sub
|
| Regards
|
| Michael Beckinsale
|
|

 

[michael.beckinsale]

Jim,

I feel such an idiot. You are of course right!

Regards