Take the 2-minute tour ×
Super User is a question and answer site for computer enthusiasts and power users. It's 100% free, no registration required.

I have written the following VBA code that contains a condition where if the Cell#="Yes" then expand the bottom row, else if Cell#="No" then collapse the bottom row.

Private Sub Worksheet_Change(ByVal Target As Range)
If Range("E15").Value = "Yes" Then
ActiveSheet.Outline.ShowLevels RowLevels:=3
ElseIf Range("E15").Value = "No" Then
ActiveSheet.Outline.ShowLevels RowLevels:=8
End If
End Sub

Now this seems to be working fine without any issues, but for some reason it expands and collapses all rows that are within the same Row Level as displayed in the code, what I really want is to be able to choose exactly which row I want to expand and collapse based on the condition.'

As you can see I'm not much of a VB Pro, but any help would be much appreciated.

Thanks!

share|improve this question
    
I'm having trouble understanding the question. You have groups of rows where a cell will be "yes" or "no" and you want it to expand that group, or you want it to show the last line of the group? –  Raystafarian Jul 16 '13 at 9:28
add comment

1 Answer

up vote 0 down vote accepted

If I understand you correctly, you should be able to set and unset the ShowDetails property to expand and collapse specific summary rows in your outline.

As shown below, Showdetails is a read/write property of an entire row (or column) within an outline. To show the detail for a specific summary row, reference a single cell in that row as shown in the example code below. Excel will raise an error if you try to set the property to True for a summary level that is already expanded. If the summary row is not visible because it is collapsed within a larger grouping, Showdetails will (silently) fail to expand the row.

Option Explicit

Sub Worksheet_Change(ByVal Target As Range)
    With ActiveSheet
        If .Range("E15").Value = "Yes" Then
            With .Range("A26").EntireRow
                If .ShowDetail = False Then
                    .ShowDetail = True
                End If
            End With
        ElseIf Range("E15").Value = "No" Then
            With .Range("A45").EntireRow
                If .ShowDetail = False Then
                    .ShowDetail = True
                End If
            End With
        End If
    End With
End Sub
share|improve this answer
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.