Display/Hide textboxes with combobox and VBA

[kaurka]

Hi,

In the Useform I have a ComboBox with drop down options "1 to 12". When you select a number a NameBox and CompanyBox become visible. The number of NameBox and CompanyBox become visible depend on the number selected int he ComboBox. How can I shorten this code:

Private Sub ComboBox1_Change()

    If ComboBox1 = "" Then
        NameBox1.Visible = False
        CompanyBox1.Visible = False
        NameBox2.Visible = False
        CompanyBox2.Visible = False
        NameBox3.Visible = False
        CompanyBox3.Visible = False
        NameBox4.Visible = False
        CompanyBox4.Visible = False
        NameBox5.Visible = False
        CompanyBox5.Visible = False
        NameBox6.Visible = False
        CompanyBox6.Visible = False
        NameBox7.Visible = False
        CompanyBox7.Visible = False
        NameBox8.Visible = False
        CompanyBox8.Visible = False
        NameBox9.Visible = False
        CompanyBox9.Visible = False
        NameBox10.Visible = False
        CompanyBox10.Visible = False
        NameBox11.Visible = False
        CompanyBox11.Visible = False
        NameBox12.Visible = False
        CompanyBox12.Visible = False
        End If
        
    If ComboBox1 = "1" Then
        NameBox1.Visible = True
        CompanyBox1.Visible = True
        NameBox2.Visible = False
        CompanyBox2.Visible = False
        NameBox3.Visible = False
        CompanyBox3.Visible = False
        NameBox4.Visible = False
        CompanyBox4.Visible = False
        NameBox5.Visible = False
        CompanyBox5.Visible = False
        NameBox6.Visible = False
        CompanyBox6.Visible = False
        NameBox7.Visible = False
        CompanyBox7.Visible = False
        NameBox8.Visible = False
        CompanyBox8.Visible = False
        NameBox9.Visible = False
        CompanyBox9.Visible = False
        NameBox10.Visible = False
        CompanyBox10.Visible = False
        NameBox11.Visible = False
        CompanyBox11.Visible = False
        NameBox12.Visible = False
        CompanyBox12.Visible = False
        End If

[Tinbendr]

Private Sub ComboBox1_Change()
Dim A As Long

Select Case Val(ComboBox1.ListIndex)
    Case 0
        For A = 1 To 12
            Me.Controls("NameBox" & A).Visible = False
            Me.Controls("CompanyBox" & A).Visible = False
        Next
    
    Case 1 To 12
        
        For A = 1 To 12
            If A And ComboBox1.ListIndex Then
                Me.Controls("NameBox" & ComboBox1.ListIndex).Visible = True
                Me.Controls("CompanyBox" & ComboBox1.ListIndex).Visible = True
            Else
                Me.Controls("NameBox" & A).Visible = False
                Me.Controls("CompanyBox" & A).Visible = False
            End If
        Next
End Select
        
End Sub
        
End Sub

[kaurka]

Hi David,

Thanks for your reply,

However that didnt work, various NameBoxs and CompanyBoxs appear as you choose different number from dropbox.

[Tinbendr]

Oops! That should be

If A = ComboBox1.ListIndex Then

[kaurka]

Hi David,

The correction worked but i would like to show this:

When 3 is selected i see:

NameBox CompanyBox
NameBox CompanyBox
NameBox CompanyBox

Right now when i select 3, only third line is displayed

[xladept]

Hi Kaurka,

Try this:

Private Sub ComboBox1_Change()
Dim O As Object, S As String
    
    For Each O In ActiveSheet.Shapes: S = O.Name
    If InStr(1, S, "Name") Or InStr(1, S, "Comp") Then
    If Right(S, 1) = Val(ComboBox1.Value) Then
        O.Visible = True
    Else
        O.Visible = False: End If: End If: Next: End Sub