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
 

Excel Facts

Test for Multiple Conditions in IF?
Use AND(test, test, test, test) or OR(test, test, test, ...) as the logical_test argument of IF.

GlennUK

Well-known Member
Joined
Jul 8, 2002
Messages
11,688
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,688
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,688
Well done. Does sound ugly ... but if works, that's what counts.
 
Master Excel Bundle

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

Forum statistics

Threads
1,164,134
Messages
5,835,601
Members
430,369
Latest member
pingel

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top