How to copy a table including its checkboxes from one worksheet to another

Hello all,

I have created a userform that populates a preexisting table on a worksheet. This table contains ActiveX checkboxes in various locations. When the user submits the userform, the data is transferred to the table, including whether to "check" the checkboxes or leave them blank. That portion works properly. The vba code for the userform then needs to copy the completed table to another worksheet. This is where I am running into issues. The code is a simple row copy and paste at the bottom of the second worksheet:

Please Login or Register  to view this content.

But this does not copy or paste the various checkboxes. Do I need to link the checkboxes to a specific cell for them to be copied? If so, how do I do that? If it helps to have an example, one checkbox is named CenterfireCheckBox and is located roughly over cell W7. Its location in the original table is Top = 117, Left = 220.5.

Any thoughts would be greatly appreciated!

Why do you have ActiveX checkboxes? Do they do anything when you check\uncheck them? If the checkboxes had code and you did manage to copy the checkboxes to another sheet, it wouldn't copy their code?

Alternatively, use Form-type checkboxes and the code below...

Please Login or Register  to view this content.

Originally Posted by jrooney7

One of the checkboxes has a bit of code attached to the submission of the userform (what happens when the user clicks the submit command button):

IDK. I can't tell from your limited description and code snippet what you're attempting to accomplish.

Again, I don't know what you are trying to do, but you can use form control checkboxes. You wouldn't get code for them by recording a macro.

Put this macro in a standard code module e.g.; Module1, and assign this macro to a checkbox (right-click on the checkbox and select Assign macro).

Please Login or Register  to view this content.

Attach a sample workbook (not a picture or pasted copy). Make sure there is just enough data to demonstrate your need. Include a BEFORE sheet and an AFTER sheet in the workbook if needed to show the process you're trying to complete or automate. Make sure your desired results are shown, mock them up manually if necessary.

Remember to desensitize the data.

Click on GO ADVANCED and then scroll down to Manage Attachments to open the upload window.

None of the ActiveX checkboxes on sheet CopyCC have code. Meaning, when the user checks\unchecks one of them on the worksheet, no code is executed. This makes things easier. You could replace all the ActiveX checkboxes with Form-type checkboxes.

So now how do you reference form-type checkboxes in the code; Here are a couple of ways

Note: Form-type checkbox values are xlOn or xlOff. Not True or False.

By Name
Sheets("CopyCC").Checkboxes("Check Box 1").Value = xlOn

By index number:
If Sheet46.Checkboxes(1).Value = xlOff Then Sheet46.Rows("16:17").Delete

You could also link a cell to a Form-type checkbox and read the linked cell value or change the linked cell value. To link a cell, right-click on the checkbox and select Format Control on the pop-up context menu. Then select the Control tab on the dialog.

If you are copying multiple tables to CopyCC2. The code will have to use offsets to know which linked cell goes to a given checkbox of a given copy of the table.