CODEWITHSUNDEEP
excelvbacheckbox
user618677
user618677

Reputation: 5069

Excel VBA: How to clear CheckBox

Private Sub CheckBox9_Click()
    If CheckBox9.Value = True Then
        CheckBox9.Caption = "Done"
        ActiveWorkbook.Sheets("Well Planning Checklist").Tab.ColorIndex = 4
        'ActiveSheet.Tab.ColorIndex = 22
        Range("Q17").Value = CheckBox9.Caption
    Else
        If LCase(Range("Q17").Value) = CheckBox9.Caption Then
            CheckBox9.Value = Not (CheckBox9.Value)
        Else
            CheckBox9.Value = Not (CheckBox9.Value)
        End If
    End If
End Sub

I used the above to make sure that once the user clicks checkbox, he cannot uncheck it. However, I would want to be able to have a Button that my user could user to reset . everything and not only the checkbox. I have the below but it is not working . Could someone help me get it to work?

Private Sub CommandButton1_Click()
    CheckBox9

    ActiveWorkbook.Sheets("Well Design Section").CheckBox9.Caption = "Incomplete "
    ActiveWorkbook.Sheets("Well Design Section").CheckBox9.Value = False
    Range("Q17").Value = "Incomplete"
End Sub

Upvotes: 0

Views: 7434

Answers (1)

Daniel
Daniel

Reputation: 13142

To solve the problem, you can replace your CheckBox9_Click sub with this:

Private Sub CheckBox9_Click()
    If CheckBox9.Value = True Then
        CheckBox9.Caption = "Done"
        ActiveWorkbook.Sheets("Well Planning Checklist").Tab.ColorIndex = 4
        'ActiveSheet.Tab.ColorIndex = 22
        Range("Q17").Value = CheckBox9.Caption
    Elseif Checkbox9.Caption <> "Incomplete" Then
        If LCase(Range("Q17").Value) = CheckBox9.Caption Then
            CheckBox9.Value = Not (CheckBox9.Value)
        Else
            CheckBox9.Value = Not (CheckBox9.Value)
        End If
    End If
End Sub

The only difference is your Else statement is replaced with an Elseif. It will now only occur if the Caption <> "Incomplete", which is fine because the initial if will be true if the checkbox is clicked.

Upvotes: 2

Related Questions