Make rows (dis)appear with activeX checkbox

axelg

New Member
Joined
Jan 21, 2016
Messages
43
Office Version
  1. 365
Platform
  1. Windows
I want to hide rows when the Axtivex checkbox isn't checked.

So on a sheet nbr of AxtiveX checkboxes some basic text in fields, and some Activex Combo boxes

So I'm running a very simple script

VBA Code:
Private Sub CheckBox1_Change()

    ' Show or hide CheckBox2 based on the value of CheckBox1
    CheckBox2.Visible = CheckBox1.Value
    
    ' Show or hide row 5 based on the value of CheckBox1
    Rows(5).EntireRow.Hidden = Not CheckBox1.Value
    'ComboBox2.Visible = CheckBox1.Value


End Sub

Private Sub CheckBox2_Change()
    ' Show or hide CheckBox3 based on the value of CheckBox1
    CheckBox3.Visible = CheckBox2.Value
    
    ' Show or hide row 5 based on the value of CheckBox1
    ' Rows(8).EntireRow.Hidden = Not CheckBox2.Value
    ComboBox3.Visible = CheckBox2.Value
    
End Sub

Private Sub CheckBox3_Change()
    ' Show or hide CheckBox4 based on the value of CheckBox1
    CheckBox4.Visible = CheckBox3.Value
    
    ' Show or hide row 5 based on the value of CheckBox1
    ' Rows(8).EntireRow.Hidden = Not CheckBox2.Value
    ComboBox4.Visible = CheckBox3.Value
    
End Sub


This works fine in hiding the Combo and checkboxes.

But when I activate --> Rows(5).EntireRow.Hidden = Not CheckBox1.Value (or similar line) then the row dissapears as wanted.
But if then change the state of the combobox (in the above Checkbox) to make it (dis)appear the I can no longer select the Checkbox2

Seems like making that row appear / disappear / appear changes the character of the combox.


So basically all works, but only 1 time.
My issue is, a user changes his mind. Is there a line of code that should be added somewhere.

Thanks

ActiceX Combo Box.xlsb
ABC
1
2Deze lijn is zichtbaar
3
4
5Deze lijn is enkel zichtbaar indien Checkbox 1 geselecteerd is
6
7
8Deze lijn is enkel zichtbaar indien Checkbox 2 geselecteerd is
9
10
11Deze lijn is enkel zichtbaar indien Checkbox 3 geselecteerd is
12
13
14Aanpassing Active x via Developer / design mode / properties
15View code voor scripts
16
Boxes as you need
 

Attachments

  • screenshot axtivexboxes.png
    screenshot axtivexboxes.png
    20.9 KB · Views: 27

Excel Facts

Whats the difference between CONCAT and CONCATENATE?
The newer CONCAT function can reference a range of cells. =CONCATENATE(A1,A2,A3,A4,A5) becomes =CONCAT(A1:A5)
rewrote my code and this works. (there's a bit more in it )
VBA Code:
Private Sub Checkbox1_Click()
    If CheckBox1.Value = False Then
        CheckBox2.Visible = False
        ComboBox2.Visible = False
        ComboBox5.Visible = False
        Rows("5:5").Hidden = True
    Else
        CheckBox2.Visible = True
        ComboBox2.Visible = True
        ComboBox5.Visible = True
        Rows("5:5").Hidden = False
    End If
End Sub

Private Sub Checkbox2_Click()
    If CheckBox2.Value = False Then
        CheckBox3.Visible = False
        ComboBox3.Visible = False
        ComboBox6.Visible = False
        Rows("9:9").Hidden = True
    Else
        CheckBox3.Visible = True
        ComboBox3.Visible = True
        ComboBox6.Visible = False
        ComboBox2.Value = ComboBox1.Value
        Rows("9:9").Hidden = False
    End If
End Sub
 
Upvote 0
Solution

Forum statistics

Threads
1,224,823
Messages
6,181,177
Members
453,021
Latest member
Justyna P

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