Simplify or reduce VBA code

[Aretradeser]

How can I simplify VBA codes, concerning the hiding or visibility of different objects?
EXAMPLE 1:

Private Sub UserForm_Initialize()
    With Sheets("BDATOS")
        n = .Range("B" & Rows.Count).End(xlUp).Row
        va = .Range("G2:G" & n) 'ComboBox1 = By country of destination
        vb = .Range("H2:H" & n) 'ComboBox2 = By destination city
        vc = .Range("B2:B" & n) 'ComboBox3 = By name
    End With
    Set dar = CreateObject("System.Collections.Arraylist")
    Label2.Visible = False
    Label3.Visible = False
    TextBox1.Visible = False
    TextBox2.Visible = False
    TextBox3.Visible = False
    CheckBox2.Visible = False
    CheckBox3.Visible = False
    CheckBox4.Visible = False
    Label4.Visible = False
    Label5.Visible = False
    Label7.Visible = False
    ComboBox1.Visible = False
    ComboBox2.Visible = False
    ComboBox3.Visible = False
End Sub

EXAMPLE 2

Private Sub OptionButton2_Click()
OptionButton1.Visible = False
OptionButton4.Visible = False

    CheckBox2.Visible = True
    CheckBox3.Visible = True
    CheckBox4.Visible = True
    Label4.Visible = True
    Label5.Visible = True
    Label7.Visible = True
    ComboBox1.Visible = True
    ComboBox2.Visible = True
    ComboBox3.Visible = True
    Label1.Visible = False
    TextBox1.Visible = False
    Label8.Visible = False
    Label2.Visible = False
    TextBox2.Visible = False
    Label3.Visible = False
    TextBox3.Visible = False
    TextBox1 = ""
    ListBox1.RowSource = ""
End Sub

 

[Micron]

I don't see anything that decides what is visible when, and they're not in the same order, so it's a bit too difficult to discern the difference between those 2 codes. Perhaps loop over the collection of controls involved whose tag property you have set to some value (e.g. Required) if you want to hide/unhide just some of them according to some condition. If all of them need to be altered according to a condition, then don't worry about the tag property.

 

[dmt32]

Hi,
try creating a table of the controls with their visible state for each requirement in your project listed in column(s)

Copy this table to a worksheet named "Form Controls"

07-04-2022.xls
	A	B	C
1	Control	State 1	State 2
2	OptionButton1	FALSE	FALSE
3	OptionButton4	FALSE	FALSE
4	CheckBox2	TRUE	FALSE
5	CheckBox3	TRUE	FALSE
6	CheckBox4	TRUE	FALSE
7	ComboBox1	TRUE	FALSE
8	ComboBox2	TRUE	FALSE
9	ComboBox3	TRUE	FALSE
10	TextBox1	FALSE	FALSE
11	TextBox2	FALSE	FALSE
12	TextBox3	FALSE	FALSE
13	Label1	FALSE	TRUE
14	Label2	FALSE	FALSE
15	Label3	FALSE	FALSE
16	Label4	TRUE	FALSE
17	Label5	TRUE	FALSE
18	Label7	TRUE	FALSE
19	Label8	FALSE	TRUE
Form Controls

Cells with Data Validation
Cell	Allow	Criteria
B2:C19	List	True,False

Place following code In a STANDARD module

Enum ControlState
    State1 = 2
    State2
End Enum
Sub ControlsVisible(ByVal Form As Object, ByVal State As ControlState)
    Dim arr         As Variant
    Dim rng         As Range
   Dim i           As Long

    With ThisWorkbook.Worksheets("Form Controls")
        Set rng = .Range("A1").CurrentRegion
       ' .Visible = xlSheetVeryHidden '< optional
    End With
    Set rng = rng.Offset(1).Resize(rng.Rows.Count - 1)
    arr = rng.Value2
    For i = 1 To UBound(arr, 1)
        Form.Controls(arr(i, 1)).Visible = arr(i, State)
    Next
End Sub

From your forms CODE Page

Private Sub OptionButton2_Click()
   
    ControlsVisible Me, State2
   
End Sub

Private Sub UserForm_Initialize()
    With Sheets("BDATOS")
        n = .Range("B" & Rows.Count).End(xlUp).Row
        va = .Range("G2:G" & n) 'ComboBox1 = By country of destination
        vb = .Range("H2:H" & n) 'ComboBox2 = By destination city
        vc = .Range("B2:B" & n) 'ComboBox3 = By name
    End With
    Set dar = CreateObject("System.Collections.Arraylist")
   
    ControlsVisible Me, State1
End Sub

Solution is untested & you may have to update the visible State of each control in the table as required.
Table can be expanded to include other permutations if needed

Dave

 

[Aretradeser]

I don't see anything that decides what is visible when, and they're not in the same order, so it's a bit too difficult to discern the difference between those 2 codes. Perhaps loop over the collection of controls involved whose tag property you have set to some value (e.g. Required) if you want to hide/unhide just some of them according to some condition. If all of them need to be altered according to a condition, then don't worry about the tag property.

Thank you very much for your answer.

 

[Aretradeser]

Hi,
try creating a table of the controls with their visible state for each requirement in your project listed in column(s)

Copy this table to a worksheet named "Form Controls"

07-04-2022.xls
	A	B	C
1	Control	State 1	State 2
2	OptionButton1	FALSE	FALSE
3	OptionButton4	FALSE	FALSE
4	CheckBox2	TRUE	FALSE
5	CheckBox3	TRUE	FALSE
6	CheckBox4	TRUE	FALSE
7	ComboBox1	TRUE	FALSE
8	ComboBox2	TRUE	FALSE
9	ComboBox3	TRUE	FALSE
10	TextBox1	FALSE	FALSE
11	TextBox2	FALSE	FALSE
12	TextBox3	FALSE	FALSE
13	Label1	FALSE	TRUE
14	Label2	FALSE	FALSE
15	Label3	FALSE	FALSE
16	Label4	TRUE	FALSE
17	Label5	TRUE	FALSE
18	Label7	TRUE	FALSE
19	Label8	FALSE	TRUE
Form Controls

Cells with Data Validation
Cell	Allow	Criteria
B2:C19	List	True,False

Place following code In a STANDARD module

Enum ControlState
    State1 = 2
    State2
End Enum
Sub ControlsVisible(ByVal Form As Object, ByVal State As ControlState)
    Dim arr         As Variant
    Dim rng         As Range
   Dim i           As Long

    With ThisWorkbook.Worksheets("Form Controls")
        Set rng = .Range("A1").CurrentRegion
       ' .Visible = xlSheetVeryHidden '< optional
    End With
    Set rng = rng.Offset(1).Resize(rng.Rows.Count - 1)
    arr = rng.Value2
    For i = 1 To UBound(arr, 1)
        Form.Controls(arr(i, 1)).Visible = arr(i, State)
    Next
End Sub

From your forms CODE Page

Private Sub OptionButton2_Click()
  
    ControlsVisible Me, State2
  
End Sub

Private Sub UserForm_Initialize()
    With Sheets("BDATOS")
        n = .Range("B" & Rows.Count).End(xlUp).Row
        va = .Range("G2:G" & n) 'ComboBox1 = By country of destination
        vb = .Range("H2:H" & n) 'ComboBox2 = By destination city
        vc = .Range("B2:B" & n) 'ComboBox3 = By name
    End With
    Set dar = CreateObject("System.Collections.Arraylist")
  
    ControlsVisible Me, State1
End Sub

Solution is untested & you may have to update the visible State of each control in the table as required.
Table can be expanded to include other permutations if needed

Dave

Thank you very much, Dave, as always a pleasure to have you and your always accurate help.

 

[dmt32]

Thank you very much, Dave, as always a pleasure to have you and your always accurate help.

many thanks for generous feedback it is very much appreciated but would add I don't always get it right - do be mindful that whilst not all contributors may produce a working solution they offer help in a generous spirit.

Dave