Hide multiple textbox/combobox/label if condition is met

[MK29]

Hello all.

ive been stuck on this string on vba for HOURS.

I am trying to write a code that depending on the number inputed by a user will make select combo boxes visible... i.e. users selects 2 therefore 2 combo boxes become visible out of the 7 and the remaining are hidden.

as there is a large amount of combo boxes to hide and unhide i thought it would be easier and mor efficient to make a string but it doesnt seem to be working when i test it..

does anyone know a code that will help?

VBA Code:

Public Counterparty1, Counterparty2, Counterparty3, Counterparty4, Counterparty5, Counterparty6, Counterparty7 As Long

'and then in the module

If Counterparty1 = True Then
UserForm1.CPLabel1.Visible = True
UserForm1.SHLabel1.Visible = True
UserForm1.CIF1.Visible = True
UserForm1.CPTB1.Visible = True
UserForm1.SHTB1.Visible = True
UserForm1.CIFTB1.Visible = True
End If

If Counterparty2 = True Then
UserForm1.CPLabel2.Visible = True
UserForm1.SHLabel2.Visible = True
UserForm1.CIF2.Visible = True
UserForm1.CPTB2.Visible = True
UserForm1.SHTB2.Visible = True
UserForm1.CIFTB2.Visible = True
End If

If Counterparty3 = True Then
UserForm1.CPLabel3.Visible = True
UserForm1.SHLabel3.Visible = True
UserForm1.CIF3.Visible = True
UserForm1.CPTB3.Visible = True
UserForm1.SHTB3.Visible = True
UserForm1.CIFTB3.Visible = True
End If

If Counterparty4 = True Then
UserForm1.CPLabel4.Visible = True
UserForm1.SHLabel4.Visible = True
UserForm1.CIF4.Visible = True
UserForm1.CPTB4.Visible = True
UserForm1.SHTB4.Visible = True
UserForm1.CIFTB4.Visible = True
End If

If Counterparty5 = True Then
UserForm1.CPLabel5.Visible = True
UserForm1.SHLabel5.Visible = True
UserForm1.CIF5.Visible = True
UserForm1.CPTB5.Visible = True
UserForm1.SHTB5.Visible = True
UserForm1.CIF5.Visible = True
End If

If Counterparty6 = True Then
UserForm1.CPLabel6.Visible = True
UserForm1.SHLabel6.Visible = True
UserForm1.CIF6.Visible = True
UserForm1.CPTB6.Visible = True
UserForm1.SHTB6.Visible = True
UserForm1.CIF6.Visible = True
End If

If Counterparty7 = True Then
UserForm1.CPLabel7.Visible = True
UserForm1.SHLabel7.Visible = True
UserForm1.CIF7.Visible = True
UserForm1.CPTB7.Visible = True
UserForm1.SHTB7.Visible = True
UserForm1.CIF7.Visible = True
End If


If UserForm1.NumberCP.Text = "1" Then
Counterparty1 = True
Counterparty2 = False
Counterparty3 = False
Counterparty4 = False
Counterparty5 = False
Counterparty6 = False
Counterparty7 = False
End If

If UserForm1.NumberCP.Text = "2" Then
Counterparty1 = True
Counterparty2 = True
Counterparty3 = False
Counterparty4 = False
Counterparty5 = False
Counterparty6 = False
Counterparty7 = False
End If

If UserForm1.NumberCP.Text = "3" Then
Counterparty1 = True
Counterparty2 = True
Counterparty3 = True
Counterparty4 = False
Counterparty5 = False
Counterparty6 = False
Counterparty7 = False
End If

If UserForm1.NumberCP.Text = "4" Then
Counterparty1 = True
Counterparty2 = True
Counterparty3 = True
Counterparty4 = True
Counterparty5 = False
Counterparty6 = False
Counterparty7 = False
End If

If UserForm1.NumberCP.Text = "5" Then
Counterparty1 = True
Counterparty2 = True
Counterparty3 = True
Counterparty4 = True
Counterparty5 = True
Counterparty6 = False
Counterparty7 = False
End If

If UserForm1.NumberCP.Text = "6" Then
Counterparty1 = True
Counterparty2 = True
Counterparty3 = True
Counterparty4 = True
Counterparty5 = True
Counterparty6 = True
Counterparty7 = False
End If

If UserForm1.NumberCP.Text = "7" Then
Counterparty1 = True
Counterparty2 = True
Counterparty3 = True
Counterparty4 = True
Counterparty5 = True
Counterparty6 = True
Counterparty7 = True
End If

 

[Legacy 456155]

Your assignments to Counterparty1 -7 occur after you use them. Please explain.

Public Counterparty1, Counterparty2, Counterparty3, Counterparty4, Counterparty5, Counterparty6, Counterparty7 As Long

Can't do that in VBA. 1-6 will all be variants.

 

[dmt32]

You do not say how users will input the number but lets assume it's a textbox on your userform

try following

 Dim ctrl As Control
    Dim UserValue As Integer
    
    UserValue = Val(Me.TextBox1.Value)
    
    For Each ctrl In Me.Controls
        Select Case TypeName(ctrl)
        Case "ComboBox", "Label"
            ctrl.Visible = Val(Right(ctrl.Name, 1)) <= UserValue
        End Select
    Next

This assumes that your controls follow the naming convention shown in your post

Dave

 

[MK29]

Your assignments to Counterparty1 -7 occur after you use them. Please explain.

Public Counterparty1, Counterparty2, Counterparty3, Counterparty4, Counterparty5, Counterparty6, Counterparty7 As Long

Can't do that in VBA. 1-6 will all be variants

Thanks for replying!!


the end result should be that if an individual selects 1 in UserForm1.NumberCP.Text then only the first selection of fields should become visible to enter values(specified relevant fields in counterparty 1) and then fields in counterparty 2-7 should remain invisible ..

and then if 2 is entered into UserForm1.NumberCP.Text then counterparty 1-2 is visible and the rest are invisible

even when I change the string names to solely letters it doesn't work

 

[MK29]

You do not say how users will input the number but lets assume it's a textbox on your userform

try following

Dim ctrl As Control
    Dim UserValue As Integer
   
    UserValue = Val(Me.TextBox1.Value)
   
    For Each ctrl In Me.Controls
        Select Case TypeName(ctrl)
        Case "ComboBox", "Label"
            ctrl.Visible = Val(Right(ctrl.Name, 1)) <= UserValue
        End Select
    Next

This assumes that your controls follow the naming convention shown in your post

Dave

thanks for replying.


trying to understand your code.. what would be input in ctrl and control?

 

[dmt32]

ctrl is an object variable & is used in for next loop to cycle through all the controls on your userform.
If the TypeName of the control matches the select case statement, then ctrl variable becomes that control & visibility is set according to uservalue variable - entered in textbox or whatever method you are using?


Dave

 

[MK29]

ctrl is an object variable & is used in for next loop to cycle through all the controls on your userform.
If the TypeName of the control matches the select case statement, then ctrl variable becomes that control.

Dave

hi im sorry im not understand because im not sure how I will make multiple textbox, combo box and labels invisible depending on the value selected.

I need to condense them all down into a string otherwise the module will be extremely heavy as its 35 boxes per value that will either be visible or invisible

 

[dmt32]

Depending on number entered (in textbox??) then the code while loop through ALL your userforms controls & if the control meets the Typename criteria in the select case statement, it's visibility will be set based on the user value entered.

You should note that suggestion was based on there just being 7 comboboxes with a naming convention of numeric value at end - it will need modifying if there are more than 9 comboboxes with double digit names.

 

[MK29]

Depending on number entered (in textbox??) then the code while loop through ALL your userforms controls & if the control meets the Typename criteria in the select case statement, it's visibility will be set based on the user value entered.

You should note that suggestion was based on there just being 7 comboboxes with a naming convention of numeric value at end - it will need modifying if there are more than 9 comboboxes with double digit names.

thanks but I really don't understand how I would add the counterparty string into the below

For Each ctrl In Me.Controls
Select Case TypeName(ctrl)
Case "ComboBox", "Label"

 

[dmt32]

Probably easier if you publish ALL the code in your userform & explain which control will be "inputted by a user" or even better, place copy of your workbook in a dropbox & provide a link to it here.

Dave