Logo 
Search:

MS Office Forum

Ask Question   UnAnswered
Home » Forum » MS Office       RSS Feeds

How to fold/unfold outline's level 1(or any level) in VBA?

  Asked By: Adella    Date: Sep 18    Category: MS Office    Views: 2943
  

I tried to record a VBA that unfold/fold level1 in an outline, but
I do not see any code generated in Macro. Does anyone konw how to code
in VBA to unfold/fold any level of outlines?

Share: 

 

4 Answers Found

 
Answer #1    Answered By: Hubayshah Mansour     Answered On: Sep 18

Give this a try...

Sub ShowOutlineLevel()

Const SUMMARY_LEVEL = 1
Const SECOND_LEVEL = 2
Const THIRD_LEVEL = 3

'ActiveSheet.Outline.ShowLevels RowLevels:=SUMMARY_LEVEL
'ActiveSheet.Outline.ShowLevels RowLevels:=SECOND_LEVEL
ActiveSheet.Outline.ShowLevels RowLevels:=THIRD_LEVEL

End Sub

 
Answer #2    Answered By: Dallas Martin     Answered On: Sep 18

Thanks..it works.
But I found that it's very strange that now I need to click the original level
1, 2 ,3 button provided by Excel in the left side of the spreadsheet "twice" to
unfold/fold (it used be to "once"). Why does this happen?

Please try a very simple case and you would duplicate my problem right away.

 
Answer #3    Answered By: Tracy Cole     Answered On: Sep 18

Not sure what's going on, but I couldn't replicate your problem - even
tried it upto 6 levels.

 
Answer #4    Answered By: Adalrich Fischer     Answered On: Sep 18

I found that because I placed the code  in a command box, then when I
press the command box to display only level1, it worked as
expected. At this time, I lost the cursor. Then if I want to click
the original level  1, 2 ,3 button provided by Excel in the left side
of the spreadsheet, I need to do it twice: first time to bring
cursor's attention back to spreadsheet, second time to do the normal
fold/unfold job. I sloved this problem by adding something like
--
Cells(currentRow, currentCol).Select
--
at the very end of the code in command box to bring cursor's
attention back. It is OK now.

But, I just wonder must I do this extra step? Shouldn't Excel do
that for us?

 
Didn't find what you were looking for? Find more on How to fold/unfold outline's level 1(or any level) in VBA? Or get search suggestion and latest updates.




Tagged: