Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
VBA availability AFTER unsharing workbook
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 '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 |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Unsharing a workbook in Excel 2007 | Excel Worksheet Functions | |||
Prevent Users from unsharing a workbook | Excel Worksheet Functions | |||
Preventing users from unsharing a workbook | Excel Discussion (Misc queries) | |||
shared workbook keeps locking and "unsharing" - why? | Excel Discussion (Misc queries) | |||
Unsharing a workbook | Excel Programming |