Hide checkbox when row is hidden

[ChristineJ]

Cells H1:H100 have each contain a text instruction. Cells J1:J100 each have a checkbox - I guess floating on top - from Insert Form Controls. I don't need the checkboxes to do anything except allow the user to tick them so they have a record of which instructions in column H they have completed.

At times I need to hide rows that have a checkbox in Column J. I'd like the checkbox in that row to hide as well. Is that possible?

Excel 2007

 

[MickG]

Hi, Try this:-
When you select a Row or Rows to Hide, you will see a Msgbox, If you click "OK" the rows selcted will be Hidden and any "CheckBox" in that Relected range will be Hidden.
If you click "Cancel any "Hidden " rows in the selected range will be "Visible" and Any "CheckBoxes" that are "Hidden" will be "Visible".

Private [COLOR="Navy"]Sub[/COLOR] Worksheet_SelectionChange(ByVal Target [COLOR="Navy"]As[/COLOR] Range)
[COLOR="Navy"]Dim[/COLOR] cbx [COLOR="Navy"]As[/COLOR] CheckBox, rng [COLOR="Navy"]As[/COLOR] Range
[COLOR="Navy"]If[/COLOR] Split(Target.address, "$")(1) = Target.row & ":" [COLOR="Navy"]Then[/COLOR]
    [COLOR="Navy"]For[/COLOR] [COLOR="Navy"]Each[/COLOR] cbx [COLOR="Navy"]In[/COLOR] ActiveSheet.CheckBoxes
        [COLOR="Navy"]Set[/COLOR] rng = cbx.TopLeftCell
            [COLOR="Navy"]If[/COLOR] Not Intersect(rng, Target) [COLOR="Navy"]Is[/COLOR] Nothing [COLOR="Navy"]Then[/COLOR]
                [COLOR="Navy"]If[/COLOR] MsgBox("Hide Rows ???", vbOKCancel + vbQuestion, "Accept/Reject") = vbOK [COLOR="Navy"]Then[/COLOR]
                    cbx.Visible = False
                    Target.EntireRow.Hidden = True
                [COLOR="Navy"]Else[/COLOR]
                    Target.EntireRow.Hidden = False
                    cbx.Visible = True
                [COLOR="Navy"]End[/COLOR] If
            [COLOR="Navy"]End[/COLOR] If
    [COLOR="Navy"]Next[/COLOR] cbx
[COLOR="Navy"]End[/COLOR] If
[COLOR="Navy"]End[/COLOR] [COLOR="Navy"]Sub[/COLOR]

Regards Mick

 

[ChristineJ]

Mick -

Thanks for responding! It is close what I need. When I tried it and selected numerous rows with checkboxes, I have to click OK once for each row selected, so it may mean numerous clicks of the OK. Otherwise, it has the functionality I'm looking for.

I already have this macro on a button to hide the rows with a "x" in Column C:

Sub Transactions_Only_On()
Application.ScreenUpdating = False
Dim i%
For i = 118 To 751
Rows(i).Hidden = Cells(i, 3).Value = "x"
Next i
Range("E1").Select
Application.ScreenUpdating = True
End Sub

Is there a way to get the checkboxes in Column J to hide when the row is hidden as part of this code?

 

[MickG]

Hi, Try this:-

[COLOR="Navy"]Sub[/COLOR] Transactions_Only_On()
Application.ScreenUpdating = False
[COLOR="Navy"]Dim[/COLOR] i%, rng [COLOR="Navy"]As[/COLOR] Range, cbx [COLOR="Navy"]As[/COLOR] CheckBox
[COLOR="Navy"]For[/COLOR] i = 118 To 751
[COLOR="Navy"]For[/COLOR] [COLOR="Navy"]Each[/COLOR] cbx [COLOR="Navy"]In[/COLOR] ActiveSheet.CheckBoxes
        [COLOR="Navy"]Set[/COLOR] rng = cbx.TopLeftCell
           [COLOR="Navy"]If[/COLOR] Not Intersect(rng, Range("J" & i)) [COLOR="Navy"]Is[/COLOR] Nothing And Cells(i, 3).Value = "x" [COLOR="Navy"]Then[/COLOR]
                    cbx.Visible = False
                    [COLOR="Navy"]Exit[/COLOR] For
                [COLOR="Navy"]End[/COLOR] If
                 [COLOR="Navy"]Next[/COLOR] cbx
Rows(i).Hidden = Cells(i, 3).Value = "x"
[COLOR="Navy"]Next[/COLOR] i
Range("E1").Select
Application.ScreenUpdating = True
[COLOR="Navy"]End[/COLOR] [COLOR="Navy"]Sub[/COLOR]

Regards Mick

 

[ChristineJ]

You are brilliant, Mick! Works perfectly. Thanks for taking the time to follow up on this for me. Chris