Macro to Show/Hide detail in an outline

NathanPage

New Member
Joined
Dec 8, 2005
Messages
9
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
 

Some videos you may like

Excel Facts

Select a hidden cell
Somehide hide payroll data in column G? Press F5. Type G1. Enter. Look in formula bar while you arrow down through G.

GlennUK

Well-known Member
Joined
Jul 8, 2002
Messages
11,402
Works fine for me. Have you declared Spincount as a particular data type? Are you sure S1 has the value of the spinner. Is the active sheet the correct sheet, and is it unprotected?

It's a shame your users aren't clever enough to use the 1-5 symbols, as that is the whole point of why those symbols are there.
 

NathanPage

New Member
Joined
Dec 8, 2005
Messages
9
Thanks for testing it out/responding, GlennUK.

No, I haven't made any declarations, and I've checked that S1 has the value of the spinner- I've added another line in the macro to change the value of S2 to Spincount to be sure: S1 and S2 are always the same.

The sheet's unprotected.

Sometimes the macro works, sometimes it doesn't, but I cannot find any pattern in when it does/doesn't work.

The spreadsheet is a large one, so takes a while to calculate through (about 1/3rd of a second). Do you think this might be causing it?

Pulling my hair out here! :)
 

GlennUK

Well-known Member
Joined
Jul 8, 2002
Messages
11,402
Sometimes it works and sometimes it doesn't .... sounds very fishy to me .... do you have any cell comments in this sheet? If so, remove them all!
 

NathanPage

New Member
Joined
Dec 8, 2005
Messages
9

ADVERTISEMENT

No, nothing like that!

I've actually just tried another spreadsheet, built from scratch, and have the same problem.
 

NathanPage

New Member
Joined
Dec 8, 2005
Messages
9
I've managed to solve it myself now, although not with such an elegant solution.

I've created two macros, one which shows the next level, one which hides the current level, and just used simple macro buttons for each macro. 'tis ugly, but at least it works!
 

GlennUK

Well-known Member
Joined
Jul 8, 2002
Messages
11,402
Well done. Does sound ugly ... but if works, that's what counts.
 

Watch MrExcel Video

Forum statistics

Threads
1,118,081
Messages
5,570,086
Members
412,310
Latest member
mark884
Top