Checking Value of Checkbox and setting value for all other Checkboxes accordingly

Question

I am trying to create something like a "Mastercheckbox" that will automatically check all other checkboxes on my worksheet. So I'm trying to start the following VBA Code as soon as I click click on the "Mastercheckbox" I am using Form Tools rather than Active X to create my checkboxes in order to make sure my VBA is compatible with any machine (I read that Active X might cause some issues) and have got the following code:

Sub Mastercheckbox()

Dim Chk As CheckBox

With ActiveSheet.Shapes("Mastercheckbox")
    If .ControlFormat.Value = False Or .ControlFormat.Value = True Then
        If .ControlFormat.Value = False Then
            For Each chk In ActiveSheet.CheckBoxes
                If Not chk.Name = "Mastercheckbox" Then
                chk.Value = False
                End If
            Next chk
        End If

        If Not .ControlFormat.Value = True Then
            For Each chk In ActiveSheet.CheckBoxes
                If Not chk.Name = "Mastercheckbox" Then
                chk.Value = True
                End If
            Next chk
        End If
     Else: MsgBox ("couldn't check value of mastercheckbox")
     End If
End With
End Sub

Everything in the For Loops works, but checking the value of the Mastercheckbox just isn't working for some reason and it jumps straight to the Else case. Can anyone help me out?

user3598756 · Accepted Answer · 2017-03-25 21:50:04Z

3

being not an ActiveX control you must check its value against xlOn(corresponding to 1) and xlOff `(corresponding to -4146)

you may also refactor a little your code as follows:

Option Explicit

Sub Mastercheckbox()        
    With ActiveSheet.Shapes("Mastercheckbox")
        If .ControlFormat.Value = xlNone Then
            MsgBox ("couldn't check value of mastercheckbox")
        Else
            HandleCheckBoxes .ControlFormat.Value
        End If
    End With
End Sub

Sub HandleCheckBoxes(val As Long)
    Dim Chk As CheckBox

    For Each Chk In ActiveSheet.CheckBoxes
        If Not Chk.name = "Mastercheckbox" Then Chk.Value = val
    Next Chk
End Sub

edited Mar 25, 2017 at 21:50

answered Mar 25, 2017 at 13:39

user3598756

29.5k4 gold badges20 silver badges30 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

user3598756 Over a year ago

You are welcome. See my edit to shorten the code a little

Shai Rado · Accepted Answer · 2017-03-25 13:52:18Z

Since you have decided to use User_Form checkbox, you need to define your variables as Shape. And in order to check their value, you need to check if they are xlOn or xlOff.

In order to see that you read the value of "Mastercheckbox" once it's pressed, you can use the piece of code below:

Dim MasterCB As Shape

Set MasterCB = ActiveSheet.Shapes("Mastercheckbox") '<-- set the MasterCB  variable

If MasterCB.ControlFormat.Value = xlOn Then
    MsgBox "Is checked"
ElseIf MasterCB.ControlFormat.Value = xlOff Then
    MsgBox "Not checked"
End If

Code for your post

Option Explicit

Sub Mastercheckbox()

Dim Chk As Shape
Dim MasterCB As Shape

' set the MasterCB  variable
Set MasterCB = ActiveSheet.Shapes("Mastercheckbox")

' loop through all shapes in ActiveSheet
For Each Chk In ActiveSheet.Shapes
    If Chk.Type = msoFormControl Then '<-- check your shape type is User_Form type
        If Chk.FormControlType = xlCheckBox Then '<-- check if the shape is a checkbox
            If Chk.Name <> "Mastercheckbox" Then 
                Chk.ControlFormat.Value = MasterCB.ControlFormat.Value '<-- modify all checkboxes to the value of "Mastercheckbox"
            End If
        End If
    End If
Next Chk

End Sub

Collectives™ on Stack Overflow

Checking Value of Checkbox and setting value for all other Checkboxes accordingly

2 Answers 2

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related