How to copy checkboxes down a column?

I figured out how to insert a checkbox into cell I4. I'd like to drag/copy the cell all the way down the column, but it doesn't work this way. How can I do that without individually copy/pasting into each cell?

Better yet, is there a way to make a check box appear only if there is content in the row? This is a spread sheet where I add one or two rows per day. For example, if there is content in B25, make a check box appear in I25? That would help keep the spreadsheet clean.

Thanks,

Dan

Hello earthtodan,

Yes it can be done. However, I need to know where the CheckBox came from, Forms tool bar or Control Toolbox. Also are there any macros associated with these check boxes? Any special formatting of font size, color, etc? Do they display any text?

Sincerely,
Leith Ross

I did it using the forms toolbar, although I don't really know the difference. I'll do it whichever way is best. I don't know how to get the checkboxes to "stick" using the control toolbox.
There is no special formatting associated with them or any code. They are in a column titled "Oversold", and they get checked if a project (row) is oversold. They are going to be counted in a COUNT IF TRUE formula.

Dan

Hello Dan,

Here are the macros to add the check boxes into column "I" if the cell in column "B" isn't empty.

Please Login or Register  to view this content.

Adding the Macro
1. Copy the macro above pressing the keys CTRL+C
2. Open your workbook
3. Press the keys ALT+F11 to open the Visual Basic Editor
4. Press the keys ALT+I to activate the Insert menu
5. Press M to insert a Standard Module
6. Paste the code by pressing the keys CTRL+V
7. Make any custom changes to the macro if needed at this time
8. Save the Macro by pressing the keys CTRL+S
9. Press the keys ALT+Q to exit the Editor, and return to Excel.

To Run the Macro...
To run the macro from Excel, open the workbook, and press ALT+F8 to display the Run Macro Dialog. Double Click the macro's name to Run it.

Sincerely,
Leith Ross

Wow, thanks for all that code. I'll test it out as soon as I'm not supposed to be working.

Checkboxes from the Forms toolbox or Control Toolbar are on a different layer of the sheet than cells, so it's easy to mangle their associations. 'Twere I you, I'd use Marlett checkboxes. They are cells with a double-click behavior implemented in about five lines of VBA. A search will turn up many examples.

Originally Posted by shg

Checkboxes from the Forms toolbox or Control Toolbar are on a different layer of the sheet than cells, so it's easy to mangle their associations. 'Twere I you, I'd use Marlett checkboxes. They are cells with a double-click behavior implemented in about five lines of VBA. A search will turn up many examples.

That is awesome! Way cleaner than checkboxes. There is only one bug, I have to make the font in the checkbox cells really small in order for the row not to expand when it's checked (it won't unexpand when it's unchecked). If I could find a way to use 10-font checkboxes without the rows expanding, it would be perfect.

?? -- just set the whole column to a sufficiently small font size.

Originally Posted by shg

?? -- just set the whole column to a sufficiently small font size.

Yes, that's what I did. I have to size it at font 8 to not expand the rows. The check marks show up tiny. They work, but they're small.

You might use the normal font and CHAR(149) "•"

I know this is an old thread but how do you center the checkbox in the cell? I am very new to VBA.

Originally Posted by Leith Ross

Hello Dan,

Here are the macros to add the check boxes into column "I" if the cell in column "B" isn't empty.

Please Login or Register  to view this content.

Adding the Macro
1. Copy the macro above pressing the keys CTRL+C
2. Open your workbook
3. Press the keys ALT+F11 to open the Visual Basic Editor
4. Press the keys ALT+I to activate the Insert menu
5. Press M to insert a Standard Module
6. Paste the code by pressing the keys CTRL+V
7. Make any custom changes to the macro if needed at this time
8. Save the Macro by pressing the keys CTRL+S
9. Press the keys ALT+Q to exit the Editor, and return to Excel.

To Run the Macro...
To run the macro from Excel, open the workbook, and press ALT+F8 to display the Run Macro Dialog. Double Click the macro's name to Run it.

Sincerely,
Leith Ross

This is very helpful but I have a few questions:

Note: All questions are meant to be included in the macro.

How do I make the size wider so the "Text" next to the box will all shows up?

Can the "Cell Link" be included in the Macro to another column?

Is there any way to edit this code so that the check box fill color is yellow until checked. Thanks in advance.

I know this is a very old post, but I am hoping someone can help me. I am very new to VBA and am unable to get this code to work. I copied and pasted it per the instructions, but I am getting the message "Compile error: Expected Function or variable." Does anyone have any ideas for how to fix this? Thank you in advance.