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 http://superuser.com/questions/893412/how-to-get-text-instead-of-truefalse-in-excel — Slai, Sep 10 '16 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, Sep 10 '16 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, Sep 10 '16 at 23:50

score 2 · Answer 1 · answered Sep 11 '16 at 08:44

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 '16 at 08:44

user3598756

28,893
4
18
28

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](http://screentogif.codeplex.com/). – Sep 11 '16 at 09:14
@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 – user3598756 Sep 15 '16 at 09:55
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. – Sep 15 '16 at 10:15
@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 Sep 15 '16 at 10:19
@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 – user3598756 Sep 15 '16 at 10:56

score 1 · Accepted Answer · 2016-09-15T10:28:52.567

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

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? — ReigningData, Sep 13 '16 at 17:44

score 0 · Answer 3 · answered Sep 10 '16 at 23:33

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.

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

3 Answers3

Linked