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

JamesKM

Board Regular
Joined
Aug 12, 2002
Messages
130
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?
 

Some videos you may like

Excel Facts

Formula for Yesterday
Name Manager, New Name. Yesterday =TODAY()-1. OK. Then, use =YESTERDAY in any cell. Tomorrow could be =TODAY()+1.

dk

MrExcel MVP
Joined
Feb 15, 2002
Messages
2,942
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

Board Regular
Joined
Aug 12, 2002
Messages
130
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

MrExcel MVP
Joined
Feb 15, 2002
Messages
2,942
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.

Code:
Sub UpdateLinks()
Dim lngRow As Long

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

End Sub
 

JamesKM

Board Regular
Joined
Aug 12, 2002
Messages
130
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

MrExcel MVP
Joined
Feb 15, 2002
Messages
2,942
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.

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

Watch MrExcel Video

Forum statistics

Threads
1,122,652
Messages
5,597,373
Members
414,139
Latest member
okela0

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top