Forum Discussion
Copying a Check Box to other cells
I am doing a sheet where I need a check box in each of the 5000 rows. I would appreciate any help in doing it other than pasting a check box 4999 times.
Thank you!
lpong0413
- Assuming they're all in the same column, does copy...paste not work? All the way down the column. One "Paste" should do it for all 4,999 rows.
I thought that should work but no matter how many cells I highlight to copy it to, only one cell will take it. Yes, they are all in one column. One thing I noticed, when we normally copy something, it creates a dotted line box around the cell that is copied and that tells us the copied cell is ready to be pasted. In the case of this check box, no dotted box appears but I can paste it to other cells down the line without repeating the "copy" command.
Is this a software (ms office - excel) malfunction?
It just occurs to me to ask Presumably this is simply a case where somebody along the lines is answering "Yes" or "No" (in effect) to some question or other. You could--presuming that to be the case--just have a cell with Data Validation set (in the menu, under Data....Validation) to accept only "Y" or "N" as responses.
A check box, on the other hand, USUALLY isn't used in every one of 5,000 rows of a database. Its appropriate use is in an input form, rather than in a database. [Now, I admit, I don't know what you're doing, and it may be entirely appropriate to have the check box, but I thought it appropriate to at least ask.
LPONG0413 if the checkboxes are in the same column, you could just add the checkbox into the first cell of that column and then drag the fill handle down (not the checkbox itself, just the cell).
You will see the names of the checkboxes to appear the same, but the backend name of each object will have a different name.
There is a downside to this approach: if the first checkbox is linked to specific cell, then all other cells will be linked to the same cell and you would need to link each checkbox manually.
Let me know if this helps.
Cheers,
Branka
I tried that too but everytime I tried to select the cell (not the check box) it's like clicking the check box to "check" or "uncheck" it.
When I right-click the cell, the check box is "selected" and that is what I can copy and paste but only to one cell at a time.
You may navigate to the cell under check box with arrows, when press and hold Shift, scroll down till end of your range (other words select it), release Shift and press Ctrl+D.
The only you'll need 4999 times to link appeared check boxes to cells.
LPONG0413 Great news!
check this code I found. simple and effective solution. I have just ran it for 5,000+ lines and crashed Excel... it worked fine for less during testing.
https://www.youtube.com/watch?v=cqq6lS23lGM
VBA CODE:
Sub LinkCheckBoxes() Dim chk As CheckBox Dim lCol As Long lCol = 3 'number of columns to the right of checkbox For Each chk In ActiveSheet.CheckBoxes With chk .LinkedCell = _ .TopLeftCell.Offset(0, lCol).Address End With Next chk End Sub
