Copying a Check Box to other cells

[LPONG0413]

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

[#2]

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.

[#3]

@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

[#4]

@mathetes 

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?

[#5]

@Branka Bradule 

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.

[#6]

@Branka Bradule 

I enlarged the cell and was able to drag the cell down! 

Thank you very much!

[#7]

@LPONG0413 

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.

[#8]

@LPONG0413 Great news! 

[#9]

@LPONG0413 

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.

[#10]

@LPONG0413 

Notice @SergeiBaklan 's comment! You still need to link each and every box to a cell 4999 times after copying it. The following macro could help out here. It creates 5000 checkboxes with a text label "YES", each linking to its own cell in column B. In this particular example the size of each box is 120 pixels wide by 30 pixels high. The first one will be put at coordinate (0, 0) i.e. top left corner of the sheet. Each following box will be placed 30 pixels below the previous one. If you set the row height to 30 for all rows, it'll work nicely. Of course, you may change the parameters to meet your own needs.

Sub Macro1()

Dim NextLoc, i As Integer
NextLoc = 0

For i = 1 To 5000

    ActiveSheet.CheckBoxes.Add(0, NextLoc, 120, 30).Select
    With Selection
        .Value = xlOff
        .Characters.Text = "YES"
        .LinkedCell = "$B$" & i
        .Display3DShading = False
    End With
    
    NextLoc = NextLoc + 30
    
Next i
    
End Sub

[#11]

@mathetes 

My apologies for late reaction. 

I'm doing a data base table where a column for Y or N can serve the purpose but decided to use checkbox instead coz it looks better, and a click is more convenient than typing (for the user). I appreciate your insights!

[#12]

@Riny_van_Eekelen 

I will keep this macro for future reference.

Thank you very much for educating me. Hope you don't get tired of helping me.

[#13]

@Riny_van_Eekelen 

Dear sir, for the uses of my questinnaire , I need a second collumn of checkboxes (the questionnaire has two possible answers : APPLY /DOES NOT APPLY. could you please add the second collumn to the macro  ? THANK YOU FOR YOUR TIME 

[#14]

@alexpantz67 Sorry, but this is a very old post and since then the macro doesn't seem to work as intended on my system anymore. Furthermore, I've stopped using VBA and wouldn't know how to fix it.

[#15]

@LPONG0413 

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