VBA Code for group of checkboxes

[bellej1207]

Hi,

I'm a little out of my depth for what I'm trying to achieve, I haven't been able to find an answer to my query on the usually helpful internet or this forum, hope you can help.

I've developed a Client Needs Analysis tool which populates a question bank using a series of checkboxes. Basically the user clicks checkboxes to unhide rows on another worksheet to build up a list of questions. I have that coding working nicely e.g.

Private Sub CheckBox1_Click()

If CheckBox1 = True Then
[='Print'!14:14].EntireRow.Hidden = False
Else: [='Print'!14:14].EntireRow.Hidden = True
End If
End Sub

The questions are grouped in to 10 different headings, e.g. Tax, Governance, Investments and the checkboxes all have these as the GroupName.

What I am looking for is the coding that will hide a row if the entire group of checkboxes are blank (or False). So if all the checkboxes in the Tax section (GroupName Tax or CheckBox 1, 2, 3 & 4 are unchecked then row 5 is hidden)

Thanks in advance

Anna

 

[dmt32]

Hi welcome to the board.
With so many checkboxes the cleanest way would be to create a Class which would negate need for all the individual checkboxes code & if can find time, will have a look further for you (or perhaps another here will) but for moment, you should be able to manage your requirement with a common Function for your checkboxes.

Give this code a try & see if helps:

In Standard Module:

Function GroupClear(ByVal sh As Object, ByVal Group As String) As Boolean


    Dim ole As OLEObject


    For Each ole In sh.OLEObjects
        If TypeName(ole.Object) = "CheckBox" Then
            If ole.Object.GroupName = Group And ole.Object.Value = True Then Exit Function
        End If
    Next ole
    GroupClear = True
End Function

In your worksheet with checkboxes place following in the sheets code page:

Private Sub CheckBox1_Click()
With Me.CheckBox1
Sheets("Sheet1").Rows(5).EntireRow.Hidden = GroupClear(sh:=Me, Group:=.GroupName)
End With
End Sub


Private Sub CheckBox2_Click()
With Me.CheckBox2
Sheets("Sheet1").Rows(5).EntireRow.Hidden = GroupClear(sh:=Me, Group:=.GroupName)
End With
End Sub


Private Sub CheckBox3_Click()
With Me.CheckBox3
Sheets("Sheet1").Rows(5).EntireRow.Hidden = GroupClear(sh:=Me, Group:=.GroupName)
End With
End Sub


Private Sub CheckBox4_Click()
With Me.CheckBox4
Sheets("Sheet1").Rows(5).EntireRow.Hidden = GroupClear(sh:=Me, Group:=.GroupName)
End With
End Sub

Change sheet name shown in RED as required.

Hope Helpful

Dave