Hide and Unhide rows and controls

[emilemil22]

Hello everyone!

I have to sheets- Sheet1 and Sheet2

In sheet 1 I have ListBox1 with 3 values which correspond to values in cells in sheet 2(A1:A3)

In Sheet 1 I have also two textbox controls textbox1 and textbox 2 allocated on the rows 10 to 15.

I want to do the follwoing
-whenver the selected value corresponds to the value of cell A1 in sheet 2, then the controls textbox1 and textbox2 along with rows 10-15 are hidden.
-whenever the selected value corresponds to the value of cell A2 or cell A3 in sheet 2, then the controls textbox1 and textbox2 along with rows 10-15 are unhidden.

Could you advise on the VBA macro? I am trying to do these for weeks but did not get near to that.

Many thanks for your help!!

 

[Yongle]

This code is triggered when a different value is selected in cell A1 in sheet1

This goes in the SHEET module for sheet1
when sheet1 is active ... right-click sheet tab \ click View Code \ paste code below into module \ back to Excel with {ALT}{F11}

Private Sub Worksheet_Change(ByVal Target As Range)
    Dim cel As Range, IsTrue As Boolean
    Set cel = Sheets("Sheet2").Range("A1")
    IsTrue = (Range("A1") = cel)
[I]'rows[/I]
    Rows("10:15").Hidden = IsTrue
[I]'VBA for [COLOR=#006400]form control[/COLOR] textboxes[/I]
    Me.Shapes("Textbox 1").Visible = Not IsTrue
    Me.Shapes("Textbox 2").Visible = Not IsTrue
[I]'VBA for [COLOR=#006400]Active-X [/COLOR]textboxes[/I]
    TextBox1.Visible = Not IsTrue
    TextBox2.Visible = Not IsTrue
End Sub

Note
- delete the 2 lines that are not relevant
- you did not tell us which textboxes are in the sheet
- form contol textbox contains a space in the name AFTER Textbox BEFORE the number (Textbox 1)
- active-x textbox name does not contain a space (Textbox1)

 

[emilemil22]

thanks for the reply. @Yongle
1. indeed i have an active x controls- TextBox1 & TextBox2 in Sheet 1

!!!Indeed I did make a mistake in my first post instead of Listbox1 I have a ComboBox1,
2. So whenever a value is selected from ComboBox1(sheet 1) which corresponds to value of cell A1 in Sheet 2 I want the rows 10:15 along with TextBox1 and TextBox2 to be hidden.
And whenever a value is selected from ComboBox1(sheet 1) which corresponds to value of cell A2 or A3 in Sheet 2 I want the rows 10:15 along with TextBox1 and TextBox2 to be unhidden.

Could you please state any changes for the code as the previous one did not work for me.

PS. I want the codes to be reforumlated in this way as the values in ComboBox1 are in another language (Russian) and I cannot simply use IF function, if you know what I mean.

Many thanks for your help!

 

[Yongle]

This is triggered when the value in Combobox1 is changed

Put in SHEET module for sheet1

Private Sub ComboBox1_Change()
    Dim cel As Range, IsTrue As Boolean
    Set cel = Sheets("Sheet2").Range("A1")
    IsTrue = (ComboBox1.Text = cel)

[COLOR=#006400][I]'rows[/I][/COLOR]
    Rows("10:15").Hidden = IsTrue

[COLOR=#006400]'textboxes[/COLOR]
    TextBox1.Visible = Not IsTrue
    TextBox2.Visible = Not IsTrue
End Sub

 

[emilemil22]

Thank you very much!

This code works perfectly.
Could you do a favor and write the code for the addition below.

When the value of combobox1 corresponds to A1 in Sheet 2 the rows 10:15 along with controls are hidden. But I want whenever the combobox value changes and corresponds to A2 or A3 in sheet 2 the rows 10:15 along with controls become unhidden and rows 20:25 along with controls textbox3 and textbox3 in sheet1 are hidden.

I REALLY appreciate your help.

 

[Yongle]

What happens to Textbox3, Textbox4 and rows 20:25 when Combobox1 = A1 (in Sheet2)
- should they be visible ?

Are there any more textboxes or rows ( not yet mentioned ) to hide/unhide linked to the value in Combobox1 ?
( it may be more efficient to write code in a different way )

 

[emilemil22]

At the begniing I tried to post the thread in a simple way as I thought I would be able to figure it out myself but I cannot. Below I write fully what I need.

In Sheet1 I have Combobox1 which can have 7 values corresponding to cells A1:A7 in Sheet2.

I want:
1. Whenever a value in Combobox1 in Sheet1 is selected and it corresponds to values A1 or A2 or A5 in Sheet 2 THEN rows 10:15 along with textbox1 and textbox2 in Sheet1 are hidden and rows 20:25 along with textbox3 and textbox4 are unhidden.

2. Whenever a value in Combobox1 in Sheet1 is selected and it corresponds to values A3 or A4 or A7 or A6 in Sheet 2 THEN rows 10:15 along with textbox1 and textbox2 in Sheet1 are unhidden and rows 20:25 along with textbox3 and textbox4 are hidden.

And I want this to be changed automatically and dynamically whenever a value in Combobox1 changes.

I know that this is not what I said in my original post, but I thought to just get codes for a piece and figure out rest by myself but I simply could not.

Really appreciate your quick and prompt replies!

 

[Yongle]

Try this
- there are only 2 scenarios and one is the opposite of the other
- the boolean variable is set to either true or false which determines visibility of rows 10:15 \ Textbox1 \ Textbox2
- visibility of rows 20:25 \ Textbox3 \ Textbox4 is the opposite of that

Private Sub ComboBox1_Change()
    
    Dim sh2 As Worksheet, IsTrue As Boolean
    Set sh2 = Sheets("Sheet2")

    Select Case ComboBox1.Text
        Case sh2.Range("A1"), sh2.Range("A2"), sh2.Range("A5")
            IsTrue = True
        Case Else
            IsTrue = False
    End Select
[I][COLOR=#006400]'rows[/COLOR][/I]
    Rows("10:15").Hidden = IsTrue
    Rows("20:25").Hidden = Not Rows("10:15").Hidden
[COLOR=#006400][I]'textboxes[/I][/COLOR]
    TextBox1.Visible = Not IsTrue
    TextBox2.Visible = Not IsTrue
    TextBox3.Visible = Not TextBox1.Visible
    TextBox4.Visible = Not TextBox1.Visible
    
End Sub

 

[emilemil22]

Thanks a lot WORKS PERFECTLY. Everyting works.

However, there is one minor issue. As I change the selections in ComboBox1 and respective textboxes become visible it seems I cannot edit them and write within them. When I turn the design mode and reposition them a bit, only then I can write within them. But of course this is not ideal.

Do you have a solution for this as well? I know that activex is very buggy and sensitive and it is advised to use shapes in this instances but I have a huge form and changing everything to shapes will mean a massive manual work.

Many thanks!!!!

 

[Yongle]

I cannot replicate your problem ( I can select visible textboxes and write in them after the above code runs )

So this is a "shot in the dark"
- replace the entire code with code below

Private Sub ComboBox1_Change()
    
    Dim sh2 As Worksheet, IsTrue As Boolean, tb As Variant, cel As Range
    Set sh2 = Sheets("Sheet2")
    Set cel = ComboBox1.TopLeftCell
    
    Application.ScreenUpdating = False
    cel.Activate
    Select Case ComboBox1.Text
        Case sh2.Range("A1"), sh2.Range("A2"), sh2.Range("A5")
            IsTrue = True
        Case Else
            IsTrue = False
    End Select
'rows
    Rows("10:15").Hidden = IsTrue
    Rows("20:25").Hidden = Not Rows("10:15").Hidden
'textboxes
    TextBox1.Visible = Not IsTrue
    TextBox2.Visible = Not IsTrue
    TextBox3.Visible = Not TextBox1.Visible
    TextBox4.Visible = Not TextBox1.Visible
'loop textboxes activating them if visible
    For Each tb In Array(TextBox1, TextBox2, TextBox3, TextBox4)
        If tb.Visible Then tb.Activate
    Next
    cel.Activate
    
    
End Sub