Which is the best Check Box to use (and how do I use it?)

[JamesKM]

I have searched 70 of the 159 topics under "Check box" and they seem to be over the level I need (and Help is being the proverbial chocoalte teapot as usual)

I have an Excel sheet and want to put in a simple check/tick box on 60 rows. Which should I use - Control or Forms? I am totallly new to check boxes, have been playing with them for the last 1/2 hour or so and getting nowhere. (Thankfully I have ASAP utilities to delete all failed attempts!)

Once I have the check box in say column D, how can I automatically update column E with simple true or false text?

[dk]

Hi,

If you just want to return a TRUE or FALSE then you'd be fine with the Forms checkbox. Right click it, choose Format Control and set the Cell Link to the cell you want TRUE or FALSE return to.

[JamesKM]

Dan, that works great for one cell, but do I have to manually re-enter the cell reference for each check box? I have tried fill down, but they all link to the same cell.

[dk]

No, you can use a macro to set the linked cells. Say your checkboxes are in cells D1 to D60 you could use something like this to speed up the task.

Sub UpdateLinks()
Dim lngRow As Long

For lngRow = 1 To 60
    Sheets("Sheet1").CheckBoxes(lngRow).LinkedCell = "E" & lngRow - 1
Next lngRow

End Sub

[JamesKM]

Dan,
Thaks again, but i get Run Time error '9' - Subscript out of range.

I tried to record a couple of the allocations and got this:
Sub links()
ActiveSheet.Shapes("Check Box 430").Select
With Selection
.Value = xlOff
.LinkedCell = "J2"
.Display3DShading = False
End With
ActiveSheet.Shapes("Check Box 451").Select
With Selection
.Value = xlOff
.LinkedCell = "j3"
.Display3DShading = False
End With
End Sub

You can see by the check box numbers how many attempts I have made. Is this the problem?

[dk]

Hi,

Yes, the reason the code failed is because it expected to find CheckBox1, CheckBox2, etc. Try this instead, remembering to change the sheet name to match yours.

Sub UpdateLinks()
    Dim chk As CheckBox
    For Each chk In Sheets("sheet1").CheckBoxes
        chk.LinkedCell = "E" & chk.BottomRightCell.Row
    Next chk
End Sub