Karthik P B
Karthik P B

Reputation: 237

Automatic selection of checkbox in excel VBA

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.

enter image description here

Any help is appreciated!

Upvotes: 0

Views: 1328

Answers (1)

MGP
MGP

Reputation: 2551

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.

Upvotes: 1

Related Questions