Group / Ungroup Within Protected Sheet?

Jun 12, 2013

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

View 4 Replies


ADVERTISEMENT

Allow For Group/ungroup On Protected Sheet

Jun 1, 2006

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?

View 9 Replies View Related

Group/Ungroup In A Protected Shared Workbook

Aug 20, 2008

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?

View 6 Replies View Related

How Do You Allow Hide/unhide Or Group/ungroup Features While Protected

Dec 20, 2007

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 ....

View 14 Replies View Related

Protect Sheet With Freedom Of Group / Ungroup

Jun 1, 2014

Once I "protect sheet" , i can not group / ungroup the column / row. after protecting sheet, I want the freedom to group/ungroup the column.

View 2 Replies View Related

Can't See Group / Ungroup Buttons

Dec 15, 2011

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.

View 2 Replies View Related

Group / Ungroup - Tree Structure

Mar 6, 2014

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?

View 1 Replies View Related

Group / Ungroup Columns Depending On Value?

Oct 18, 2013

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.

View 2 Replies View Related

Group And Ungroup With Mac Office 2004

Dec 19, 2007

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.

View 3 Replies View Related

Macro To Group / Ungroup Multiple Columns?

Feb 21, 2014

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

[Code].....

View 5 Replies View Related

Group/Outline Worksheets When Protected

Aug 20, 2008

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..........................

View 2 Replies View Related

Group & Outline On Protected Worksheet

Jul 25, 2006

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)

View 6 Replies View Related

Totally Ungroup Existing Grouping Of Rows In Sheet?

Feb 3, 2012

I need to totally ungroup existing grouping of rows in a sheet. Totally ungroup = strip it totally of any grouping. In short, it should be back to its original state of no grouping at all.

Problem is that I do not know if the sheet has existing grouping, or if it does, how many levels of grouping.

The solution I have in mind right now is just to indiscriminately run ungrouping vba line 10X and just place an error handler i.e.

Code:
Sub Macro1()
On Error Resume Next
Range("A5:A29").Rows.Ungroup
Range("A5:A29").Rows.Ungroup
Range("A5:A29").Rows.Ungroup
Range("A5:A29").Rows.Ungroup
Range("A5:A29").Rows.Ungroup

[code].....

Is there a shorter way to handle this code-wise?

View 3 Replies View Related

Retain Use Of Group/Outline On Protected Sheets/Worksheets

Oct 5, 2006

I have a sheet that I protect but i use the below code so that I can still use my gouping '+' or '-' symbols to hide/unhide rows.

ws.Protect Password:="PASSWORD", userinterfaceonly:=True
ws.EnableOutlining = True

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

View 2 Replies View Related

Excel 2010 :: Macro Runs On Protected Sheet But Changes Protect Sheet Options?

Feb 24, 2011

I am using Excel 2010 and I have a password protected workbook with password protected sheets that uses several macros. Most of them, in order to run, have to un-protect the sheet and then re-protect it again. This has been accomplished easily enough by adding ActiveSheet.Unprotect Password:= "mypassword" and ActiveSheet.Protect Password:= "mypassword" to the appropriate places in the script. All of my macros, which do various things like sorting and moving data, deleting blank rows, displaying dialog boxes containing warning messages etc. run fine.

My problem is this: when I password protect the sheets manually, I have checked the following options in the "Protect Sheet" dialog box. Under "Allow users of this worksheet to" I have checked 1)Select unlocked cells and 2)Format cells. After entering my password and closing the dialog box my sheet is protected, but I can edit cells in the manner my allowances permit. However, once I run any of the macros that un-protect and re-protect the sheet, I remain able to select and edit unlocked cells (practically, for my purposes, this means that I can input data which will appear in the default font size and color of the sheet) but I cannot format cells (which, practically, for my purposes would allow me to occasionally change the font color and size of the data). Naturally, after running a macro, the other cell-formatting options are unavailable to me as well. Is there any way to get my manual selections to remain in place after running a macro that functions as mine do? Or is there any way to make my manual selections the default settings for a protected sheet?

View 4 Replies View Related

Copy And Paste From One Sheet To Another On Protected Sheet?

Apr 29, 2014

I have spent ages creating a work diary and have protected the sheets using VBA however on finishing the sheet one important thing that needs to happen is the ability to copy and paste information from the boxes to another worksheet. i.e MONDAY - SUNDAY

View 1 Replies View Related

Password Protected Sheet :: How To Unprotect A Sheet?

Jan 18, 2008

Does anyone know if there is a way to unprotect a sheet. The person who created this spreadsheet no longer works for our company and although we can see the spreadsheet, the formulas are hidden so we can't figure out where the data is being pulled in from. Any ideas?

View 9 Replies View Related

Cant Use [alt] + Pg Up Or Pg Dn On Protected Sheet

Jun 11, 2008

is there a code to still allow me to move between sheet tabs by using [alt] pg up or [alt] pg dn on a protected sheet?

View 9 Replies View Related

If Sheet Protected Then...

Apr 26, 2007

What code would I use to test if the sheet is protected or not? In other words:

If Activesheet.Protection = True Then
MsgBox "You have no authority!!!"
Else
' Some code here
End If

View 5 Replies View Related

Searching A Protected Sheet

Feb 11, 2010

I have a workbook whose worksheets use protection (don't want my calculations stomped on).

When the Protection is on, the search function does not work. You call it up, write what you are looking for, it accepts it, and then when you tell it to search it ignores you.

When you turn the protection off, the search function works just fine.

HOWEVER....Other workbooks I have with protection on its sheets do not share this problem, just this one workbook. And the problem is on all sheets in this workbook. The problem is not on any of the sheets in the other workbooks. I can't see anything different between them, but then I may not know what to look at.

View 2 Replies View Related

Allow Insertion Of Row In Protected Sheet

Sep 4, 2013

I wanted to know:

Q1. How can we insert new rows in a protected sheet?

For example: In the attached sample protected excel sheet "insert rows.xlsx" , if for Employee ID and Name fields, the user has more no. of records than provided, how can he insert new rows considering the fact that Company ID and Name field are locked.

Or any way to address this issue via some other method like adding a scrollbar,etc.

View 5 Replies View Related

Protected Sheet & Allow Use Of Autofilter

Dec 20, 2007

I've locked certain columns on my worksheet so that users cannot overtype target dates etc. I've password protected the worksheet.

The password protection means that for some reason the users can't use the autofilters that are on the header row.

Can someone help me solve this problem; I still need the locked cells and password protection on the sheet but the success of the sheet depends on users being able to filter for specific rows using the autofilter....

View 9 Replies View Related

Protected Sheet Will Not Allow Macro To Run

Mar 27, 2009

I have protected several sheets in my workbook to protect the forumlas and data for being tempered with by the users.

When I try to run my macro, I get an error message stating:

Runtime '1004'
You can not use this command on a protected sheet

And the debug is pointing to this part of my code

View 14 Replies View Related

Autofiltering A Protected Sheet

Dec 11, 2006

When I protect my sheet and have the option to allow the users to autofilter the sheet, how come it doesn't work? I want to allow people to autofilter the data in the worksheet but not modify the contents of the cell.

View 9 Replies View Related

Use Dropbox When Sheet Is Protected

Jan 26, 2009

I have a dropbox located on a sheet which other information needs to remain protected.

How do we give access to users to the dropbox while the remainder of the sheet is protected?

View 9 Replies View Related

AllowFiltering In Protected Sheet

Nov 10, 2009

I have a question relating to the use of autofilter in a protected sheet.

My code is working fine in setting the autofilter and locking down the sheet but a user then loses the ability to use the (Data - Filter - Show All) menu button.

Is there a way to have the worksheet protected, allow the use of the auto filters, and allow the use of the show all button?

Code below incase it is of relevence ...

View 9 Replies View Related

Copy Down Last Row On Protected Sheet

Jul 31, 2008

I am looking for VBA code that will unprotect the Workbook, prompt the user for the number of rows to be added, find the last row in the Worksheet, insert the requested number and reprotect the Worksheet. I would like the location where the rows are inserted to NOT be dependent on the "active" cell.

View 6 Replies View Related

How To Merge And Unmerge In Protected Sheet

Sep 15, 2014

I have a protected sheet to avoid delete of functions. In a specific area, however, i have merged cells of five columns in rows. Because in these lines i have write some text and overcomes the rows, after, what i do is: i unmerge the rows and choose 3 rows from 5 columns together and merge. Thus, the text is in one framework. In fact, every time, i want to merge different regions, but always i want to merged any areas who have marked with the mouse. Then, i delete the text and i need to redo the context of three lines, three separate lines. I hope you understand what i need.MergeAndUnMerge.xlsm

View 1 Replies View Related

Automatically Paste Row On Protected Sheet?

Jul 30, 2008

I want to allow user copy(Ctrl + C) and paste(Ctrl + V) a row. because some columns are locked and the sheet is protected. so, when user doing so, a warning dialog is show and can't do the paste.

so, I want to make it available in code. what I want to do is add some codes in this function:

VB:
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If Application.CutCopyMode = True Then 'there are someing in clipboard
Sheet1.Unprotect 'unprotect sheet, so can paste
End If

and in Worksheet_Change function, I protect the sheet again.

But I find that when I do Sheet1.Unprotect , the clipboard is empty! I just want to allow user can copy and paste a row, the locked cell's value can be ignored.

View 9 Replies View Related

Row Adding With Formula In Protected Sheet

Feb 11, 2014

I'm trying to manage my Stock by using protected sheet, but i stuck with this "row adding with formula" stuff.

The user can only write in the colored cell, but everytime the user add new row, the formula doesn't copying by itself

I attach the file below with no password, if it's protected, the password is none, just press enter ( password : "" )

Book2.xls

View 5 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved