Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Macro to show/hide detail in an outline
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 |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Macro to show/hide detail in an outline
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Closing File Error | Excel Discussion (Misc queries) | |||
Date macro | Excel Discussion (Misc queries) | |||
sorting detail rows - summary row in an outline | Excel Discussion (Misc queries) | |||
Macro for Show/Hide Range | Excel Discussion (Misc queries) | |||
Macro for Show/Hide Column | Excel Discussion (Misc queries) |