CODEWITHSUNDEEP
excelvbacheckbox
Joe
Joe

Reputation: 13

Excel VBA Form Checkbox

Hey I am new to Excel VBA and need someone's help. Following is what I am looking for:

If a certain unlinked (form) checkbox(topleftcell?) is checked Then certain checkboxes below it(.offset?) will be checked off (if they are not already)

I can't use cell names since the same code above will apply to bunch of columns.

This is what I have sort of

Set aaa = ActiveSheet.CheckBoxes(Application.Caller) 
With aaa.TopLeftCell 
    If aaa.Value = 1 Then 
        rsp = MsgBox("Check boxes below?", 4)
        If rsp = vbYes Then
             certain checkboxes(.offset?) below will be unchecked &
             .offset(0,0).value= "na"

Upvotes: 1

Views: 5060

Answers (1)

Siddharth Rout
Siddharth Rout

Reputation: 149277

Let's say that your checkboxes are placed as shown in the image below. I deliberately didn't align them.

enter image description here

Ok here is a small trick that we will use. Right click on all checkboxes in column 1 one by one and click on Format Control. Next go to the Alt Text tab and type 1. For Column 2 checkboxes, type 2 in the Alt text. Repeat this for all 15 columns.

enter image description here

Now assign the below macro to all the top checkboxes.

Sub CheckBox_Click()
    Dim shp As Shape, MainShp As Shape
    Dim Alttext As String

    '~~> Get the checkbox which called the macro
    Set MainShp = ActiveSheet.Shapes(Application.Caller)
    '~~> Get it's alternate text
    Alttext = MainShp.AlternativeText

    '~~> Loop through all checkboxes except the checkbox which called it
    '~~> and check for their alt text
    For Each shp In ActiveSheet.Shapes
        If shp.Name <> MainShp.Name And shp.AlternativeText = Alttext Then
            '~~> If the top checkbox is checked then set the rest as checked
            If MainShp.OLEFormat.Object.Value = 1 Then
                shp.OLEFormat.Object.Value = 1
            Else
                shp.OLEFormat.Object.Value = 0
            End If
        End If
    Next
End Sub

Now when you click the top most checkbox then it will check all the checkboxes below which have the same Alt text as the top checkbox.

Upvotes: 0

Related Questions