如何实现复选框，勾选就显示（列筛选分三类：收入、增长率、收入占比）

阳光暖洋洋

各位大神，表格比较宽，想实现复选框，勾选就显示（筛选分三类：收入、增长率、收入占比），不勾选就隐藏，求代码！拜托

peter199083

附件是我的解决方案。如果使用类模块事件可以大大精简代码量，期待高手的反馈。

Private Sub 收入_Click()
    Dim IsCheck As Boolean
    Dim ob As OLEObject
    Dim cb As MSForms.CheckBox
    For Each ob In ActiveSheet.OLEObjects
        If ob.Name = "收入" Then
            Set cb = ob.Object
        End If
    Next
   
    If cb.Value = True Then
       IsCheck = True
    End If
   
    Dim i As Integer
    For i = 4 To 22
        If Cells(7, i).Value = "收入" Then
            Columns(i).EntireColumn.Hidden = Not IsCheck
        End If
    Next
    Set ob = Nothing
    Set cb = Nothing
End Sub


Private Sub 增长率_Click()
    Dim IsCheck As Boolean
    Dim ob As OLEObject
    Dim cb As MSForms.CheckBox
    For Each ob In ActiveSheet.OLEObjects
        If ob.Name = "增长率" Then
            Set cb = ob.Object
        End If
    Next
   
    If cb.Value = True Then
        IsCheck = True
    End If
   
    Dim i As Integer
    For i = 4 To 22
        If Cells(7, i).Value = "增长率" Then
            Columns(i).EntireColumn.Hidden = Not IsCheck
        End If
    Next
    Set ob = Nothing
    Set cb = Nothing
End Sub


Private Sub 占比_Click()
    Dim IsCheck As Boolean
    Dim ob As OLEObject
    Dim cb As MSForms.CheckBox
    For Each ob In ActiveSheet.OLEObjects
        If ob.Name = "占比" Then
            Set cb = ob.Object
        End If
    Next
   
    If cb.Value = True Then
        IsCheck = True
    End If
   
    Dim i As Integer
    For i = 4 To 22
        If Cells(7, i).Value = "占比" Then
            Columns(i).EntireColumn.Hidden = Not IsCheck
        End If
    Next
    Set ob = Nothing
    Set cb = Nothing
End Sub

xunanming

要求只是一项隐藏还是其中两项有同时隐藏的需要????

xunanming

请参考------------------------------

zhjlgaojuan

sub hidd()
for i=4 to 22
select case cells(7,i).value
case "收入"
hid=收入.value
case "增长率“
hid=增长率.value
case "占比"
hid=占比.value
case else
hid=false
end select
columns(i).hidden=hid
next
end sub

每个checkbox的click事件都调用hidd过程

MissMist

1. Private Sub 收入_Click()
   
2.         ShowField "收入", Not 收入.Value
   
3. End Sub
   
4. 
   
5. Private Sub 收入占比_Click()
   
6.         ShowField "占比", Not 收入占比.Value
   
7. End Sub
   
8. 
   
9. Private Sub 增长率_Click()
   
10.         ShowField "增长率", Not 增长率.Value
    
11. End Sub
    
12. 
    
13. Private Sub ShowField(ByVal ShowWhat As String, ByVal ShowOrHidden As Boolean)
    
14.       Dim ActWst As Worksheet, nWidth As Long
    
15.       Set ActWst = ActiveSheet
    
16.       nWidth = ActWst.Range("a6").CurrentRegion.Columns.Count - 1
    
17.       For i = 4 To nWidth
    
18.           If ActWst.Cells(7, i) = ShowWhat Then
    
19.              ActWst.Columns(i).Hidden = ShowOrHidden
    
20.           End If
    
21.       Next
    
22. End Sub

复制代码

taller

peter199083

使用类模块同样可以满足需求，且大大缩小代码量。
在类模块中建立名为clsHide的类，并写入如下代码

Public WithEvents m_cb As MSForms.CheckBox

Private Sub m_cb_Click()
    Dim IsCheck As Boolean
    IsCheck = m_cb.Value
   
    Dim i As Integer
    For i = 4 To 22
        If Cells(7, i).Value = m_cb.Caption Then
            Sheets("表2-1主营业务收入").Columns(i).EntireColumn.Hidden = Not IsCheck
        End If
    Next
End Sub

在ThisWorkbook中写入代码

Dim col As Collection

Private Sub Workbook_Open()
    Call ff
End Sub

Sub ff()
   Dim CB As clsHide
   Dim o As OLEObject
   Dim i As Integer
   Set col = New Collection
   With ActiveSheet
        For i = 1 To .OLEObjects.Count
             Set o = .OLEObjects(i)
             Set CB = New clsHide
             Set CB.m_cb = o.Object
             col.Add CB
        Next
   End With
End Sub

准提部林

改用一般勾選框試試//
Xl0000021.rar (14.76 KB, 下载次数: 13)

2023-4-11 15:14 上传

点击文件名下载附件

阳光暖洋洋

xunanming 发表于 2023-4-11 00:14
请参考------------------------------

采用了，很赞，代码比较简洁