VBA Check Boxes

[DanG]

Hello,

I would like to have check boxes (with no label) insterted to the left of any row that contains data in it.
I will get a list and then I want to automatically insert check boxes so the user can check/uncheck the items on the list.

Here is what I haev starting with. Though in this code I am selecting a range, I would prefer the range to be automatically detacted by the presence of data in a column (column "B" for example).

Sub CB()

Dim rcell As Range
Dim sel As Range
Set sel = Application.InputBox(Prompt:="PLEASE USE YOUR MOUSE TO SELECT THE RANGE", Type:=8)

For Each rcell In sel
rcell.Offset(0, -1) = This is where I want to insert a checkbox
Next

End Sub

Thank you

 

[chergh]

Try something like:

dim wb as workbook
dim ws as worksheet

dim rng as range

dim lastRow as long
dim rowHeight as long

set wb = thisworkbook
set ws = wb.worksheets("Sheet1")

lastRow = ws.range("B65536").end(xlup).row

rHeight = ws.range("A1").rowheight

for each rng in ws.range("B1:B" & lastRow)

   if not isempty(rng) then
      set chBX = ws.Shapes.AddFormControl(xlCheckBox, left:=0 , top:= rng.row * rHeight, width:=10, height:=10)
      

      chBX.linkedcell = rng.address(rowabsolute:=false, columnabsolute:=false)

      set chBX = nothing
   end if
next rng

 

[DanG]

Thank you for the feedback,

I was getting an error on a piece of your vba (it would help if I knew what I was doing), so I found something and modified it to work for what I need (kind of a hack job, but it works).

Sub CheckBoxMe()
    Dim ToRow As Long
    Dim LastRow As Long
    Dim MyLeft As Double
    Dim MyTop As Double
    Dim MyHeight As Double
    Dim MyWidth As Double

    '--------------------------
    LastRow = Range("d65536").End(xlUp).Row
    For ToRow = 2 To LastRow
        If Not IsEmpty(Cells(ToRow, "d")) Then
            Cells(ToRow, "c") = "=if(b" & ToRow & "= true," & """No Mailing""," & """Get Mailing"")"
            MyLeft = Cells(ToRow, "a").Left
            MyTop = Cells(ToRow, "a").Top
            MyHeight = Cells(ToRow, "a").Height
            MyWidth = MyHeight = Cells(ToRow, "a").Width
                    '-
            ActiveSheet.CheckBoxes.Add(MyLeft, MyTop, MyWidth, MyHeight).Select
             With Selection
                .Caption = ""
                .Value = xlOff
                .LinkedCell = "b" & ToRow
                .Display3DShading = False
            End With
            
        End If
    Next
End Sub

Thank you again!
I must say, I really thought it would be easier than this.

 

[chergh]

No and you also need to think about how many of these checkboxes yoru going to have. If its going to be a few hundred then your going to really bloat the file size and I've heard that lots of objects like these and comboxes etc can result in errors appearing in your workbooks.

 

[DanG]

Maybe that is why there were not so many solutions out and about