Hide Check Boxes when Row is Hidden

[tlc53]

Hi there,

I have check boxes in column L (L20:L64) which I would like to be hidden, when the row is hidden. Currently all the check boxes are clustering together on L65 when any rows are hidden.

The rows start off as being hidden and once data is entered in column B, the row below is unhidden with this VBA coding..

Private Sub Worksheet_Change(ByVal Target As Range)
Dim rng As Range
Set rng = Intersect(Target, [B19:B64,B84:B129])
If Not rng Is Nothing Then rng(2, 1).EntireRow.Hidden = False
End Sub

Is there a VBA code that can hide the checkboxes when the row is hidden? Move and Size with Cells is not an option on the checkbox. The checkboxes are linked to column M which returns a true/false depending on whether it is ticked.

Thank you for your time!

 

[Yongle]

try

Private Sub Worksheet_Change(ByVal Target As Range)
    Dim rng As Range
    Set rng = Intersect(Target, [B19:B64,B84:B129])
    If Not rng Is Nothing Then
        rng(2, 1).EntireRow.Hidden = False
        CheckBoxShowHide
    End If
End Sub

Sub CheckBoxShowHide()
    Dim cb As Shape, rowZ As Range
    For Each cb In ActiveSheet.Shapes
        If cb.Type = msoFormControl Then
            If cb.FormControlType = xlCheckBox Then
               Set rowZ = Range(cb.ControlFormat.LinkedCell).EntireRow
               cb.Visible = Not rowZ.Hidden
            End If
        End If
    Next cb
End Sub

 

[tlc53]

Thank you but it doesn't appear to be working.

When it went to reveal the next line, it returned the following error:
Run time error 1004 Method Range of object_Worksheet failed

At one point when I was testing it, it also highlighted this line as an error:
Set rowZ = Range(cb.ControlFormat.LinkedCell).EntireRow

It then went on to say: Run-time error -2147352571 The item with the specified name wasn't found

 

[Yongle]

The above code applies to worksheet Form Control checkboxes

If yours are Active-X checkboxes, try ...

Sub CheckBoxShowHide()
    Dim cb As OLEObject, rowZ As Range
    For Each cb In ActiveSheet.OLEObjects
        If cb.OLEType = 2 Then
               Set rowZ = Range(cb.LinkedCell).EntireRow
               cb.Visible = Not rowZ.Hidden
        End If
    Next cb
End Sub

 

[tlc53]

Hi. The checkboxes are Form Control.
I tried your first code again but nothing happens. It is not even revealing the next line when B19:B64 is populated.

 

[Jon Peltier]

No need for code, it's really simpler than all that.

First you need to align the checkboxes with the rows. Hold the Alt key while you move and resize the checkbox so that its edges stick to the cell borders.

Now select the checkbox, click on the Format (or Format Shape) tab of the ribbon, find the Size group way on the right, and click the little thingie at the bottom right corner of the Size group.

Under Properties, select Move and Size With Cells.

When a row is hidden, its height becomes zero so it becomes invisible. Since the checkbox now resizes with cells, its height also becomes zero, and it also becomes invisible.

 

[tlc53]

Hooray!!!
Thank you! I had thought that because I couldn't access that option from right click, it wasn't available.
Simple is great. Thanks again!