CODEWITHSUNDEEP
excelvba
Андрей Романов
Андрей Романов

Reputation: 77

vba action if checkbox is unchecked

I want to make a sub that inserts a picture if checkbox is checked and deletes it if it's unchecked, this is what i've got so far, and the first part (insert when checked) works fine. Any ideas, please?

Dim chbx
Set chbx = ActiveSheet.CheckBoxes.Add(240, 15, 144, 15.75)
chbx.Characters.Text = "DisplacementPicturesIns"
chbx.OnAction = "DisplacementPicturesIns"
If chbx.Value = True Then
    chbx.OnAction = True
Elseif chbx.Value = False Then
....
End If

Upvotes: 1

Views: 4169

Answers (2)

coma
coma

Reputation: 11

I dunno if you still need this but here is a solution that i made to select/unselect my checkboxes elements. you can change between the if statement to do what ever you want :)

Sub SelectAll()
   Dim chkbx As CheckBox
   Dim cbv As Long

   cbv = Sheets("Feuil1").CheckBoxes(Application.Caller).Value

   If cbv = 1 Then
      For Each chkbx In Sheets("Feuil1").CheckBoxes
         chkbx.Value = xlOn
      Next
   Else
      For Each chkbx In Sheets("Feuil1").CheckBoxes
         chkbx.Value = xlOff
      Next
   End If
End Sub

Upvotes: 1

Banana
Banana

Reputation: 7483

Like it was pointed out in the notes, your code doesnt make much sense. the OnAction holds the name of the method to be called once the checkbox is ticked/unticked. Also, your condition is only being tested once, after the creation of the Checkbox control and thats it. if you want your condition to be tested every time, it must be placed inside the sub that is called on the ticking/unticking event.

My suggestion: give the checkbox a name, and then refer to it inside the sub and check it's value:

Public Sub Example()
    With ActiveSheet.CheckBoxes.Add(240, 15, 144, 15.75)
        .Characters.Text = "DisplacementPicturesIns"
        .Name = "myCheckBox"
        .OnAction = "myCheckBox_Click"
    End With
End Sub
Sub myCheckBox_Click()
    Dim chbx
    Set chbx = ActiveSheet.CheckBoxes("myCheckBox")
    If chbx.Value = 1 Then
        DisplacementPicturesIns
    Else
        'do other stuff 
    End If
End Sub

Upvotes: 0

Related Questions