Hide checkbox when row is hidden

[ChristineJ]

I am using Range("10:20").EntireRow.Hidden = True and Range("10:20").EntireRow.Hidden = False to toggle back and forth between showing and hiding these rows. Some of these rows have a checkbox form that fits in its row in column V.

I want the checkboxes in the rows to hide as well when the rows are hidden. Right now they all stack up on top of one another so it looks like one remains. In addition, when I close and reopen my file, the checkboxes no longer are in the rows where I placed them, but instead all stacked into one.

I was told to use the Move and Size with Cells property, but that is grayed out with this type of form. I don’t want an ActiveX form. Is there code I can add to anchor these checkboxes so they stay where they are and so that they all hide?

 

[ChristineJ]

Disregard. I got the effect I wanted by grouping the checkbox with a tiny square shape. I was able to format the shape with the Move and Size with Cells property.

 

[MickG]

You might as well have this, it might come in.
This code will Hide/unhide each time its run.

Private Sub CommandButton1_Click()
Dim Chk As CheckBox
 For Each Chk In ActiveSheet.CheckBoxes
    If Not Intersect(Chk.TopLeftCell, Range("10:20")) Is Nothing Then
        With Chk
            .Visible = Not .Visible
        End With
    End If
 Next Chk
With ActiveSheet.Range("10:20").EntireRow
    .Hidden = Not .Hidden
End With
End Sub

 

[ChristineJ]

The grouping with a shape did not work after all, so I was glad you followed up with your post. However, even with your code the checkboxes are stacking up on one another when the rows are hidden and aren't hiding. Thanks.

 

[MickG]

Are the boxes, still grouped ????
With the ungrouped Boxes:-
If you format only checkboxes that are outside rows 10: 20 as "Don't move or size with cells", and leave those within the rows a "Move but don't size with cells" then when you run the code the unhidden Check boxes should not move, It works for me on a trial sheet..!!!

 

[ChristineJ]

I started a blank spreadsheet and put a checkbox in each row in 10:20 in column C. I formatted the properties as you have above and pasted the code into the worksheet. I'm not doing something right, because when I hide the rows all of the checkboxes stack up on top of each other in row 21. I'm hiding the rows by highlighting the row address at the left and right clicking/Hide.

 

[MickG]

I'm not sure what's happening here.
Presumably its the boxes that are not hidden that are stacking Up ????

The code hides rows "10:20" and the checkboxes within them, You don't need to physically hide them, The code below, with the added line also sets the "Move/Don't Move" format. When you run the code the "Check boxes" with the rows "10:20 should be hidden, the other chekboxes should stay on the original rows/column, but off course they will move relative to the sheet as 10 rows are hidden .
When you run the code again, the rows and checkboxes should all show in the original places.
Try again, let me know exactly the result when you run the code.

Private Sub CommandButton1_Click()
Dim Chk As CheckBox
For Each Chk In ActiveSheet.CheckBoxes
Chk.Placement = xlMove 'New line added
If Not Intersect(Chk.TopLeftCell, Range("10:20")) Is Nothing Then
With Chk
.Visible = Not .Visible
End With
End If
Next Chk
With ActiveSheet.Range("10:20").EntireRow
.Hidden = Not .Hidden
End With
End Sub

 

[ChristineJ]

My fault entirely. I was not running your first code properly; once I did, it produced exactly the results I needed. I was able to tweak that code now so it works perfectly in my application. Apologies for using your time unnecessarily the second time. I appreciate your help - this is a very useful code! CJ

 

[MickG]

Pleased, you stuck with it and got it working
Regrds Mick