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

What does custom number format of ;;; mean?
Three semi-colons will hide the value in the cell. Although most people use white font instead.
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.
 
Upvote 0
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! :)
 
Upvote 0
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!
 
Upvote 0
No, nothing like that!

I've actually just tried another spreadsheet, built from scratch, and have the same problem.
 
Upvote 0
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!
 
Upvote 0
Well done. Does sound ugly ... but if works, that's what counts.
 
Upvote 0

Forum statistics

Threads
1,213,534
Messages
6,114,185
Members
448,554
Latest member
Gleisner2

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
Back
Top