lundi 2 octobre 2017

Excel VBA code that sets checkbox when cell behind isn't empty

I'm new here (and beginner in VBA). I tried to modify this code to set checkbox only for cells in Range("B21:B40") which aren't empty.

Code is not ready. I m not sure how to build two for each loops working pararell that one will check content in Range("B21:B25") and next in Range("A21:A25") will set checkboxes

Sub ActiveCheckBox()

Dim setRange As Range, cel As Range
Dim checkRange As Range, cel1 As Range
Dim wks As Worksheet
Dim cb As Checkbox

Set wks = Sheets("InterFace")
Set setRange = wks.Range("A21:A25")
Set checkRange = wks.Range("B21:B25")

For Each cel1 In checkRange
    If cel1 <> "" Then
        For Each setRange In checkRange
            Set cb = cel.Worksheet.CheckBoxes.Add(cel.Left + cel.Width / 2 - 8.25, _
                    cel.Top + cel.Height / 2 - 8.25, 0, 0)  ' 8.25 is cb.Height / 2
                    With cb
                    .Text = vbNullString                      ' to clear Caption
                    .LinkedCell = cel.Address(0, 0)             ' Example A1 instead of $A$1
                    .Name = "cb" & cb.LinkedCell              ' optional
                    End With
        Next
    End If
Next

setRange.NumberFormat = ";;;" ' optional to hide the cell values

End Sub

To build that i used code from this topic Script to Insert a Checkbox into every cell and assign it to that cell in Excel Please let me know what i'm doing wrong.




Aucun commentaire:

Enregistrer un commentaire