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!!

 

[emilemil22]

Thank a lot for your efforts everythiing works perfectly except the issues:
1. Still after certain textboxes become visible I am not able to write withing them
2. when I save the file in certain state the when i open it the tetboxes become mixed and overlapped on each other.

https://drive.google.com/open?id=18S22aqtxjvgM148WAQnuu3hYYK5qV90y

this is the file I have, could you open it and see what is the problem.

Here is the code from your previous post I have changed just names so that it correspoonds to my file. Just paste the code in vba.

The reference sheet where textboxes are hidden/unhidden is named "Form". and the rows to be hidden unhidden are 42:48 and 49:55 with textboxes a15, a16, a17, a14a, a14b, a14c. The combobox in my file is named a14.



Private Sub a14_Change()

Dim sh2 As Worksheet, IsTrue As Boolean, tb As Variant, cel As Range
Set sh2 = Sheets("Lists")
Set cel = a14.TopLeftCell

Application.ScreenUpdating = False
cel.Activate
Select Case a14.Text
Case sh2.Range("L4"), sh2.Range("L5"), sh2.Range("L6"), sh2.Range("L10"), sh2.Range("L11"), sh2.Range("L12")
IsTrue = True
Case Else
IsTrue = False
End Select
'rows
Rows("42:48").Hidden = IsTrue
Rows("49:55").Hidden = Not Rows("42:48").Hidden
'textboxes
a15.Visible = Not IsTrue
a16.Visible = Not IsTrue
a17.Visible = Not IsTrue
a14a.Visible = Not a15.Visible
a14b.Visible = Not a16.Visible
a14c.Visible = Not a17.Visible
'loop textboxes activating them if visible
For Each tb In Array(a15, a16, a17, a14a, a14b, a14c)
If tb.Visible Then tb.Activate
Next
cel.Activate


End Sub


\



Many thanks and I hope this is not too complicated

 

[Yongle]

Does your workbook VBA contain TextBox Event macros - perhaps TextBoxXX_Change ?
(or double click, got focus, lost focus, mouse move etc)

 

[emilemil22]

No, as far I can see I dont have any _Change events, I have just double checked it in my file which I shared with you!

 

[emilemil22]

And the file link is https://drive.google.com/open?id=18S22aqtxjvgM148WAQnuu3hYYK5qV90y

 

[Yongle]

Suggest you try to replicate it yourself in a NEW workbook with details exactly as explained by you in post#7

- sheet1 containing combobox1 with dropdown and textboxes 1,2,3 & 4
- sheet2 containing values in A1:A7

and the code provided in post#8 (repeated below)

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
'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
    
End Sub

Does the same problem happen in a clean workbook ?

 

[emilemil22]

What do you mean replicating in new workbook.? Just copying everything into a new workbook? If yes, I did that but I still have the same problem

 

[Yongle]

I meant starting from NEW to see if exactly the same thing happens
- sometimes problems "disappear" when we start again!

I have another idea for you to try in the original worksheet

You said the problem goes away when Textboxes are manually repositioned by you
- so let's ask VBA to do that instead and see if the same thing happens

Replace the code I gave you earlier with code below AND amend E11, E13 , E21 and E23 to the cells where the textboxes should be placed when visible

Private Sub ComboBox1_Change()
    
    Dim sh2 As Worksheet, IsTrue As Boolean
    Set sh2 = Sheets("Sheet2")
    Application.ScreenUpdating = False
    
    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

    On Error Resume Next
        TextBox1.Top = Range("[COLOR=#ff0000]E11[/COLOR]").Top
        TextBox1.Left = Range("[COLOR=#ff0000]E11[/COLOR]").Left
        TextBox2.Top = Range("[COLOR=#ff0000]E13[/COLOR]").Top
        TextBox2.Left = Range("[COLOR=#ff0000]E13[/COLOR]").Left
        TextBox3.Top = Range("[COLOR=#ff0000]E21[/COLOR]").Top
        TextBox3.Left = Range("[COLOR=#ff0000]E21[/COLOR]").Left
        TextBox4.Top = Range("[COLOR=#ff0000]E23[/COLOR]").Top
        TextBox4.Left = Range("[COLOR=#ff0000]E23[/COLOR]").Left
    On Error GoTo 0
End Sub

 

[emilemil22]

@Yongle Just the very last question to you! ARE YOU GENIOUS? Thank you so so much for your patience and help! It works perfectly I can not write in them!

We almost spent a day on this and it finally works! Once again thank you very much!!!

 

[Yongle]

Good !