Excel VBA script to insert multiple checkboxes linked to cell with yes and no instead of true and false

Question

I am working on a massive excel file were I need to insert multiple cells with checkboxes and I need those boxes attached to the cell they appear over and I need the output to say "Cleared" or "" verses currently they say "True" or "False". So far I have the following code to mass produce the cells but now I need to tweak this code to change the output to say "Cleared" or "" verses "True" or "False".

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("A1:A1000")

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

Can anyone help me figure this out?

you can use conditional formatting and/or custom format superuser.com/questions/893412/… — Slai
– Slai, Commented Sep 10, 2016 at 21:49
I'm going to have multiple checkbox columns that will require different different True/False values. For example in column A I need the caption to say "yes" or "no" instead of True/False but in Column B I need the caption to say "Cleared" or "" (aka blank) instead of True/False. So conditional formatting can't be used in this case. — ReigningData
– ReigningData, Commented Sep 10, 2016 at 22:24
@ReigningData I don't see why not. Each cell can have different format and conditional format(s), so you can have separate conditional formats for each column. — Slai
– Slai, Commented Sep 10, 2016 at 23:50

user3598756 · Accepted Answer · 2016-09-11 08:44:59Z

2

you could adopt a Shapes approach like follows:

Option Explicit

Sub AddCheckBoxes()           
    With Sheets("Sheet1")
        AddRangeCheckBoxes .Range("A1:A2"), "|YES\NO"
        AddRangeCheckBoxes .Range("B1:B2"), "|Cleared\"
    End With
End Sub

Sub AddRangeCheckBoxes(rng As Range, outputs As String)
    Dim cel As Range

    With rng.Parent
        For Each cel In rng
            With .Shapes.AddFormControl(xlCheckBox, cel.Left, cel.Top, 30, 6)
                .TextFrame.Characters.Text = ""
                .AlternativeText = cel.Address(False, False) & outputs
                .OnAction = "UpdateCheckBox"
            End With
        Next cel
    End With
End Sub

Sub UpdateCheckBox()
    Dim cellAddr As String
    Dim val As String

    With Worksheets("Sheet1")
        With .Shapes(Application.Caller)
            cellAddr = Split(.AlternativeText, "|")(0)
            val = Split(Split(.AlternativeText, "|")(1), "\")(IIf(.OLEFormat.Object.Value = 1, 0, 1))
        End With
        .Range(cellAddr).Value = val
    End With
End Sub

answered Sep 11, 2016 at 8:44

user3598756

29.5k4 gold badges20 silver badges30 bronze badges

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

5 Comments

user6432984 Over a year ago

I feel like I plagiarized your answer. I think that we would have both posted at the same time if by I've been playing around with ScreenToGif.

user3598756 Over a year ago

@ThomasInzina my solution is more general in that you can assign different output pairs to different ranges checkboxes by means of AlternativeText property of Shape object without having to write as many UpdateCheckBox() subs. My first (and much earlier) solution was the same as yours but I felt unsatisfied it just would work for only one fixed output pair different than the default "False/True" one. So I played around for quite some time to find a way and tag different output values to different checkboxes, Seems like the OP's wasn't interested in such a more general solution

user6432984 Over a year ago

Hey sorry that I forgot to give you the +1. I like you solution but mine is actually easier to modify because you can do it after the controls are created. If you want, I can append an alternate sub that will mimic your output. So that you can compare the two methods.

user3598756 Over a year ago

@ThomasInzina, I didn't get what you mean by "because you can do it after the controls are created". While I do think that throwing in different/comparative solutions is always a good thing, so please go on!

user3598756 Over a year ago

@ThomasInzina, your alternate solution works indeed. The main difference from mine code is that yours would split the "output pair" assigning from the corresponding range definition, thus making it less intuitive and friendly on "coder" side and not that OOP oriented, in that it mixes up the "OnAction" code with the user's choices. While you see I kept UpdateCheckBox (and AddRangeCheckBoxes, too) user-independent. Of course, that's my way of intending OOP coding

score 1 · Accepted Answer · 2016-09-15 10:28:52Z

1

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("A1:A1000")

    For Each cel In myRange

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

        With cb
            .Caption = ""
            .OnAction = "ProcessCheckBox"
        End With

    Next

End Sub

Sub ProcessCheckBox()
    Dim cb As CheckBox
    With Sheets("Sheet1")
        Set cb = .CheckBoxes(Application.Caller)
        If Not cb Is Nothing Then cb.TopLeftCell = IIf(cb.Value = 1, "Cleared", "")
    End With
End Sub

Important: The ProcessCheckBox() module has to be in a standard module. If it is a worksheet module you will receive this message:

If you to make the code more flexible you can use the combo-box's index or name in a Select Case statement to decide on what your final output will be.

Sub ProcessCheckBox()
    Dim cb As CheckBox

    With Sheets("Sheet1")

        Set cb = .CheckBoxes(Application.Caller)
        If Not cb Is Nothing Then

            Select Case cb.Index
            Case 1, 2, 4
                cb.TopLeftCell = IIf(cb.Value = 1, "Cleared", "")
            Case 3, 5, 7
                cb.TopLeftCell = IIf(cb.Value = 1, 1, 0)
            Case Else
                cb.TopLeftCell = IIf(cb.Value = 1, True, False)
            End Select

        End If

    End With
End Sub

edited Sep 15, 2016 at 10:28

answered Sep 11, 2016 at 9:10

user6432984

2 Comments

ReigningData Over a year ago

The ProcessCheckBox macro is generating an error message "Cannot run the macro 'Book1!ProcessCheckBox'. The macro may not be available in this workbook or all macros may be disabled." I tried going into Options -> Trust Center -> Trust Center Settings -> Macro Setting -> and selected Trust access to the VBA project object model and Enable all macros (not recommended; potentially dangerous code can run) Any suggestions?

user6432984 Over a year ago

I'm happy that I could help!

vacip · Accepted Answer · 2016-09-10 23:33:28Z

Hide the column with the true/false, and insert another column next to it with an IF formula referencing the true/false (=IF(B1,"Cleared","Not cleared"))

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("A1:A1000")

For Each cel In myRange

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


    With cb

        .Caption = ""
        .LinkedCell = cel.Address

    End With

    cel.Offset(0, 1).FormulaR1C1 = "=IF(RC[-1],""Cleared"",""Not cleared"")"

Next

wks.Range("A:A").EntireColumn.Hidden = True

End Sub

You might want to adjust column widths and text alignments as the boxes now overlap with the text.

Collectives™ on Stack Overflow

Excel VBA script to insert multiple checkboxes linked to cell with yes and no instead of true and false

3 Answers 3

5 Comments

2 Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

5 Comments

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related