Search code examples
excelvbatextboxdropdownuserform

Dropdown Combobox and Textbox Generated on Userform with VBA

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

enter image description here

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, 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

enter image description here

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 CIF field 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 modified based on someone's comment in my other post

'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, "N").End(xlUp).Row 'Change column N with BDI column in case of you.
    Set rng = ActiveSheet.Range("N3:N" & lstrow)


    Me.ComboBox2.Clear
    For Each cel In rng
        If cel = strBDI Then
            Me.ComboBox2.AddItem cel.Offset(0, -13) '-13 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, "N").End(xlUp).Row 'Change column N with BDI column in case of you.
    Set rng = ActiveSheet.Range("N3:N" & lstrow)

    For Each cel In rng
        If cel = strBDI And cel.Offset(0, -13) = strCIF Then
            matchRow = cel.Row
            Exit For
        End If
    Next

    Me.TextBox1 = ActiveSheet.Cells(matchRow, 2)
    Me.TextBox2 = ActiveSheet.Cells(matchRow, 6)
    Me.TextBox3 = ActiveSheet.Cells(matchRow, 13)


End Sub

Private Sub cmdSave_Click()

    ActiveSheet.Cells(matchRow, 2) = Me.TextBox1
    ActiveSheet.Cells(matchRow, 6) = Me.TextBox2
    ActiveSheet.Cells(matchRow, 13) = 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 "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

But it still doesn't work as I wanted. Whenever I pick one of the BDI Industry Group, the CIF doesn't come out.

Any help would be greatly appreciated.

Thank you.

Solution

  • It looks that your (main) problem is the fact that there are "#NA" errors in the analyzed column and these rows must be escaped:

    In ComboBox1 Change event, I suggest you to use:

    Private Sub Combobox1_Change()
 Dim rng As Range, cel As Range, lstrow As Long, strBDI As String

    strBDI = Me.ComboBox1.Value

    lstrow = Worksheets("Lending & Funding").Cells(Worksheets("Lending & Funding") _
                 .Rows.Count, "N").End(xlUp).Row
    Set rng = Worksheets("Lending & Funding").Range("N3:N" & lstrow)
    Me.ComboBox2.Clear
    
    For Each cel In rng.Cells
        If Not IsError(cel.Value) Then
            If cel.Value = strBDI Then
                Me.ComboBox2.AddItem cel.Offset(0, -13) '-13 need to adjust with CIF column left from BDI column
            End If
        End If
    Next
End Sub

    and use the same way for ComboBox2 Event:

    Private Sub Combobox2_Change()
 Dim rng As Range, cel As Range, lstrow As Long
 Dim strBDI As String, strCIF As String

    strBDI = Me.ComboBox1.Value: strCIF = Me.ComboBox2.Value
    
    lstrow = Worksheets("Lending & Funding").Cells(Worksheets("Lending & Funding") _
       .Rows.Count, "N").End(xlUp).Row 'Change column N with BDI column in case of you.
    Set rng = Worksheets("Lending & Funding").Range("N3:N" & lstrow)

    For Each cel In rng
        If Not IsError(cel.Value) Then
            If cel = strBDI And cel.Offset(0, -13) = strCIF Then
                matchRow = cel.Row
                Exit For
            End If
        End If
    Next

    If matchRow > 0 Then 'sometimes, the above conditions may not return any match...
        Me.TextBox1 = Worksheets("Lending & Funding").Cells(matchRow, 2)
        Me.TextBox2 = Worksheets("Lending & Funding").Cells(matchRow, 6)
        Me.TextBox3 = Worksheets("Lending & Funding").Cells(matchRow, 13)
    Else
        MsgBox "There is no mathch for the chosen criteria..."
    End If
End Sub

    Then, the way of loading ComboBox1 (to manually set the unique values) is not the most efficient/appropriate...

    Try this approach, please:

    Private Sub UserForm_Initialize()
 Dim shLF As Worksheet, dict As New Scripting.Dictionary, lastRow As Long
 Dim I As Long, lastCol As Long, arr As Variant
 
 Set shLF = Worksheets("Lending & Funding")
 lastRow = shLF.Range("N" & Rows.Count).End(xlUp).Row
 lastCol = shLF.Cells(2, Columns.Count).End(xlToLeft).Column + 2
 
 For I = 3 To lastRow
    If Not dict.Exists(shLF.Range("N" & I).Value) Then
        dict.Add shLF.Range("N" & I).Value, 1
    End If
 Next I
 'sort the dictionary, load the sorted column in an array, clear the temporary range and load combo:
 shLF.Cells(1, lastCol).Resize(dict.Count, 1).Value = WorksheetFunction.Transpose(dict.Keys)
 With shLF.Range(shLF.Cells(1, lastCol), shLF.Cells(1, lastCol).Resize(dict.Count, lastCol))
    .Sort shLF.Cells(1, lastCol), xlAscending
    arr = .Value
    .Clear
 End With
 Me.ComboBox1.List = arr
 'clear zero BDI (if necessary):
 For I = 0 To Me.ComboBox1.ListCount - 1
    If Me.ComboBox1.List(I) = "0" Then Me.ComboBox1.RemoveItem (I): Exit For
 Next I
End Sub

    And it is better (I think) to show the form in a way to also be able to click, scroll, edit the page cells:

    Private Sub CommandButton2_Click()
    BDIIndustryGroup.Show vbModeless
End Sub

    Edited:

    The code for the 'SAVE' button, able to fill back the values you changed in the text boxes. It now, selects the saved row, in order to allow you checking the result...

    Private Sub CommandButton1_Click()
  Dim sh As Worksheet
  
  Set sh = Worksheets("Lending & Funding")

  sh.Cells(matchRow, 2) = Me.TextBox1
  sh.Cells(matchRow, 6) = Me.TextBox2
  sh.Cells(matchRow, 13) = Me.TextBox3

  sh.Cells(matchRow, 2).EntireRow.Select
  
  MsgBox "Data Saved Successfully!", vbInformation, "Save"
End Sub