Automatic selection of checkbox in excel VBA

Question

I have maintained a excel sheet where in column A there are list of all parameters for a particular product, and again in column D there are few parameters which i require to choose from set of all the parameters in column A.

Is it possible in vba to trigger a click event where it should compare between column A and column D and select the checkboxes automatically if it finds the parameter.

Any help is appreciated!

You can have a look into the Worksheet_Change-Event. Could you maybe describe, why you would want the checkbox to be selected? It would be fairly easy to use a worksheet-formula to output yes/no depending on whether the parameter is part of the parameters name list — MGP
– MGP, Commented Nov 24, 2015 at 6:59
@Macro Getrost - The reason i want automatic checkbox selection is that, the list might grow nearly to 100 or 150 parameters and it takes lot of time for the user to manually compare each and every parameter, hence my making it automatic we can save a significant amount of time — Karthik P B
– Karthik P B, Commented Nov 24, 2015 at 7:15
What I mean is, would it suffice, if you would instead of checking a checkbox have a column, e.g. Column C, where it would just say yes or no depending on whether the parameter is listed in the given names? — MGP
– MGP, Commented Nov 24, 2015 at 7:18
Yes i do agree with your approach, but sometimes there might be a chances that user wants to deselect/select couple of parameters once after the comparison has been done, during those cases mentioning YES or NO will not help. — Karthik P B
– Karthik P B, Commented Nov 24, 2015 at 7:22

MGP · Accepted Answer · 2015-11-24 07:59:31Z

1

Ok What you can do is this:

Put the checkboxes (make sure that they are format control checkboxes) in Column C. (Be sure that the Checkbox is completely in the cell)

Post this in the Worksheetmodul:

Private Sub Worksheet_Change(ByVal Target As Range)

Dim chk As CheckBox
Dim check As Boolean
Dim rng As Range

For Each chk In ActiveSheet.CheckBoxes

    Set rng = Range("D:D").Find(what:=chk.TopLeftCell.Offset(0, -2).Value, _
    LookIn:=xlValues, _
    lookat:=xlWhole, _
    searchorder:=xlByRows, _
    searchdirection:=xlNext, _
    MatchCase:=False)

    If Not rng Is Nothing Then

        chk.Value = True

    End If

Next chk

End Sub

Every time a value is changed in the worksheet, the sub is triggered.

answered Nov 24, 2015 at 7:59

MGP

2,6732 gold badges22 silver badges35 bronze badges

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

2 Comments

MGP Over a year ago

What you could think about is adding a Button, which when clicked runs this sub, since every time someone triggers an event, the checkboxes are checked. So if someone unchecked a few checkboxes and makes a change to a value, then they will be checked again.

Karthik P B Over a year ago

Thanks a ton Marco, this is waht i was expecting. It's working fine.Thanks once again!

Collectives™ on Stack Overflow

Automatic selection of checkbox in excel VBA

1 Answer 1

2 Comments

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Related