问显示某些行，但隐藏其他行
EN

Stack Overflow用户

提问于 2014-11-24 14:00:12

回答 1查看 245关注 0票数 1

我正在使用一个excel工作簿，其中一个工作表上的行根据在另一个工作表中选择的选项而隐藏或显示。这个结构看起来像这样

用户可以选择隐藏A和B、A和C、A、B或C。用户可以选择隐藏A和B或C(他们必须在B或C之间进行选择).They还可以选择隐藏每个字母下的单独行。第1、2和3行。如果选中隐藏2的选项，则隐藏每个字母下的所有"2“行。如果取消选中此选项，则所有2行将再次出现。问题是已经隐藏的字母的"2“行将会显示。

我遇到了心理障碍，但这就是我所做的。Psuedocode的可读性，因为现在我的代码是凌乱的，我讨厌vba的样子。无论如何，这是一个逻辑问题，而不是语法问题。

Property hiddenA As Bool get let
Property hiddenB As Bool get let
Property hiddenC As Bool get let


OptionButton1.Click()
     hiddenA = true
     Hide A row and all rows associated with it

OptionButton2.Click()
     HiddenA = false
     Show A row and all rows associated with it
OptionButton3.Click()
     HiddenB = false
     HiddenC = true
     Show B row and all rows associated with it
     Hide C row and all rows associated with it

OptionButton4.Click()
     HiddenB = true
     HiddenC = false
     Hide B row and all rows associated with it
     Show A row and all rows associated with it



 CheckBox1.Click()
         if CheckBox1.value = false Then
            Hide all "1" rows
         Else
            Show all "1" rows, but keep the "1"s under already hidden letters, hidden. 
            This is   the problem.

诸若此类。也有用于显示/隐藏所有2、3和4行的复选框。

vba

excel

回答 1

Stack Overflow用户

发布于 2014-11-24 16:53:57

粗略的大纲-未测试的代码...类似这样的操作应该会取消隐藏所有内容，检查每个复选框的状态，在范围中添加复选框，并在结束时隐藏整个范围。

'CheckBox1 is Row 1 in group
'CheckBox2 is Row 2 in group
'CheckBox3 is Row 3 in group
'CheckBox4 is Row 4 in group
'CheckBox5 is Row 5 in group
'CheckBox6 is Group A
'CheckBox7 is Group B
'CheckBox8 is Group C
'CheckBox9 is Group D
'CheckBox10 is Group E

Sub CheckBoxClick() 'Assign this to all checkboxes
Application.ScreenUpdating = False 'Turn off screen updating
ActiveSheet.Cells.EntireRow.Hidden = False 'Unhide all

Dim RngCnt As Range
Dim LastRow As Long
Dim CurRow As Long
Dim ChkBx As OLEObject

LastRow = Range("A" & Rows.Count).End(xlUp).Row
    For Each ChkBx In ActiveSheet.OLEObjects
        If TypeName(ChkBx.Object) = "CheckBox" Then
        Select Case ChkBx.Name
            Case "CheckBox1"
                If ChkBx.Value = True Then
                    RngCnt = Union(RngCnt, Range(Rows this chk box effects))
                End If
            Case "CheckBox2"
                If ChkBx.Value = True Then
                    RngCnt = Union(RngCnt, Range(Rows this chk box effects))
                End If
            Case ETC, ETC, ETC to "CheckBox10"
            ...
        End If
    Next ChkBx
    RngCnt.EntireRow.Hidden = True
Application.ScreenUpdating = True
End Sub