Collapse item in subtotal by using VBA

I have made a macro which make a subtotal of a page in two levels:

Selection.Subtotal GroupBy:=1, Function:=xlSum, TotalList:=Array(8, 10), _
Replace:=True, PageBreaks:=False, SummaryBelowData:=False
Selection.Subtotal GroupBy:=2, Function:=xlSum, TotalList:=Array(8, 10), _
Replace:=False, PageBreaks:=False, SummaryBelowData:=False

Now I want to make a checkbutton where the user can select to collapse an
item on the second level. My problem is now that I can't find any information
on how to select a special item and then collapse it with VBA. When I record
the acction, where I manually click on the "-" to the left of this item and
it collapse. When I stop the recording of the macro I find no code.

Hope someone can help on this one.

Take a look at the outline object, for example

ActiveSheet.Outline.ShowLevels 3

--
HTH

Bob Phillips

"Karin Iversen" <Karin Iversen@discussions.microsoft.com> wrote in message
news:9163101B-D63F-4C3F-969E-1235FB7605D9@microsoft.com...
> I have made a macro which make a subtotal of a page in two levels:
>
> Selection.Subtotal GroupBy:=1, Function:=xlSum, TotalList:=Array(8,
10), _
> Replace:=True, PageBreaks:=False, SummaryBelowData:=False
> Selection.Subtotal GroupBy:=2, Function:=xlSum, TotalList:=Array(8,
10), _
> Replace:=False, PageBreaks:=False, SummaryBelowData:=False
>
> Now I want to make a checkbutton where the user can select to collapse an
> item on the second level. My problem is now that I can't find any
information
> on how to select a special item and then collapse it with VBA. When I
record
> the acction, where I manually click on the "-" to the left of this item
and
> it collapse. When I stop the recording of the macro I find no code.
>
> Hope someone can help on this one.
>
>
>

This does not work as I want. I have a list and the

ActiveSheet.Outline.ShowLevels 2

collapse all on the 2 level - I just want to collapse one of the subtotals
on the 2 level.

2006
January
XXX 1000
YYY 2000
February
XXX 1025
YYY 500
March
XXX 200
YYY 562

By the Outline above all months are collapsed.

I want to collapse only the month February. Is this possible?

Karin


"Bob Phillips" wrote:

> Take a look at the outline object, for example
>
> ActiveSheet.Outline.ShowLevels 3
>
> --
> HTH
>
> Bob Phillips
>
> "Karin Iversen" <Karin Iversen@discussions.microsoft.com> wrote in message
> news:9163101B-D63F-4C3F-969E-1235FB7605D9@microsoft.com...
> > I have made a macro which make a subtotal of a page in two levels:
> >
> > Selection.Subtotal GroupBy:=1, Function:=xlSum, TotalList:=Array(8,
> 10), _
> > Replace:=True, PageBreaks:=False, SummaryBelowData:=False
> > Selection.Subtotal GroupBy:=2, Function:=xlSum, TotalList:=Array(8,
> 10), _
> > Replace:=False, PageBreaks:=False, SummaryBelowData:=False
> >
> > Now I want to make a checkbutton where the user can select to collapse an
> > item on the second level. My problem is now that I can't find any
> information
> > on how to select a special item and then collapse it with VBA. When I
> record
> > the acction, where I manually click on the "-" to the left of this item
> and
> > it collapse. When I stop the recording of the macro I find no code.
> >
> > Hope someone can help on this one.
> >
> >
> >
>
>
>

Karin,

The only thing I can think of is somehow identifying all of the rows within
the group and hiding them one by one. Here is an example of what I mean

Dim rng As Range
Dim i As Long

For i = 1 To Cells(Rows.Count, "A").End(xlUp).row
If Cells(i, "A").Value = "Feb" Then
Rows(i).ShowDetail = False
End If
Next i


--
HTH

Bob Phillips

"Karin Iversen" <KarinIversen@discussions.microsoft.com> wrote in message
news:E29DEC5D-C8F1-495A-A708-F51EC9489D89@microsoft.com...
> This does not work as I want. I have a list and the
>
> ActiveSheet.Outline.ShowLevels 2
>
> collapse all on the 2 level - I just want to collapse one of the subtotals
> on the 2 level.
>
> 2006
> January
> XXX 1000
> YYY 2000
> February
> XXX 1025
> YYY 500
> March
> XXX 200
> YYY 562
>
> By the Outline above all months are collapsed.
>
> I want to collapse only the month February. Is this possible?
>
> Karin
>
>
> "Bob Phillips" wrote:
>
> > Take a look at the outline object, for example
> >
> > ActiveSheet.Outline.ShowLevels 3
> >
> > --
> > HTH
> >
> > Bob Phillips
> >
> > "Karin Iversen" <Karin Iversen@discussions.microsoft.com> wrote in
message
> > news:9163101B-D63F-4C3F-969E-1235FB7605D9@microsoft.com...
> > > I have made a macro which make a subtotal of a page in two levels:
> > >
> > > Selection.Subtotal GroupBy:=1, Function:=xlSum,
TotalList:=Array(8,
> > 10), _
> > > Replace:=True, PageBreaks:=False, SummaryBelowData:=False
> > > Selection.Subtotal GroupBy:=2, Function:=xlSum,
TotalList:=Array(8,
> > 10), _
> > > Replace:=False, PageBreaks:=False, SummaryBelowData:=False
> > >
> > > Now I want to make a checkbutton where the user can select to collapse
an
> > > item on the second level. My problem is now that I can't find any
> > information
> > > on how to select a special item and then collapse it with VBA. When I
> > record
> > > the acction, where I manually click on the "-" to the left of this
item
> > and
> > > it collapse. When I stop the recording of the macro I find no code.
> > >
> > > Hope someone can help on this one.
> > >
> > >
> > >
> >
> >
> >

Bob,

That works.

Thanks for your help,
Karin


"Bob Phillips" wrote:

> Karin,
>
> The only thing I can think of is somehow identifying all of the rows within
> the group and hiding them one by one. Here is an example of what I mean
>
> Dim rng As Range
> Dim i As Long
>
> For i = 1 To Cells(Rows.Count, "A").End(xlUp).row
> If Cells(i, "A").Value = "Feb" Then
> Rows(i).ShowDetail = False
> End If
> Next i
>
>
> --
> HTH
>
> Bob Phillips
>
> "Karin Iversen" <KarinIversen@discussions.microsoft.com> wrote in message
> news:E29DEC5D-C8F1-495A-A708-F51EC9489D89@microsoft.com...
> > This does not work as I want. I have a list and the
> >
> > ActiveSheet.Outline.ShowLevels 2
> >
> > collapse all on the 2 level - I just want to collapse one of the subtotals
> > on the 2 level.
> >
> > 2006
> > January
> > XXX 1000
> > YYY 2000
> > February
> > XXX 1025
> > YYY 500
> > March
> > XXX 200
> > YYY 562
> >
> > By the Outline above all months are collapsed.
> >
> > I want to collapse only the month February. Is this possible?
> >
> > Karin
> >
> >
> > "Bob Phillips" wrote:
> >
> > > Take a look at the outline object, for example
> > >
> > > ActiveSheet.Outline.ShowLevels 3
> > >
> > > --
> > > HTH
> > >
> > > Bob Phillips
> > >
> > > "Karin Iversen" <Karin Iversen@discussions.microsoft.com> wrote in
> message
> > > news:9163101B-D63F-4C3F-969E-1235FB7605D9@microsoft.com...
> > > > I have made a macro which make a subtotal of a page in two levels:
> > > >
> > > > Selection.Subtotal GroupBy:=1, Function:=xlSum,
> TotalList:=Array(8,
> > > 10), _
> > > > Replace:=True, PageBreaks:=False, SummaryBelowData:=False
> > > > Selection.Subtotal GroupBy:=2, Function:=xlSum,
> TotalList:=Array(8,
> > > 10), _
> > > > Replace:=False, PageBreaks:=False, SummaryBelowData:=False
> > > >
> > > > Now I want to make a checkbutton where the user can select to collapse
> an
> > > > item on the second level. My problem is now that I can't find any
> > > information
> > > > on how to select a special item and then collapse it with VBA. When I
> > > record
> > > > the acction, where I manually click on the "-" to the left of this
> item
> > > and
> > > > it collapse. When I stop the recording of the macro I find no code.
> > > >
> > > > Hope someone can help on this one.
> > > >
> > > >
> > > >
> > >
> > >
> > >
>
>
>