Hiding activex checkboxes

riedyp

Board Regular
Joined
Feb 13, 2020
Messages
88
Office Version
  1. 365
Platform
  1. Windows
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.
VBA Code:
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

snip.PNG
 

Excel Facts

How to calculate loan payments in Excel?
Use the PMT function: =PMT(5%/12,60,-25000) is for a $25,000 loan, 5% annual interest, 60 month loan.
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:

VBA Code:
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
 
Upvote 0
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.
 

Attachments

  • Forum1.PNG
    Forum1.PNG
    71.9 KB · Views: 16
  • Forum2.PNG
    Forum2.PNG
    149.2 KB · Views: 19
  • Forum3.PNG
    Forum3.PNG
    148.2 KB · Views: 19
Upvote 0
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:
VBA Code:
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?
 
Last edited:
Upvote 0
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?
 
Upvote 0
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.
 
Upvote 0
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.
 
Upvote 0
If I am using ActiveX Option buttons consolidated with a "Group Box" would the code be fairly similar to what you suggested above?
VBA Code:
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
 
Upvote 0
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":

1582148490045.png


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.

VBA Code:
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:

VBA Code:
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.
 
Last edited:
Upvote 0

Forum statistics

Threads
1,223,164
Messages
6,170,444
Members
452,326
Latest member
johnshaji

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top