时间戳

首页 > 解决方案 > Excel VBA 复选框 - 运行单击代码时自行检查和取消选中

excel - Excel VBA 复选框 - 运行单击代码时自行检查和取消选中

问题描述

我在 Q 列的受保护工作表的每一行上都有一个复选框。选中或取消选中此复选框将运行下面的代码。

当我选中复选框时，我希望它

当我取消选中复选框时，我希望它

由于某种原因，当我选中复选框（未选中）时，代码运行，并且由于某种原因复选框恢复为未选中状态。如果复选框最初被选中并且我取消选中它，代码运行并且由于某种原因复选框恢复为选中状态，则会发生相反的情况。我的代码没有取消选中或选中复选框。

有人可以帮我弄清楚为什么在我选中或取消选中复选框后复选框会发生变化吗？

Sub PartQuantitiesCheckBox_Click()

    Dim sCheckboxName, sCheckboxValue, sCheckboxChecked, sDS1BuildRange As String
    Dim iCurrentRow As Integer

    sCheckboxName = Application.Caller

    ' If this returns 1 then the checkbox was checked and we clicked it to uncheck it.
    sCheckboxValue = ActiveSheet.Shapes(sCheckboxName).ControlFormat.Value

    If sCheckboxValue = "1" Then
        sCheckboxChecked = "False"
        MsgBox ("sCheckboxChecked = False")
    Else
        sCheckboxChecked = "True"
        MsgBox ("sCheckboxChecked = True")
    End If

    ' The checkbox name is prefaced with "cbPartQtyNeeded" followed by the row number (eg. cbPartQtyNeeded4).
    ' Strip the row number out of the checkbox name.
    iCurrentRow = Mid(sCheckboxName, 16, Len(sCheckboxName) - 15)
    sDS1BuildRange = "R" & iCurrentRow

    ActiveSheet.Unprotect

    If sCheckboxChecked = "False" Then
     
        Range(sDS1BuildRange).Select
        With Selection.Interior
            .Pattern = xlSolid
            .PatternColorIndex = xlAutomatic
            .ThemeColor = xlThemeColorDark1
            .TintAndShade = -0.149998474074526
            .PatternTintAndShade = 0
        End With
    
        Range(sDS1BuildRange).Formula = "=IFERROR(IF($P" & iCurrentRow & "*'Cover Sheet'!$M$8=0,"""",$P" & iCurrentRow & "*'Cover Sheet'!$M$8),"""")"
        Range(sDS1BuildRange).Locked = True

    Else

        Range(sDS1BuildRange).Locked = False
        Range(sDS1BuildRange).Select
        With Selection.Interior
            .Pattern = xlSolid
            .PatternColorIndex = xlAutomatic
            .ThemeColor = xlThemeColorDark1
            .TintAndShade = 0
            .PatternTintAndShade = 0
        End With

        sValue = Range(sDS1BuildRange).Value
        Range(sDS1BuildRange).Formula = ""
        Range(sDS1BuildRange).Value = sValue
         
    End If

    ActiveSheet.Protect DrawingObjects:=True, Contents:=True, Scenarios:=True, AllowFiltering:=True

End Sub

标签： excelvbacheckbox

解决方案

我创建了一个全新的复选框，并用它替换了其他一个复选框。我之前从我创建的第一个复选框中复制了所有复选框，然后将它们重命名。我认为这把他们搞砸了。创建一个新的似乎可以很好地运行代码，而无需重新选中/取消选中该复选框。

推荐阅读