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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
VBA availability AFTER unsharing workbook
I ... find that a lot of the facilities in the VBE are 'greyed out' even
after unsharing the workbook. 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 "Michael Beckinsale" wrote in message ... | 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 | | |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
VBA availability AFTER unsharing workbook
Jim,
I feel such an idiot. You are of course right! Regards |
Reply |
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 |