Hiding activex checkboxes

[riedyp]

Hello,

I am trying to hide/unhide rows and activex checkboxes based off of the value of an existing activex checkbox.
I am trying to:
- Unhide Rows(18:19,22) when the "Yes" checkbox of row 17 is True.
- Unhide Rows(20:21) when the "Yes" checkbox of row 19 is True.
- Unhide Rows(23:24) when the "Yes" checkbox of row 22 is True.

Below is just one of the countless methods I have tried. Please Let me know of anything you would suggest I try.

Private Sub Worksheet_Change(ByVal Target As CheckBox)
If CheckBox210.Value = True Then
Me.CheckBox213.Visible = True
Me.CheckBox214.Visible = True
Me.CheckBox215.Visible = True
Else
Me.CheckBox213.Visible = False
Me.CheckBox214.Visible = False
Me.CheckBox215.Visible = False
End If
End Sub

 

[John_w]

Welcome to MrExcel forums.

The Worksheet_Change event isn't fired when an ActiveX checkbox is clicked, so that won't work. The easiest method is to use Click handler for each "YES" checkbox in the sheet module.

CheckBox1 - name of "YES" checkbox on row 17
CheckBox4 - name of "YES" checkbox on row 19
CheckBox7 - name of "YES" checkbox on row 22

Add this code to the sheet module:

Option Explicit

Private Sub CheckBox1_Click()
    Hide_Or_Unhide Me.OLEObjects("CheckBox1")
End Sub

Private Sub CheckBox4_Click()
    Hide_Or_Unhide Me.OLEObjects("CheckBox4")
End Sub

Private Sub CheckBox7_Click()
    Hide_Or_Unhide Me.OLEObjects("CheckBox7")
End Sub

Private Sub Hide_Or_Unhide(cb As Object)

'    With cb
'        MsgBox "Clicked: " & .Name & " " & .Object.Caption & " Row " & .TopLeftCell.Row & vbCrLf & _
'               IIf(cb.Object.Value = True, "Ticked", "Unticked")
'    End With
   
    Select Case cb.TopLeftCell.Row
       
        Case 17
            If cb.Object.Value = True Then
                ActiveSheet.Rows("18:19").EntireRow.Hidden = False
                ActiveSheet.Rows("22").EntireRow.Hidden = False
            Else
                ActiveSheet.Rows("18:19").EntireRow.Hidden = True
                ActiveSheet.Rows("22").EntireRow.Hidden = True
            End If
           
        Case 19
            If cb.Object.Value = True Then
                ActiveSheet.Rows("20:21").EntireRow.Hidden = False
            Else
                ActiveSheet.Rows("20:21").EntireRow.Hidden = True
            End If
       
        Case 22
            If cb.Object.Value = True Then
                ActiveSheet.Rows("23:24").EntireRow.Hidden = False
            Else
                ActiveSheet.Rows("23:24").EntireRow.Hidden = True
            End If
       
    End Select

End Sub

The commented-out MsgBox code shows information about the checkbox that was clicked and is for diagnostic purposes

 

[riedyp]

Thank you! I tried to insert my CheckBox names in for your variables and it is not necessarily working properly. Could you possibly look at the attachments to see where I am going wrong? The CheckBoxes are named CheckBox201,CheckBox202,CheckBox203.......to CheckBox224.

 

[John_w]

It's easier to help you if you post your code (inside vba tags), rather than screenshots of it.

My code only handles the 3 "YES" checkboxes, as per your OP. If your 3 "YES" checkboxes are named "CheckBox201", "...204", "...207" then change the 'Click' handlers in my code to:

Private Sub CheckBox201_Click()
    Hide_Or_Unhide Me.OLEObjects("CheckBox201")
End Sub

Private Sub CheckBox204_Click()
    Hide_Or_Unhide Me.OLEObjects("CheckBox204")
End Sub

Private Sub CheckBox207_Click()
    Hide_Or_Unhide Me.OLEObjects("CheckBox207")
End Sub

Also, the above Sub CheckBox201_Click() replaces your Sub CheckBox201_Click(), shown in your code.

Why are you using Check Boxes though? Wouldn't Option Buttons be more appropriate for the user?

 

[riedyp]

Hi John,
It is working well now, thank you. I am only using checkboxes because I assumed it was my option since I am not very familiar with Options Boxes. what would be the advantages of using Option buttons?

 

[John_w]

For the either/or type of questions shown in your screenshot, option buttons would be a more natural user interface. Option buttons, with the same GroupName property, allow the user to select only 1 option, either YES, NO or N/A. Checkboxes allow the user to select none or all 3 options.

 

[riedyp]

I see what you mean, I am new to using these tools so I didn't know that those were an option. I am going to probably utilize those for this sheet now that I see what they are.

 

[riedyp]

If I am using ActiveX Option buttons consolidated with a "Group Box" would the code be fairly similar to what you suggested above?

Option Explicit

Private Sub OptionButton1_Click()
    Hide_Or_Unhide Me.OLEObjects("OptionButton1")
End Sub

Private Sub OptionButton4_Click()
    Hide_Or_Unhide Me.OLEObjects("OptionButton4")
End Sub

Private Sub OptionButton7_Click()
    Hide_Or_Unhide Me.OLEObjects("OptionButton7")
End Sub

Private Sub Hide_Or_Unhide(cb As Object) 'I do not know what the change would be here, unless it is as simple as switching to "ob"

'    With cb 'I do not know what the change would be here, unless it is as simple as switching to "ob"
'        MsgBox "Clicked: " & .Name & " " & .Object.Caption & " Row " & .TopLeftCell.Row & vbCrLf & _
'               IIf(cb.Object.Value = True, "Ticked", "Unticked")
'    End With
   
    Select Case cb.TopLeftCell.Row
       
        Case 17
            If cb.Object.Value = True Then 'I do not know what the change would be here, unless it is as simple as switching to "ob"
                ActiveSheet.Rows("18:19").EntireRow.Hidden = False
                ActiveSheet.Rows("22").EntireRow.Hidden = False
            Else
                ActiveSheet.Rows("18:19").EntireRow.Hidden = True
                ActiveSheet.Rows("22").EntireRow.Hidden = True
            End If
           
        Case 19
            If cb.Object.Value = True Then 'I do not know what the change would be here, unless it is as simple as switching to "ob"
                ActiveSheet.Rows("20:21").EntireRow.Hidden = False
            Else
                ActiveSheet.Rows("20:21").EntireRow.Hidden = True
            End If
       
        Case 22
            If cb.Object.Value = True Then 'I do not know what the change would be here, unless it is as simple as switching to "ob"
                ActiveSheet.Rows("23:24").EntireRow.Hidden = False
            Else
                ActiveSheet.Rows("23:24").EntireRow.Hidden = True
            End If
       
    End Select

End Sub

 

[John_w]

If I am using ActiveX Option buttons consolidated with a "Group Box" would the code be fairly similar to what you suggested above?

A Group Box is a Form Control and I don't know if it would work with ActiveX Option Buttons. As I said in my previous post, it is easier to 'group' ActiveX option buttons by setting the GroupName property of every option button. So for the YES, NO and N/A option buttons on row 17, give them all the GroupName "Row17":

Similarly, GroupName "Row19" for all 3 option buttons on row 19, and "Row22" for the 3 on row 22.

Yes, the code is very similar. Changing the variable name from 'cb' to 'ob' is sensible, now that the Hide_Or_Unhide routine is dealing with an option button.

Private Sub Hide_Or_Unhide(ob As Object)

    With ob
        MsgBox "Check Option Button: " & .Name & " " & .Object.Caption & " Row " & .TopLeftCell.Row & vbCrLf & _
               "State " & IIf(ob.Object.Value = True, "On", "Off")
    End With
   
    Select Case ob.TopLeftCell.Row
       
        Case 17
            If ob.Object.Value = True Then
                ActiveSheet.Rows("18:19").EntireRow.Hidden = False
                ActiveSheet.Rows("22").EntireRow.Hidden = False
            Else
                ActiveSheet.Rows("18:19").EntireRow.Hidden = True
                ActiveSheet.Rows("22").EntireRow.Hidden = True
            End If
           
        Case 19
            If ob.Object.Value = True Then
                ActiveSheet.Rows("20:21").EntireRow.Hidden = False
            Else
                ActiveSheet.Rows("20:21").EntireRow.Hidden = True
            End If
       
        Case 22
            If ob.Object.Value = True Then
                ActiveSheet.Rows("23:24").EntireRow.Hidden = False
            Else
                ActiveSheet.Rows("23:24").EntireRow.Hidden = True
            End If
       
    End Select

End Sub

The main change is needed in the "_Click" handlers. You need a "_Click" handler for every option button, but each one passes the "YES" option button to Hide_Or_Unhide. Therefore, with the following option button names for Row 17:

YES - OptionButton1, NO - OptionButton2, N/A - OptionButton3

The "_Click" handlers would be:

Private Sub OptionButton1_Click()
    Hide_Or_Unhide Me.OLEObjects("OptionButton1")
End Sub

Private Sub OptionButton2_Click()
    Hide_Or_Unhide Me.OLEObjects("OptionButton1")
End Sub

Private Sub OptionButton3_Click()
    Hide_Or_Unhide Me.OLEObjects("OptionButton1")
End Sub

and follow the same pattern for the option buttons on row 19 and row 22.