Macro to show/hide detail in an outline

[NathanPage]

First post here, I hope someone can help!

I have a group outline on one of my sheets that has five separate levels. I need to create buttons on the sheet to hide/show a greater or lesser level of detail (i.e. clicking an "up" button will show one additional level of detail in the outline, clicking the "down" will hide one level).

I've tried using a spinner, which seemed like a suitable option. The spinner was set to minimum 1, maximum 5, linking to cell S1. I then had a simple code that ran when the spinner changed:

Spincount = Range("S1")
ActiveSheet.Outline.ShowLevels RowLevels:=Spincount

However, this often results in a runtime error, of "ShowLevels method of Outline class failed".

If anyone knows why this isn't working, or can think of an alternative method for what I'm trying to do? (Basically, the normal approach of clicking on the 1-5 symbol, or the +/- symbols is not considered simple enough for the users of my report).

Thanks in advance,

Nathan

[Dave Peterson]

I added outlining symbols to a test worksheet using data|subtotals.

I added a spinner from the forms toolbar to that same worksheet. I rightclicked
on it and chose format control and set the min to 1 and the max to 5 and the
increment to 1.

I added this macro to a general module.

Option Explicit
Sub testme()
Dim mySpinner As Spinner
Set mySpinner = ActiveSheet.Spinners(Application.Caller)
ActiveSheet.Outline.ShowLevels RowLevels:=mySpinner.Value
End Sub


I assigned the macro to that spinner and it worked ok for me.



NathanPage wrote:
>
> First post here, I hope someone can help!
>
> I have a group outline on one of my sheets that has five separate
> levels. I need to create buttons on the sheet to hide/show a greater or
> lesser level of detail (i.e. clicking an "up" button will show one
> additional level of detail in the outline, clicking the "down" will
> hide one level).
>
> I've tried using a spinner, which seemed like a suitable option. The
> spinner was set to minimum 1, maximum 5, linking to cell S1. I then had
> a simple code that ran when the spinner changed:
>
> Spincount = Range("S1")
> ActiveSheet.Outline.ShowLevels RowLevels:=Spincount
>
> However, this often results in a runtime error, of "ShowLevels method
> of Outline class failed".
>
> If anyone knows why this isn't working, or can think of an alternative
> method for what I'm trying to do? (Basically, the normal approach of
> clicking on the 1-5 symbol, or the +/- symbols is not considered simple
> enough for the users of my report).
>
> Thanks in advance,
>
> Nathan
>
> --
> NathanPage
> ------------------------------------------------------------------------
> NathanPage's Profile: http://www.excelforum.com/member.php...o&userid=29472
> View this thread: http://www.excelforum.com/showthread...hreadid=491753

--

Dave Peterson