I used some code to allow grouping/ungrouping in a protected sheet. This works great! Enable Outline/Outlining on a Protected Excel Worksheet. However, when I share this protected workbook, all of a sudden grouping and ungrouping is not possible anymore. I get the same error as before, when the workbook was protected, not shared, and did not have the code mentioned above. Error: "You cannot use this command on a protected sheet. To unprotect..." Assumption: Is it possible that the Workbook Open Event is not being triggered if the workbook is a shared workbook?
Question: How can I group/ungroup columns in a shared (and protected) workbook?
I have put the following code into my excel workbook to allow me to ungroup sections in a protected sheet. How can I change it to specify multiple worksheets?
Private Sub Workbook_Open() With Worksheets("P5") .EnableOutlining = True .Protect Password:="rob", _ Contents:=True, UserInterfaceOnly:=True End With End Sub
I'm building a template for various users. I have protected the sheet with only certain cells available for input. The file is very large and there are some sections that can be group/ungrouped as the user desires.
however, once I protect the sheet, they can no longer click the - or + to expand and collapse. How can I enable group/ungroup on a protected sheet?
We have a file that needs to be updated monthly by running macros, but the macros are being blocked by protection. I would like to put some code at the start of the macro that unlocks all sheets and then at the end locks them up again. Actually, all I really need is a macro to do just that unlock all sheets, and then another one that locks them back up again as we will need to lock and unlock at various times and it is annoying to have to do it by sheet ....
I would like to know how the following code provided in the Excel Help Best Practice Forums , used to enable outlining on a protected sheet, can be applied to a shared workbook.
Private Sub Workbook_Open() With Sheet1 .Protect Password:="Secret", UserInterfaceOnly:=True .EnableOutlining = True End With End Sub
After enabling 'Share Workbook' and re-opening the file it presents the following error:
Run-time error '1004': Method 'Protect' of object '_Worksheet' failed
I have a protected worksheet with some macros running on it, and I have been requested to make the workbook shared. Trying to implement this, I keep getting the standard "1004 You cannot use this command on a protected sheet" error when applying an autofilter.
Here is what is making this problem a little tricky:
- As the workbook is shared, I cannot do Protect UserInterfaceOnly because you can't change the protection settings on a shared workbook without unsharing it. Needless to say, I cannot unshare the workbook.
- When I protect the sheet, I do allow the user to use autofilter. Consequently, Sheet.Protection.AllowFiltering and Sheet.EnableAutofilter both are on. Still, I keep getting the error.
where shData is the codename of the sheet, lngField is the number of the field and strArg is the filtering criteria.
My guess is, that the error comes from the fact that (for some silly reason) in VBA the .AutoFilter method is used to create a new filter, not only to use an existing one. The members of Sheet.AutoFilter.Filters collection are read-only, and cannot be used to modify the existing filter.
I am having a problem with one of my workbooks. I have grouped some columns, but now I can't see the button on the top of the grouped ones. I tried to group/ungroup again and, as there were some hidden rows, I tried to unhide them all and it is still not working.
I am trying to create a sheet to resemble tree structure of rows. That is by default all the parent rows must be visible and click of + sign, the row should expand to show its child rows which in turn can have child rows but appear collapsed. When I choose two rows and click on Group and select rows, I see a grouping but when I try to collapse using - sign, both 2 rows gets disabled. I want to make the first row as parent and second row as child. How do I do it?
I have a ton of worksheets that I maintain for Budget, LE, LE2, LE3. Every time we complete a Budget I need to group the LE Columns, and once we complete an LE, the budget column needs to be grouped and LE column ungrouped. So twice a year I need to make these changes. It is quite cumber some to change this for multiple tabs.
I already have a technical worksheet to dynamically change the date, naming etc. I want to be able to add a cell that will either read 200 (budget), or 300 (LE). Depending on what his cell reads, I want to group and ungroup their respective columns.
Can any Mac users out there confirm that if a sheet is protected that a user cannot group and ungroup rows using the "+" if the sheet is protected using the following...
With Sheet1 .Protect Password:="******", UserInterfaceOnly:=True .EnableOutlining = True End With
It works fine on my PC Office 2003 but I hear it won't work on a Mac but I can't confirm it.
What I need to do is group and collapse certain columns when Cell D4 in my Date tab reads Bud. If it doesn't read Bud, I need it to ungroup those columns and group and collapse other columns.
So for example: If I have 6 columns (A - F), when Date tab cell D4 reads 'Bud'. I want to group columns A and B, and only show C-F. If the cell doesn't read Bud, I want columns A and B to be ungrouped, and Columns E and F to be grouped and collapsed.
I've been racking my brain, using the Record Macro and just cannot get it correct. Granted I have created something similar to HIDE my columns, however I need it to be grouped so that higher management can ungroup to see some details if need be.
Code: Sub GroupColumn() Dim s As Worksheet For Each s In ActiveWorkbook.Sheets(Array("Name1", "Name2, "Name3", "Name4")) If Worksheets("Date").Cells(1, 4).Value = "Bud" Then
I have autofilter enabled on the header row across the top of my worksheet. Is there a quick line of VBA I can use to toggle EACH one to (ALL). This would act as a "Reset" in my spreadsheet and display all content. I had a solution that worked, until I made the workbook shared / password protected.
I Need the outline/group feature to work on a protected workbook with over 200 sheets, meaning a macro to cover the entire workbook not just a single sheet. This macro should travel with the workbook as it will be on many different peoples PC's
Private Sub Workbook_Open() Dim cSheet As Integer Dim tSheet As Worksheet On Error Resume Next cSheet = 1 Set tSheet = Worksheets(cSheet) While Err.Number = 0 Call wbProtect(tSheet) cSheet = cSheet + 1 Set tSheet = Worksheets(cSheet) Wend Err.Number = 0 End Sub..........................
I curently using the group and outline to subtotal certain data. I do not want to data to be modified. As a result I protect the particular sheet. Problem is once I protect the worksheet, I can't expand the data hidden (using the group & outline). Is there anyway that i can expand the data & in the same time protect the data. A sample in enclose(without worksheet protected)
This is ok but When close the workbook and reopen it, my sheet becomes fully protected and i cant use the group icons. Can anyone either suggest better code or a way to initilise the workbook to act in this way when it loads up. For Reference Below is my full
Sub ProtectAll() Dim ws As Worksheet sSheet = Control.Name For Each ws In ThisWorkbook.Worksheets Select Case ws.Name Case sSheet1 Case Else ws.Protect Password:="PASSWORD", userinterfaceonly:=True ws.EnableOutlining = True End Select Next ws End Sub
I have an Excel workbook which is networked throughout our business. Its been in daily use for years with no problems. Every month I add a new sheet and to do that I untick the "allow changes by more than one user...etc". However this month the tick is greyed out and wont let me clear it. The pc's run windows XP professional with Excel 2003.
I have a workbook that is no longer being shared. I tried clicking on Tools > Shared Workbook. BUT i keep getting a message saying "The file C:CCTPAccountsSummary1.XLS cannot be found". Is there any way to kill this Shared workbook so I am free to modify everything?
How do I get a shared workbook to automatically save upon every change by each user. The only way I can get this to work (at this point) is to run a macro that updates every 45 seconds IN EACH WORKBOOK! It's counterintuitive because the workbook is shared (on a network), but each user must start the "time" macro in their "individual" SHARED workbook to allow the automatic saving.
What am I missing? There has to be a more efficient way to do this, right?
Code below HTML Sub Time()
Call Save Application.OnTime Now + TimeValue("00:00:45"), "Time"
I have a workbook that i am trying to make a shared workbook.
Shared workbooks have all kinds of functionality limitations. The one specifically giving me problems is the ability to protect workbooks.
Once a workbook is shared it's protection cannot be changed.
I protect a workbook prior to sharing and set the UserInterfaceOnly property = true (this allows my code to do whatever it wants but still maintains protection in the user interface).
Once i share and excel makes me save, everything works super and all the code in my auto_open macro works perfectly. But as soon as i close and open the file it seems that the UserInterfaceOnly property i set to true turns off and my code stops working.
Because it's a shared workbook i cannot turn it back on in the auto_open because that functionality is disabled.
Which version of excel allows you to do a Track Changes without switching to a shared workbook? I am awared the Excel 2002 version allows Track Changes on the conditioned that the workbook is switch to shared mode.
1. I have a shared and protected workboook that about 15 people update. For some reason, when people open it they can't always scroll. Anyone know why this happens and how to fix?
2. Is there a setting so that whoever opens the workbook will see the same view (instead of whatever view the last person saved the file in)?
Is there a way to setup personal views of a shared workbook ? For eg : I might hide columns f to j and the other person might have to hide columns h to m. Also, one person might be using ver 2003 and other person using 2010.
Our main spreadsheet in the office is a shared filed which is accessed by 3 pc's. We have been using this set-up for nearly 3 years and no problems until this last week. Now, over the past week, this message pops up when we try to save the file on the PC's not hosting the file.
"Excel was unable to save the workbook because there was a problem reading or applying part of the revision history. You can unshare the work book or save to a non shared workbook format to fix the problem"
I have shared/unshared/reshared and shared again but a week later, I still keep getting this message popping up. Any ideas?
One thing I have noticed is that we have set it in the advanced tab of the share workbook options not to keep revision history, but sometimes it also shows 'keep history for 30 days'.
