Forum Discussion
Checkbox control dragging in Excel
I am using Excel 365. After inserting a checkbox control in a cell, not able to drag down to fill the below cells with the checkbox. why? The same possible in my other system having Excel 2019. Please advise. I need to get the same feature in Excel 365 too.
In Excel 365, there is currently no built-in feature to automatically fill down checkboxes by dragging like you can with other types of data…so far I know.
However, you can achieve similar functionality by using a workaround.
One approach is to copy and paste the checkboxes to the desired range.
Here's how you can do it:
- Insert a checkbox in the first cell where you want to start.
- Right-click on the checkbox, and choose "Copy."
- Select the range of cells where you want to paste the checkboxes.
- Right-click on the first cell of the range, and choose "Paste."
This will copy the checkbox to all the selected cells.
You can adjust the size and position of the checkboxes as needed.
Alternatively, you can use VBA (Visual Basic for Applications) to automate this process. You can write a VBA script that inserts checkboxes in a specified range of cells.
Here is a basic example of how you can do it:
Vba code is untested, please backup your file first.
Sub InsertCheckboxes() Dim rng As Range Dim cb As CheckBox Dim cell As Range ' Set the range where you want to insert checkboxes Set rng = Range("A1:A10") ' Modify this range as needed ' Loop through each cell in the range For Each cell In rng ' Insert a checkbox in the cell Set cb = ActiveSheet.CheckBoxes.Add(cell.Left, cell.Top, cell.Width, cell.Height) ' Adjust checkbox properties as needed With cb .Value = xlOff .LinkedCell = cell.Offset(0, 1).Address ' Link checkbox to cell for value tracking End With Next cell End SubYou can run this VBA macro to insert checkboxes in the specified range of cells. Modify the rng variable to specify the range where you want to insert the checkboxes.
To run the macro, press Alt + F11 to open the VBA editor, insert a new module, paste the code into the module, and then run the macro from the Excel interface or the VBA editor.
The text and the steps was created with the help of AI.
My answers are voluntary and without guarantee!
Hope this will help you.
Was the answer useful? Mark as best response and like it!
This will help all forum participants.
Many thanks for your reply & the details.
I have checked it using your shared VBA code & it worked well to get the checkboxes in the cells (a range of cells in one column). But when I filter this Excel data, the unwanted rows get hidden in the sheet. but the checkboxes do not. Hope you understand what my requirement is. Pls advice how the unwanted checkboxes can also get hidden on filtering, just like data in cells.
To ensure that the checkboxes are hidden along with the filtered data, you can use VBA to dynamically control the visibility of the checkboxes based on the filtered rows.
This VBA code ensures that the checkboxes are hidden along with the filtered data. When you filter your Excel data, the checkboxes that are associated with hidden rows will also be hidden. This ensures that only the checkboxes corresponding to visible rows remain visible, just like the data in the cells.
Private Sub Worksheet_Change(ByVal Target As Range) Dim cb As CheckBox Dim cell As Range Dim rng As Range ' Define the range where your checkboxes are located Set rng = Me.Range("A1:A10") ' Modify this range as needed ' Loop through each checkbox in the range For Each cb In Me.CheckBoxes ' Check if the checkbox is within the defined range If Not Intersect(cb.TopLeftCell, rng) Is Nothing Then ' Check if the linked cell is filtered out If cb.LinkedCell <> "" Then Set cell = Me.Range(cb.LinkedCell) If cell.EntireRow.Hidden Then ' Hide the checkbox if the linked cell's row is hidden cb.Visible = False Else ' Show the checkbox if the linked cell's row is visible cb.Visible = True End If End If End If Next cb End SubMy answers are voluntary and without guarantee!
Hope this will help you.
