CODEWITHSUNDEEP
vbaexcelcheckbox
Kay
Kay

Reputation: 25

Hundreds of checkboxes change from True/False to Yes/No

I have a spreadsheet that is continuously being updated to track visitations to patients. I have 2 columns that I've used the below VBA code to enter multiple checkboxes into which are assigned to the cell into which they are located. However, I would like to add something that makes the value of the checkboxes either Yes or No when I click on them. There are over 600 of these in the spreadsheet, so I need something that will cover all of them at once instead of having to change each one separately. I would post a pic of the spreadsheet, but I can't yet since I'm a new user and don't have reputation points. Can anyone help?

Sub AddCheckBoxes()

Dim cb As CheckBox
Dim myRange As Range, cel As Range
Dim wks As Worksheet

Set wks = Sheets("Sheet1")

Set myRange = wks.Range("K2:K300, L2:L300")

For Each cel In myRange

Set cb = wks.CheckBoxes.Add(cel.Left, cel.Top, 30, 6)


With cb

    .Caption = ""
    .LinkedCell = cel.Address

End With

Next

End Sub

End Sub

Upvotes: 0

Views: 2490

Answers (1)

Davesexcel
Davesexcel

Reputation: 6984

When the code is creating the checkboxes, you can add .OnAction ="MacroName"

 With cb
        .LinkedCell = cel.Address
        .Caption = ""
        .OnAction = "YesNoChkBox"    'will call the macro when the checkbox is clicked.
    End With

What this does is assigns a macro to the checkbox when it is clicked. The macro will then figure out the range of the clicked checkbox and will display "yes" or "no" if it is true or now.

 Sub MakeCkBx()

    Dim cb As CheckBox
    Dim myRange As Range, cel As Range
    Dim wks As Worksheet

    Set wks = Sheets("Sheet1")

    Set myRange = wks.Range("K2:K21, L2:L21")

    For Each cel In myRange

        Set cb = wks.CheckBoxes.Add(cel.Left, cel.Top, 30, 6)

        With cb
            .LinkedCell = cel.Address
            .Caption = ""
            .OnAction = "YesNoChkBox"    'will call the macro when the checkbox is clicked.
        End With
        cel.HorizontalAlignment = xlRight
    Next

End Sub
Sub YesNoChkBox()
    Dim r As Range, x, CkBx As CheckBox
    Set CkBx = ActiveSheet.CheckBoxes(Application.Caller)
    Set r = Range(CkBx.LinkedCell)

    If CkBx = 1 Then
        r = "yes"
    Else
        r = "No"
    End If
End Sub

Run the MakeCkBx macro then click on the checkboxes to get the results.

I have a simple example of application.caller located here

Upvotes: 2

Related Questions