Add checkbox to cell where cell equals yes

[TaskMaster]

Hi all,

I currently have to highlight all of my cells which contain the word Yes and use the following to add in a checkbox. Is there a way to automate this so rather than selecting the range it just does it where the cell is Yes?

Sub InsertCheckboxes()

    Dim c As Range

    For Each c In Selection
        Dim CB As CheckBox
        Set CB = ActiveSheet.CheckBoxes.Add(c.Left, _
                                    c.Top, _
                                    c.Width, _
                                    c.Height)
        With CB
            .Caption = ""
           ' .Value = xlOff
           ' .LinkedCell = c.Address
            .Display3DShading = False
        End With
    Next
End Sub

 

[igold]

You are going to have to tell Excel where to look on your sheet to find the "Yes". Whether you do it in the code or manually. It is not practical to have Excel search the entire sheet.
Something like this (using your code) will look at Range("E1:E20") for the word "Yes", delete that text ("Yes") and replace it with a checkbox that is checked or true.

Sub InsertCheckboxes()

    Dim c As Range
    Dim CB As CheckBox
    
    For Each c In Range("E1:E20")
        If c.Value = "Yes" Then
            c.Value = ""
            Set CB = ActiveSheet.CheckBoxes.Add(c.Left, _
                c.Top, _
                c.Width, _
                c.Height)
            With CB
                .Caption = ""
                .Value = True
                '.LinkedCell = c.Address
                .Display3DShading = False
            End With
        End If
    Next
    
End Sub