Forum Discussion
Megan800
Aug 28, 2023Copper Contributor
Custom function in Excel
Can someone please help me develop a function or macro? I need help with copy and paste, as in copy will paste a check box insert that will register what cell it's pasted to. Including the conditional formatting that is attached to it. Example if I copy and paste G3 to G4, the pasted G4 cell will register in that cell and not be attached to G3 anymore. As well with the conditional formatting which registers which color to fill the cell.
- NikolinoDEGold Contributor
Creating a custom function or macro in Excel to achieve this kind of functionality is quite complex, as Excel's built-in features are limited in handling objects like checkboxes dynamically. However, you can use VBA (Visual Basic for Applications) to create a macro that performs the desired copy, paste, and tracking of checkboxes.
Here is a simplified VBA example to get you started. This code will copy a checkbox from one cell to another and adjust its position, then it will track the location of the checkbox:
vbaCopy code
Sub CopyPasteCheckBox() 'This code is only a examble,you may need to adjust this. Dim sourceCell As Range Dim targetCell As Range Dim cb As CheckBox Dim newCB As CheckBox ' Set the source and target cells Set sourceCell = Worksheets("Sheet1").Range("G3") Set targetCell = Worksheets("Sheet1").Range("G4") ' Copy the checkbox Set cb = sourceCell.CheckBoxes(1) cb.Copy ' Paste the checkbox to the target cell targetCell.PasteSpecial ' Create a new checkbox object in the target cell Set newCB = targetCell.CheckBoxes(1) ' Assign a macro to the new checkbox (optional) newCB.OnAction = "CheckBoxClick" ' Store information about the checkbox location newCB.LinkedCell = targetCell.Address ' Optional: Copy conditional formatting from source to target cell sourceCell.Copy targetCell.PasteSpecial Paste:=xlPasteFormats Application.CutCopyMode = False End Sub
In this code:
- CopyPasteCheckBox is the macro that you can run.
- sourceCell and targetCell are the source and target cells where you want to copy and paste the checkbox.
- The checkbox is copied from the source cell and pasted into the target cell.
- A new checkbox object is created in the target cell.
- The LinkedCell property is used to store the location of the checkbox.
You can customize and expand upon this code to suit your specific needs, such as handling multiple checkboxes and conditional formatting. You may also want to create additional macros to handle checkbox clicks or other actions.
Please note that working with checkboxes and their properties in VBA can be more complex if your Excel workbook has multiple worksheets or if you have a specific layout. Make sure to test and adapt the code to your specific Excel setup and requirements. The text, steps and the code were created with the help of AI.
My answers are voluntary and without guarantee!