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. Pleas...
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.