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.