Free Microsoft Excel 2013 Quick Reference

Group and Ungroup Macro

I have a spread sheet where I have the group feature running down the rows. I have rows 5-8 grouped, skipping 9, 10-13 grouped skipping 14, 15-18 grouped skipping 19 etc... all the way down about 31 times. Anyhow the user will click the group to see rows 5-8, put in info, drop to 10-13 and put in info, maybe skip a few and put in info further down. What I want them to be able to do is click a button that hides all the groups reguardless of which ones are open, is there a simple formula for this? I tired to record it, but it did not write code.


Post your answer or comment

comments powered by Disqus
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...


	VB:
	
 Sheet1 
    .Protect Password:="******", UserInterfaceOnly:=True 
    .EnableOutlining = True 
End With 

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines
It works fine on my PC Office 2003 but I hear it won't work on a Mac but I can't confirm it.

Thanks,

Guy

Hi there,

Could someone please assist with the VBA code to be able to group and ungroup columns and rows on a protected worksheet?

I have protected the worksheet with the columns and rows already collapsed, with a password of "mypassword"
The sheet name is Profit&Loss

Any and all assistance would be apprecated

Thanks

I have an excel file that is shared with multiple users. Some of its contents
needs to be protected, so I went to Tools, Protection and I protected the
worksheet, leaving unlocked several rows and columns that needs to be updated
by users, but still some other rows and columns remain protected.

As the file is to long (about 1000 lines) and very wide (it reaches column
IQ), I use the functions to group and ungroup several rows or columns to
quick expand or shrink the file (hide or unhide rows and columns). The
problem comes while the file is shared and protected, the group and ungroup
format functions are not allowed to use.

The cells that can be modified, were previously unlocked (select the cell,
click format cell, protection tab and remove the checkmark at locked, the
placed the protection pworksheet password).

Thanks for the help.

how do you group and ungroup rows or columns in new Excel?

I need help with grouping and ungrouping protected sheets. I have 3 sheets
in a workbook that must be protected. With protection on I can't use the
group and ungroup function. Is there a solution for this ? If anyone could
help that would be great.
--
Regards

Bob.

Good morning,

1. I have got too many "Style" on an Excel model and I cannot remove them. Some of them are locked and when I want to delete them, they duplicate. Could you please suggest what would be the best way to delete all of them including the ones that are locked. It is possible to untick the box "locked" but by doing so, they duplicate and the duplicate ones are locked.

2. On one excel spreadsheet, I cannot either group or ungroup. However I can do so in the other spreadsheets from the model. Do you have an explanation for that and do you know how to fix this?

Thank you in advance for your help.

Regards.

Hello all. I need some assistance. I have just about completed the workbook some of you have already assisted me with, but in trying to prevent future complications with the myrid of codes and formulas, I need to protect the sheets so only certain cells are editable by the user. I setting this protection I have discovered that I am unable to ungroup rows to view the details. The error is: "You cannot use this command on a protected sheet. Unprotect the sheet..."

Is there a way to protect the sheet while allowing the group and ungroup functions to operate??

Thanks,
Andrew

I needs some help with a macro to group and ungroup rows based on a variable changing. In the attached file, you'll see that cells B7:B58 contains the weeks (i.e. Wk 1 to Wk 52). Each time a user changes Cell D3 (i.e. the variable), I want the macro to:

1. Ungroup the whole active worksheet

2. The variable (Cell D3) becomes the latest week (e.g. if the variable is 20 then = Wk 20). All 8 weeks before this current week (i.e. Wks 12-19 or Rows 18-25) and all 8 weeks after this current week (i.e Wks 21-28 or Rows 27-34) will be visible. The rest will be grouped (to Level 1) - i.e. Rows 7-17 and 35-58).

3. If the variable becomes 21, Rows 19-26 and 28-35 will be shown. Rows 7-18 and 36-58 will be grouped (then hidden).

4. Etc.

5. I also want to embed some conditional stuff in there such that if the variable is less than or equal to 8 then rows 15-58 will be grouped.

6. And if the variable is equal to or greater than 45 then the macro will not group any of the rows 51 to 58.

Makes sense?
Book1.xls

I have a sheet that has rows and columns that are grouped.
I need to protect rollup portion of the grouped rows.
However, when I do that and protect the sheet, I can no longer group
and ungroup.
How can I allow the user to group and ungroup rows and columns but
protect the grouped row?
Example:
row 4 is a rollup of MY DATA with the next 10 rows as
supporting/detailed data. I have created a group so that I can
contract 9 of the rows and show only the rollup MY DATA or expand it
and show all the subordinate data. The formulas in the columns MY DATA
are protected while the cells in the supporting rows are available to
the user for change.

However, when I protect the sheet, the user can no longer group and
ungroup. How can I fix this?

Glen

I am trying to use the code below to allow users to group and ungroup in locked sheets:

	VB:
	
 Workbook_Open() 
    Dim ws As Worksheet 
    For Each ws In ThisWorkbook.Worksheets 
        With ws 
            .protect Password:="xxx", UserInterfaceOnly:=True 
            .EnableOutlining = True 
        End With 
    Next ws 
End Sub 

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines
When I test it as a regular macro, and run it once I've opened the excel and locked the sheets, it works perfectly. However as soon as I change it to a workbook_open event it doesn't work at all. help?

Hello,
I want to make a sheet, which will bo fully protected, but grouping and ungrouping will be possible.
I want to have to buttons, one for rows and one for columns.
I've made sth like this. But there is an error, and I don't know how to move further.

Sory 4 my english, but its not ma firtst language.

Names in sheet are in differnt language, but don't look at it.

Thank you very much for your help!

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?

This thread was posted in the Access section. However someone has suggested that this should reside in this section, hence the duplication.

I have VBA code in an Access database that essentially creates a chart in MS Excel. On the bottom axes (Date) I have dates from 01-01-07 to 31-03-07. When I try to record a macro which would provide me with the necessary code to right click on Date and select Group and Show Detail | Group to select Months, no VBA code appears in the macro?

Hi,

I have been using the group and ungroup function to hide and unhide rows in report which I prepare, but I want try upgrading by using macro. Here is what am looking for. Its call center report which I send it to the mangement, the management wants to look at the concern managers team scores and then drill down to the individual team members scores.

By using the grouping function I use to show the team members scores under the manager, now I want to use control tool box function so that I click on the manager's name which would apprently show me the team details. and when I again click on the managers name it should basically Unhide the team or group the team details

Thanks,
Karthik.


Here is the code for a simple copy and paste routine for an embedded
macro. The code opens one book, then opens another. It copies a section from one
book into the other and finally closes both books. I have it set to do
this for about 100 books using sub groups and embedded macros.

The problem? When the code gets to "ActiveSheet.Paste" (after 5 minutes or
so), the error message is: "Run time error '438'. Object doesn't support this property or method."

The catch is: A WHOLE LIST OF THIS PROCEDURE FOR TWENTY OTHER WORKBOOKS IS
PERFORMED DIRECTLY BEFORE THIS SUB-MACRO in Sub GroupI()!

Why get to one workbook and error out when the procedure works for the
first 20? I have turned macro security to high, I have clicked the
"Trusted Sources" checkbox.....I can't make sense of the inconsistency.

This should be EASY!!!! AAAGGGGHHHHH!!!!!!

Here is the code....

Sub GroupII()
Workbooks.Open Filename:="S:BUDGETS5BudgetBudgets -
FieldP001BGT05.xls"
ChDir "S:BUDGETS5BudgetTax"
Workbooks.Open Filename:="S:BUDGETS5BudgetTaxP-05-001.xls"
Sheets("Tax").Select
Range("B8:M41").Select
Selection.Copy
Windows("P001BGT05.xls").Activate
Sheets("Selling Expenses").Select
ActiveSheet.Unprotect
Range("B1402:M1435").Select
ActiveSheet.Paste
ActiveWorkbook.Save
ActiveWorkbook.Close
Windows("P-05-001.xls").Activate
ActiveWindow.Close

has microsoft made it any easier in excel 2003 to allow a user to group and
ungroup a sheet while it is protected?

Anyone know how to protect a worksheet, but also allow the grouping and
ungrouping of columns?

right now i am using the group and ungroup from the data menu to hide
particular rows. i need help (i fairly new to this) creating a macro that
when either a toggle button, or command button is pressed will hide/unhide
the row above it. for instance rows 4&5 are grouped, but row four is
hidden... how can i create a button to put on row 5 to hide/unhide row 4? i
don't like grouping the rows because when i protect the sheet you cannot
group or ungroup the rows...

I used the code you posted but i get a Run-time error 9 - subscript out of
range error.

how do i fix this?

Also, my groups are set up in sheet 6 of my workbook, does that change
anything?

"Debra Dalgleish" wrote:

> If you protect the worksheet programmatically, you can enable outlining,
> and you will be able to use the groups that you have created.
>
> The following code goes in the ThisWorkbook module:
>
> Private Sub Workbook_Open()
> With Worksheets("Sheet1")
> .EnableOutlining = True
> .Protect Password:="password", _
> Contents:=True, UserInterfaceOnly:=True
> End With
> End Sub
>
> To paste the code into the ThisWorkbook module:
>
> Right-click on the Excel icon, to the left of the File menu
> Choose View Code
> Paste the code where the cursor is flashing.
>
>
> Fadi Haddad wrote:
> > 1 -I have grouped data in my excel sheet by using the Group rows function.
> > 2- When i protect the sheet, the goup and Ungroup button (the + sign at the
> > left of the sheet), won't work.
> >
> > Question:
> > Is there a way to proctect the sheet and keep the Group and ungroup (+
> > sign)function normally.
> >
> > Thank you
>
>
> --
> Debra Dalgleish
> Excel FAQ, Tips & Book List
> http://www.contextures.com/tiptech.html
>
>

Many Thanks Debra,

No i didn't protect the sheet programatically, i only locked the sheet by
using the protect sheet under the tools menu.

Thank you in advance for your help, and to you Frank.

"Debra Dalgleish" wrote:

> If you protect the worksheet programmatically, you can enable outlining,
> and you will be able to use the groups that you have created.
>
> The following code goes in the ThisWorkbook module:
>
> Private Sub Workbook_Open()
> With Worksheets("Sheet1")
> .EnableOutlining = True
> .Protect Password:="password", _
> Contents:=True, UserInterfaceOnly:=True
> End With
> End Sub
>
> To paste the code into the ThisWorkbook module:
>
> Right-click on the Excel icon, to the left of the File menu
> Choose View Code
> Paste the code where the cursor is flashing.
>
>
> Fadi Haddad wrote:
> > 1 -I have grouped data in my excel sheet by using the Group rows function.
> > 2- When i protect the sheet, the goup and Ungroup button (the + sign at the
> > left of the sheet), won't work.
> >
> > Question:
> > Is there a way to proctect the sheet and keep the Group and ungroup (+
> > sign)function normally.
> >
> > Thank you
>
>
> --
> Debra Dalgleish
> Excel FAQ, Tips & Book List
> http://www.contextures.com/tiptech.html
>
>

Hi,

I have the following macro in ThisWorkbook (suggested in another topic) to allow grouping and ungrouping in a password protected file.

Sub WorkBook_Open()
Application.ScreenUpdating = False
Dim n As Single
For n = 1 To Sheets.Count
With Sheets(n)
.Protect Password:="test", userinterfaceonly:=True
.EnableOutlining = True
.EnableAutoFilter = True
End With
Next n
Application.ScreenUpdating = True
End Sub

When i apply it to an excel 2000 file it works perfectly. But when i want to open the file in excel 2003 it gives an error:

Run-time error 1004: The password you supplied is not correct. Verify that the caps lock is off etc.etc.

This starts with the worksheets that use grouping. The other sheets before that sheet (with no grouping) are all password protected with the correct password.

Can anybody help? I'm new to macro's...

Thanx!

Michel

right now i am using the group and ungroup from the data menu to hide
particular rows. i need help (i fairly new to this) creating a macro that
when either a toggle button, or command button is pressed will hide/unhide
the row above it. for instance rows 4&5 are grouped, but row four is
hidden... how can i create a button to put on row 5 to hide/unhide row 4? i
don't like grouping the rows because when i protect the sheet you cannot
group or ungroup the rows...

I use a workbook with numerous calculated cells and "grouped" rows of data.
Earlier version of Excel would instantaneously calculate cells and change
view from expanded to grouped or visa versa. After update to Excel 2003 this
process takes at least 3-4 minutes. Any suggestions?

I want to break a set of 52 columns (weeks) into relevant quarters using the
group and outline function, however, Exel in its wisdom automatically merges
adjacent groups.
This could be made to work by inserting blank columns or summary columns
between each quarter. However, many formulae use offsets to look into the
future columns.
Inserting columns of any sort will kaibosh the offset so it's not an option.
I would prefer not to use macros if possible (keeps it simple for when I
leave) but will do as a last resort.

Does anyone know how to get around or 'disable' the merging of columns.


No luck finding an answer? You could always try Google.