Improve company productivity with a Business Account.Sign Up

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 4861
  • Last Modified:

Group and Outline in Excel

Hi all
I'm using Group and Outline in Excel 2000 to group some collumns using VBA ( Selection.Columns.Group ). My question is : How do I hide and show the grouped collumns using VBA. I tried recording a macro and got the following VBA code :
ExecuteExcel4Macro "SHOW.DETAIL(2,8,FALSE,,7)"
Excel 4 macro ???????
Is there no other code that I can use ? If not, can someone tell me the syntax of this code.

Peter
0
forsbom
Asked:
forsbom
  • 5
  • 2
1 Solution
 
criCommented:
There is, at least for Excel 97.

a) Do you want to show/hide the details for an individual column ? If yes: Do you want a toggle ? If no: Display the level 1,2,3... for all columns ?

b) Do you have users using Excel 97 ? There is a bug I tried to report in vain to Microsoft which must be circumvented.
0
 
criCommented:
Sub Peekaboo()
  'Toggles the show/hide of column 6
  With ActiveSheet.Columns(6)
     .ShowDetail = (.ShowDetail = False)
  End With
End Sub
0
 
forsbomAuthor Commented:
Hi cri
Thanks It's just what I wanted.
Your previous question : Yes my users are using Excel 97! What's the bug and what do I need to do ?????

Peter
0
Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

 
criCommented:
The bug is that if you re-state ShowDetail=False for a column/row with _already_ hidden details, then VBA will crash. Doing this on for ShowDetails=True will be ignored.

Therefore, either:

a) Ensure you always know the show/hidden status

OR

b) Use the Toggle above

OR

c) If the show/hide is for all columns/rows use this command:
ActiveSheet.Outline.ShowLevels RowLevels:=0, ColumnLevels:=1

OR

d) Use this auxiliary Sub:

Sub HideDetail(rngToHide As Range)
   'Necessary because Visual Basic Office 97(SR-2) has a bug:
   '"ShowDetail = False" should not be re-stated if already = False,
   'contrary to ShowDetail = True resp. VB of Excel 5.0

   With rngToHide
      If .ShowDetail = True Then
         .ShowDetail = False
      Else
         'Do absolutely nothing
      End If
   End With
End Sub

Sub test()
  With ActiveSheet
    HideDetail .Columns(6)
    HideDetail .Rows(16)
    MsgBox "Fasten your seat belts, will crash VBA"
    .Rows(16).ShowDetail = False
 End With
 
End Sub
0
 
forsbomAuthor Commented:
Thanks cri!!
Great answer!

Peter
0
 
criCommented:
ThAnk you. Does it crash in Excel 2000 too ?
0
 
criCommented:
Perhaps it was the answer to your question, but I should have asked first whether your intention is to allow grouping/ungrouping on protected worksheets (are there others ?...). In this case, if manual handling is wanted, this solution is much neater:


Option Explicit 'Prevent typos

Private Sub Workbook_Open()
  'Work-around for Excel 97 freezing Grouping/Autofilter for protected workSHEETS
  'As these properties are not saved, this sub re-sets them at open
  'Place this at ThisWorkbook level
 
  Dim ws As Worksheet
  For Each ws In ActiveWorkbook.Worksheets
    With ws
     .EnableOutlining = True
     .Protect contents:=True, DrawingObjects:=True, userInterfaceOnly:=True
     .EnableAutoFilter = True
    End With
  Next ws
End Sub


0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

  • 5
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now