excel - 保存工作簿时删除复选框

Question

B 列包含复选框。我的数据以表格形式组织。我注意到如果我过滤数据然后关闭并保存文件，隐藏的行中的复选框将被删除。我添加了一个例程，在工作簿关闭之前从所有工作表中删除过滤器，但它仍然删除复选框。保存工作簿时，复选框似乎被删除。请帮助解决这个问题。

谢谢

我用来删除过滤器的代码(removeAllFilters)

If (ActiveSheet.AutoFilterMode And ActiveSheet.FilterMode) Or ActiveSheet.FilterMode Then
  ActiveSheet.ShowAllData  

在 Workbook_beforeClose 中调用 sub

Private Sub Workbook_BeforeClose(Cancel As Boolean)

    Call removeAllFilters
    Application.OnKey "^m"

End Sub

Answer 1

与 this solution 相关，关闭文件时不会删除复选框:

使用自动过滤器时，框会与隐藏行一起移动(彼此重叠)

要解决此问题，请进行以下更改:

.

替换 中的代码本工作手册 有了这个:

Option Explicit

Private Sub Workbook_Open()
    Application.OnKey "^m", "LoadForm"
    setSheets
End Sub

Private Sub Workbook_BeforeClose(Cancel As Boolean)
    Application.OnKey "^m"
    removeAutoFilter
    ThisWorkbook.Save
End Sub

.

在 VBA 模块中 模组报告 添加2个子:

Public Sub removeAutoFilter()
    Dim ws As Worksheet, lRow As Range, cRow As Long, thisWs As Worksheet, cbN As String
    Dim l As Double, r As Double, aSet As Byte, s1 As Boolean, s2 As Boolean
    Dim tbl As ListObject, done As Boolean, isUsed As Boolean

    Application.ScreenUpdating = False: setSheets

    For Each ws In ThisWorkbook.Worksheets
        If ws.Name = wsColon.Name Or ws.Name = wsLung.Name Or ws.Name = wsMela.Name Then
            If Not ws.AutoFilter Is Nothing Then
                For Each tbl In ws.ListObjects
                    tbl.Range.AutoFilter: tbl.Range.AutoFilter: done = True
                Next
                If Not done Then ws.UsedRange.AutoFilter: ws.UsedRange.AutoFilter
            End If
            For Each lRow In ws.UsedRange.Rows
                With lRow
                    cRow = .Row:  isUsed = Len(.Cells(1, EMPTY_ROW_CHECK_3).Value2) > 0
                    If (cRow > 2 And isUsed) Or (cRow > 2 And .Cells(1, 3).Value > 0) Then
                        getAlignment .Cells(1, 2), l, r
                        cbN = IIf(cRow < 10, "_0" & cRow, "_" & cRow)
                        If cRow = 3 Then
                            s1 = ws.CheckBoxes("cb" & SET_Name1 & 1 & cbN).Visible
                            s2 = ws.CheckBoxes("cb" & SET_Name1 & 2 & cbN).Visible
                            aSet = IIf(s1, IIf(s2, 3, 1), 2)
                        End If
                        restCB ws, "cb" & SET_Name1 & 1 & cbN, .Top, l - 1
                        restCB ws, "cb" & SET_Name1 & 2 & cbN, .Top, r + 7
                    End If
                End With
            Next
        End If
    Next
    Application.ScreenUpdating = True
    updateSet aSet
End Sub

Private Sub restCB(ByRef ws As Worksheet, cbN As String, t As Double, l As Double)
    Dim cb As Shape
    For Each cb In ws.Shapes
        With cb
            If .Name = cbN Then
                .Visible = True
                .Top = t
                .Left = l
                Exit For
            End If
        End With
    Next
End Sub

它删除所有工作表上的任何过滤器，并将数据重置为 AutoFiltered

确定事件集(1、2 或 3)

取消隐藏所有复选框(两组)

根据当前行

垂直和水平重新对齐它们

重新初始化事件集

关闭前保存文件