idea设置多级目录展开-Excel实现分级菜单-多级展开

使用Treeview树控件来实现分级菜单-多级展开，您可能经常见到，但Excel自身使用单元格来实现分级菜单可能您很少见吧

下面就使用VBA代码来实现这个功能。

Excel中国源创教程：

实现的效果图如下：

动画展示：

实现的VBA完整代码：

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)

    Dim k, i, j, p

    If Target.Row = 1 Or Target.Text = "" Or Target.Column > 2 Then Exit Sub

    Application.ScreenUpdating = False

    j = UsedRange.Rows.Count

    If Target.NumberFormatLocal = "@* -" Then

        Target.NumberFormatLocal = "@* +"

        k = True

    Else

        Target.NumberFormatLocal = "@* -"

        k = False

    End If

    For i = Target.Row + 1 To j

        Rows(i).EntireRow.Hidden = k

        If Target.Column = 1 Then

            If Cells(i, 2).NumberFormatLocal = "@* +" Or k = True Then

                p = True

            ElseIf Cells(i, 2).NumberFormatLocal = "@* -" Then

                p = False

            End If

            If Cells(i, 2) = "" Then Rows(i).EntireRow.Hidden = p

        End If

        If Cells(i + 1, 1) <> "" Or Target.Column = 2 And Cells(i + 1, 2) <> "" Then Exit For

    Next i

    Cancel = True

    Application.ScreenUpdating = True

End Sub