How to Copy a Checkbox and Update Its Linked Cell Using VBA in WPS Office
Question details
The user needs a VBA procedure to duplicate an Excel form-control checkbox from one worksheet cell to another, update the new checkbox's linked cell, and copy the original cell's conditional formatting in WPS Office Spreadsheets.
- Product
- WPS Office Spreadsheets
- Device & OS
- not provided
- Scenario
- Maintaining a macro-enabled worksheet where a form-control checkbox must be duplicated to a new cell without losing its linked-cell behavior or conditional formatting.
- Observed behavior
- The desired result is a copied checkbox that is linked to the target cell, runs the assigned OnAction macro, and carries over conditional formatting from the source cell.
Before running the macro, confirm that your worksheet uses form-control checkboxes rather than ActiveX checkboxes. Note the exact worksheet name and the source and target cell addresses—for example, Sheet1!G3 and Sheet1!G4—and adjust the VBA code accordingly.
Use VBA to Copy an Excel Form-Control Checkbox and Update Its Linked Cell in WPS Spreadsheets
Use this solution when you need to duplicate a form-control checkbox from one worksheet cell to another, update the new checkbox's linked cell, and keep the source cell's conditional formatting with a single VBA macro.
This macro assumes the worksheet uses form-control checkboxes, which are commonly used in Excel-compatible WPS workbooks. If your workbook uses ActiveX checkboxes, adapt the object type and properties accordingly.
In WPS Spreadsheets, press Alt+F11, or click the Developer tab and then click Visual Basic to open the Microsoft Visual Basic for Applications editor.
Right-click the workbook project in the Project Explorer, choose Insert > Module, paste the following code, and press F5 to run it: Sub CopyPasteCheckBox() Dim sourceCell As Range Dim targetCell As Range Dim cb As CheckBox Dim newCB As CheckBox Set sourceCell = Worksheets("Sheet1").Range("G3") Set targetCell = Worksheets("Sheet1").Range("G4") Set cb = sourceCell.CheckBoxes(1) cb.Copy targetCell.PasteSpecial Set newCB = targetCell.CheckBoxes(1) newCB.OnAction = "CheckBoxClick" newCB.LinkedCell = targetCell.Address sourceCell.Copy targetCell.PasteSpecial Paste:=xlPasteFormats Application.CutCopyMode = False End Sub Change the worksheet name, cell references, and OnAction macro name to match your workbook.
Return to the worksheet and click the checkbox in the target cell. Confirm that it selects correctly, that its linked cell is the target cell, and that conditional formatting was copied from the source cell.
Copy a Checkbox and Update Its Linked Cell with WPS Office VBA
WPS Office Spreadsheets includes a VBA editor, so you can copy a form-control checkbox, reassign its linked cell, and preserve conditional formatting directly in WPS Office—while staying compatible with Excel .xlsx and .xlsm workbook formats.
- 1. Enable the Developer tab: In WPS Spreadsheets, click the Menu button, go to Options > Customize Ribbon, and enable the Developer tab to access form controls and Visual Basic.
- 2. Insert a form-control checkbox: On the Developer tab, click Insert, select Check Box (Form Control), and draw the checkbox in the worksheet. This is the checkbox type used by the VBA example.
- 3. Open the VBA editor and add the macro: Press Alt+F11 or click Developer > Visual Basic, right-click the project, choose Insert > Module, and paste the CopyPasteCheckBox code.
- 4. Run the macro and verify the result: Place the cursor inside the Sub CopyPasteCheckBox code and press F5. Check that the new checkbox uses the target cell as its linked cell and that formatting was copied.
- 5. Save as a macro-enabled workbook: Save the file as an .xlsm or WPS-compatible macro-enabled format so the checkbox macro remains available the next time you open it.
Frequently Asked Questions
Does this VBA code work for ActiveX checkboxes?
No, the example is for form-control checkboxes. If you use ActiveX checkboxes, open the VBA editor with Alt+F11 and adapt the code to use OLEObject and its Object.LinkedCell property instead of CheckBoxes.
Why is my copied checkbox still linked to the original cell?
You must set the new checkbox's LinkedCell property after pasting. In VBA, after targetCell.PasteSpecial, use Set newCB = targetCell.CheckBoxes(1) and then newCB.LinkedCell = targetCell.Address.
How do I open the VBA editor in WPS Spreadsheets?
Press Alt+F11, or enable the Developer tab from Menu > Options > Customize Ribbon and then click Developer > Visual Basic.
Can I preserve conditional formatting when copying a checkbox?
Yes. The macro includes sourceCell.Copy and targetCell.PasteSpecial Paste:=xlPasteFormats, which copies the formatting from the source cell to the target cell after the checkbox is pasted.
How do I run the macro after pasting the code?
Click inside the Sub CopyPasteCheckBox() block and press F5, or select Run > Run Sub/UserForm in the VBA editor.
