Generating Combobox and Editing Textbox on Userform using VBA

Question

Basically, I have a dialog box that I want to make

If you can see above, I want to make the combobox applied on Search by BDI Industry Group and CIF.

Then, whenever I clicked the Search by Industry Group combobox and choose one, then the CIF field will give you another dropdown to choose, and then, the rest of other fields will be automatically filled based on the data below

The screenshot above is just a small part of my database. There are 7504 rows in total and the header starts from A2.

Let's say I pick Transportation

Then I want the dialog box shows me dropdown either CIF 13039099 and 12901262 to choose.

Next, for example, when I picked CIF 13039099, the rest of other fields will show the information directly and automatically based on the database, which is:

• Client Name: Adit Jaya Mandiri' CV
• RM Name: Irawan Noor
• Segment: Commercial

And I want Client Name, RM Name, and Segment are editable.

After that when I click Save, It will be automatically updated based on the latest change. Is this possible to execute? Below is the VBA code which I made

Private Sub ComboBox1_Change()

Me.ComboBox2 = ""
 
Select Case Me.ComboBox1

    Case "BDIIndustryGroup"

        Me.ComboBox2.RowSource = "BDIIndustryGroup"

   
    Case "CIF"

        Me.ComboBox2.RowSource = "CIF"


    Case "Client Name"

        Me.ComboBox2.RowSource = "CLientName"

   
    Case "RM Name"

        Me.ComboBox2.RowSource = "RM Name"
   

    Case "Segment"

        Me.ComboBox2.RowSource = "Segment"

End Select

End Sub


Private Sub UserForm_Initialize()

Dim lastrow As Long

Dim lastcolumn As Long

lastcolumn = ActiveSheet.Cells(2, Columns.Count).End(xlToLeft).Column

With Worksheets("sheet2")


    For I = 1 To lastcolumn

        With .Columns(I)

        lastrow = Sheet2.Cells(Rows.Count, I).End(xlUp).Row

            With Range(Cells(1, I), Cells(lastrow, I))

            Range(Cells(1, I), Cells(lastrow, I)).Select

            Selection.CreateNames Top:=True

            End With

        End With

    Next I

End With


Me.ComboBox1.RowSource = "BDIIndustryGroup"
      

End Sub

 

Private Sub CommandButton2_Click()

Unload Me

End Sub

I tried this code but it's not working as I wanted.

I also got second alternative code from @Harun24HR but the CIF dropdown cannot be generated based on BDI Industry group.

'Codes to form

Option Explicit

Private matchRow As Long

Private Sub Combobox1_Change()

Dim rng As Range, cel As Range

Dim lstrow As Long

Dim strBDI As String

 

    strBDI = Me.ComboBox1

   

    lstrow = Cells(ActiveSheet.Rows.Count, "E").End(xlUp).Row 'Change column E with BDI Industry Group column in case of you.

    Set rng = ActiveSheet.Range("E3:E" & lstrow)

   

    Me.ComboBox2.Clear

    For Each cel In rng

        If cel = strBDI Then

            Me.ComboBox2.AddItem cel.Offset(0, -4) '-4 need to adjust with CIF column left from BDI column

        End If

    Next

 

End Sub

Private Sub Combobox2_Change()

Dim rng As Range, cel As Range

Dim lstrow As Long

Dim strBDI As String

Dim strCIF As String

 

    strBDI = Me.ComboBox1

    strCIF = Me.ComboBox2

   

    lstrow = Cells(ActiveSheet.Rows.Count, "E").End(xlUp).Row 'Change column E with BDI Group Industry column in case of you.

    Set rng = ActiveSheet.Range("E3:E" & lstrow)

   

    For Each cel In rng

        If cel = strBDI And cel.Offset(0, -4) = strCIF Then

            matchRow = cel.Row

            Exit For

        End If

    Next

   

    Me.TextBox1 = ActiveSheet.Cells(matchRow, 2)

    Me.TextBox2 = ActiveSheet.Cells(matchRow, 3)

    Me.TextBox3 = ActiveSheet.Cells(matchRow, 4)

   

End Sub

Private Sub cmdSave_Click()

    ActiveSheet.Cells(matchRow, 2) = Me.TextBox1

    ActiveSheet.Cells(matchRow, 3) = Me.TextBox2

    ActiveSheet.Cells(matchRow, 4) = Me.TextBox3

   

    MsgBox "Data Saved Succesfully!", vbInformation, "Save"

   

End Sub

Private Sub UserForm_Initialize()

    'Add items manually or use sub to add unique items from BDI Industry Group column

    Me.ComboBox1.AddItem "AUTOMOTIVE ATPM & DEALER"

    Me.ComboBox1.AddItem "AUTOMOTIVE COMPONENT"

    Me.ComboBox1.AddItem "CABLE"

    Me.ComboBox1.AddItem "CEMENT"

    Me.ComboBox1.AddItem "CHEMICAL"

    Me.ComboBox1.AddItem "chemical & PLASTICS"

    Me.ComboBox1.AddItem "COAL INDUSTRY"

    Me.ComboBox1.AddItem "COMPUTER & RELATED"

    Me.ComboBox1.AddItem "CONSTRUCTION"

    Me.ComboBox1.AddItem "COSMETICS & TOILETRIES"

    Me.ComboBox1.AddItem "CPO INDUSTRY"

    Me.ComboBox1.AddItem "ELECTRICITY & POWER PLANT"

    Me.ComboBox1.AddItem "ENGINE MACHINERIES & TOOLS"

    Me.ComboBox1.AddItem "FARMING & ANIMAL FEED"

    Me.ComboBox1.AddItem "FINANCIAL SERVICES"

    Me.ComboBox1.AddItem "FISHERIES"

    Me.ComboBox1.AddItem "FOOD & BEVERAGE"

    Me.ComboBox1.AddItem "FORESTRY & WOOD PRODUCT"

    Me.ComboBox1.AddItem "FURNITURE"

    Me.ComboBox1.AddItem "GARMENT"

    Me.ComboBox1.AddItem "HOME APPLIANCES"

    Me.ComboBox1.AddItem "HOSPITAL & HEALTHCARE"

    Me.ComboBox1.AddItem "HOTEL & ACCOMMODATION SERVICE"

    Me.ComboBox1.AddItem "INFRASTRUCTURE"

    Me.ComboBox1.AddItem "MEDICAL EQUIPMENT"

    Me.ComboBox1.AddItem "METAL NON STEEL"

    Me.ComboBox1.AddItem "MINING & QUARRING"

    Me.ComboBox1.AddItem "OIL & GAS - DOWNSTREAM"

    Me.ComboBox1.AddItem "OIL & GAS - UPSTREAM"

    Me.ComboBox1.AddItem "OTHERS"

    Me.ComboBox1.AddItem "PACKAGING & CORRUGATED"

    Me.ComboBox1.AddItem "PETROCHEMICAL & PLASTICS"

    Me.ComboBox1.AddItem "PHARMACEUTICAL"

    Me.ComboBox1.AddItem "PRINTING"

    Me.ComboBox1.AddItem "PROPERTIES & REAL ESTATE"

    Me.ComboBox1.AddItem "PULP & PAPER"

    Me.ComboBox1.AddItem "RENTAL SERVICES"

    Me.ComboBox1.AddItem "RETAILER"

    Me.ComboBox1.AddItem "RUBBER INDUSTRY"

    Me.ComboBox1.AddItem "SERVICE INDUSTRY"

    Me.ComboBox1.AddItem "SHIPPING"

    Me.ComboBox1.AddItem "STEEL"

    Me.ComboBox1.AddItem "SUGAR INDUSTRY"

    Me.ComboBox1.AddItem "TELECOMMUNICATION"

    Me.ComboBox1.AddItem "TEXTILE"

    Me.ComboBox1.AddItem "TOBACCO & CIGARETTE"

    Me.ComboBox1.AddItem "TRANSPORTATION"

    Me.ComboBox1.AddItem "WHOLESALES TRADING"

End Sub

Any help would be greatly appreciated.

Thank You.

Answer 1

As per below screenshot control name setting try following codes.

'Codes to form.
Option Explicit
Private matchRow As Long

Private Sub ComboBox1_Change()
Dim rng As Range, cel As Range
Dim lstRow As Long
Dim strBDI As String

    strBDI = Me.ComboBox1
    
    lstRow = Cells(ActiveSheet.Rows.Count, "E").End(xlUp).Row 'Change column E with BDI column in case of you.
    Set rng = ActiveSheet.Range("E2:E" & lstRow)
    
    Me.ComboBox2.Clear
    For Each cel In rng
        If cel = strBDI Then
            Me.ComboBox2.AddItem cel.Offset(0, -4) '-4 need to adjust with CIF column left from BDI column
        End If
    Next
    
End Sub

Private Sub ComboBox2_Change()
Dim rng As Range, cel As Range
Dim lstRow As Long
Dim strBDI As String
Dim strCIF As String

    strBDI = Me.ComboBox1
    strCIF = Me.ComboBox2
    
    lstRow = Cells(ActiveSheet.Rows.Count, "E").End(xlUp).Row 'Change column E with BDI column in case of you.
    Set rng = ActiveSheet.Range("E2:E" & lstRow)
    
    For Each cel In rng
        If cel = strBDI And cel.Offset(0, -4) = strCIF Then
            matchRow = cel.Row
            Exit For
        End If
    Next
    
    Me.TextBox1 = ActiveSheet.Cells(matchRow, 2)
    Me.TextBox2 = ActiveSheet.Cells(matchRow, 3)
    Me.TextBox3 = ActiveSheet.Cells(matchRow, 4)
End Sub

Private Sub cmdSave_Click()
     ActiveSheet.Cells(matchRow, 2) = Me.TextBox1
     ActiveSheet.Cells(matchRow, 3) = Me.TextBox2
     ActiveSheet.Cells(matchRow, 4) = Me.TextBox3
     
     MsgBox "Data Saved Successfully!", vbInformation, "Save"
End Sub

Private Sub UserForm_Initialize()
    'Add items manually or use sub to add unique items from BDI column.
    Me.ComboBox1.AddItem "Transport"
    Me.ComboBox1.AddItem "Food"
End Sub