0

The idea is to build a userform that have numerous "lines" of two comboboxes to choose from: one let choose a category (main filed) and the second one (dependent filed) let you choose subcategory for the category user have chosen in main field.

Number of this comboboxes pairs may be variable and chosen by user - for adding or deleting line of two comboboxes there are buttons on the top. When pressing "Add Field" button, userform adds new pair at the bottom and gives each combobox name with ordinal number at the end, e.g.:

  • Main_field_1, Dependent_field_2;
  • Main_field_2, Dependent_Field_2

and so on.

Visual example of userform

But there is a problem: if no name of combobox is predetermined, how can a code be written for them? For example I want to register a change in Main_field to be able to run a code, that will fill in data for Dependent_field, based on Main_field value. But if this comboboxes were created by user with "Add field" button there was no code written for them like:

Private Sub Main_field_1_Change()
    Select Case UserForm1.Main_field_1.Value
        Case "Category1"
            UserForm1.Dependent_field_1.AddItem "SubCategory_1"
            UserForm1.Dependent_field_1.AddItem "SubCategory_2"
        Case "Category2"
            UserForm1.Dependent_field_1.AddItem "SubCategory_88"
            UserForm1.Dependent_field_1.AddItem "SubCategory_99"
        Case Else
    End Select
End Sub

because name of main and dependent fields have variables in them, like Main_field_999 and Dependent_field_999 (the numbers at the end will be identical for each line) and as i understand there is code can exist like this:

Private Sub Main_field_XX_Change()
    Select Case UserForm1.Main_field_XX.Value
        Case "Category1"
            UserForm1.Dependent_field_XX.AddItem "SubCategory_1"
            UserForm1.Dependent_field_XX.AddItem "SubCategory_2"
        Case "Category2"
            UserForm1.Dependent_field_XX.AddItem "SubCategory_88"
            UserForm1.Dependent_field_XX.AddItem "SubCategory_99"
        Case Else
    End Select
End Sub

The question is: how can a code be written to recognize a change in combobox, that have a variable name to then execute different piece of code, based on value in this combobox?

315
  • 1
  • 1

0 Answers0