Search code examples
excelvbacheckboxexcel-2011

Check box general selector and macro to show next three rows when one checkbox is selected

I am new to macros so I'm not sure this is possible in VBA.

I am trying to create a document where is composed with many mini tables made of 4 rows. One row is the title which have a checkbox and will always be shown and three rows below where contains data that I only what to see when I select the relevant checkbox.

This document will have many mini tables hence many check boxes and I was wondering if there is a generic selector for checkboxes where I can apply the same macro.

I have seen the following macro, but this will apply only to one check box and I was wondering if there was a way to apply one for all checkboxes saying that if checkbox in row 4 is selected then show row 5,6 and 7. If checkbox in row 8 is selected then show rows 9,10,and 11 and so on....

Private Sub CheckBoxRow4_Click()
 Rows("5:6:7").Hidden = CheckBoxRow4.Value
End Sub

See screenshot for a better idea. Excel table

It would also be appreciated if you could indicate how can I get those three rows below hidden by default when opening the document.

I am using Excel 2011 for Mac if that makes any difference.

Thank you in advance.

Solution

  • I'm sure there will be several approaches to this. My first thought goes to adding checkboxes, linking them all to a single macro. When activated, you have to do several things:

    1. find out who is calling the sub (which checkbox);

    2. find out where that specific checkbox is located (which row);

    3. hide / unhide the rows below it.

    1:

    The name of the checkbox is easy. Application Caller will give you that.

    2:

    Location is the real problem here. I don't see a simple solution here, other then giving the checkboxes such specific names, that it is clear which row it is in. If you add a checkbox, you can give the name in the 'named range' inputfield. If you give it names that will specify the rows it must hide, it is even better. So something like: HIDE_4_7 would indicate the checkbox must hide / unhide rows 4 to 7.

    3:

    Hiding the rows is now easy.

    total solution:

    Sub HideRows()
Dim cbName As String
Dim cbValue As Boolean
Dim s() As String
Dim firstRow As Long
Dim lastRow As Long

On Error Resume Next
cbName = Application.Caller
If Err.Number <> 0 Then Exit Sub 'sub is not called from an application object
cbValue = (ActiveSheet.CheckBoxes(cbName) = xlOn)
If Err.Number <> 0 Then Exit Sub 'sub is not called from a checkbox
On Error GoTo 0


s = Split(cbName, "_")
If s(LBound(s)) <> "HIDE" Then Exit Sub 'name of the shape is not valid
firstRow = Val(s(LBound(s) + 1))
lastRow = Val(s(LBound(s) + 2))

Sheets(1).Rows(firstRow & ":" & lastRow).Hidden = Not cbValue

End Sub

    You would have to call the checkboxes HIDE_*firstrow*_*lastrow*, and link them to this sub. That works on my side.

    EDIT

    To hide all rows on opening, you could use the Workbook_Open sub (in the workbook code storage thingy). Something like this:

    Private Sub Workbook_Open()
Dim shp As Shape
Dim s() As String
Dim firstRow As Long
Dim lastRow As Long

Dim cbValue As Boolean
For Each shp In Sheets(1).Shapes
    Debug.Print shp.Name
    s = Split(shp.Name, "_")
    If s(LBound(s)) <> "HIDE" Then GoTo nextShp
    'set checkbox off:
    Sheets(1).CheckBoxes(shp.Name) = xlOff
    firstRow = Val(s(LBound(s) + 1))
    lastRow = Val(s(LBound(s) + 2))

    Sheets(1).Rows(firstRow & ":" & lastRow).Hidden = True


nextShp:
Next shp

End Sub