CODEWITHSUNDEEP
vbaexcel
Manas Saha
Manas Saha

Reputation: 1497

Convert an excel cell containing true/false as check box from VBA

I have a column in a excel cell which has either "True" or "false" as text.

The column is populated by a VBA code.

I need to display a check box as checked instead of True, and a check box unchecked instead of False.

Can this be done from VBA code? Suppose the cell containing the value is refered as

ActiveWorkBook.Sheets("mySheetName").cells(i, 4)

i = Row Index, which is inside a loop.

Upvotes: 1

Views: 8874

Answers (2)

Vinny Roe
Vinny Roe

Reputation: 901

You can just leave the cell saying TRUE or FALSE, add the checkbox to your sheet, and set the LinkedCell on the checkbox to be your original cell. If you don't want to see the cell, place the checkbox over the top of it!

Upvotes: 1

Peter Albert
Peter Albert

Reputation: 17475

This code should do the job, just change the For loop according to your needs:

Sub subPlaceCheckbox()

    Const cDblCheckboxWidth As Double = 15
    Const cStrCheckboxPrefix As String = "cb4_"

    Dim cb As CheckBox
    Dim rng As Range
    Dim i As Long

    Application.ScreenUpdating = False

    'First, delete all old checkboxes to avoid doubles
    For Each cb In Sheet1.CheckBoxes
        If Left(cb.Name, Len(cStrCheckboxPrefix)) = _
            cStrCheckboxPrefix Then
            cb.Delete
        End If
    Next

    For i = 1 To 20
        Set rng = Sheet1.Cells(i, 4)

        'Place checkbox
        Set cb = Sheet1.CheckBoxes.Add( _
            rng.Left + rng.Width / 2 - cDblCheckboxWidth / 2, _
            rng.Top, cDblCheckboxWidth, rng.Height)

        'Set checkbox properties
        With cb
            .Name = "cb_" & rng.Address(False, False)
            .Value = rng.Value
            .LinkedCell = rng.Address
            .Display3DShading = True
            .Characters.Text = ""
        End With

        'Hide content of cell
        rng.NumberFormat = ";;;"

    Next i

    Application.ScreenUpdating = True
End Sub

Upvotes: 1

Related Questions