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

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

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.

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.

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