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.

Harun24hr · Accepted Answer · 2020-07-24 05:01:28Z

2

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

answered Jul 24, 2020 at 5:01

Harun24hr

38.1k6 gold badges24 silver badges47 bronze badges

Sign up to request clarification or add additional context in comments.

5 Comments

Rendi Julian Over a year ago

Hi @Harun24HR, thank you so much for your help. I really appreciate it. The thing is the screenshot of my data is just a small part of the database. There are 7504 rows on the sheet itself. I already try to change it to TRANSPORTATION on VBA code and also enter it on the combobox but the CIF doesn't work out and the dropdown is still empty. Could you suggest any change in your given code?

Rendi Julian Over a year ago

I already put it on the post above noticing you are the initiator of the code. I just change and add the Me.Combobx1.AddItem with several items based on my data. Then, I aso change the range since the header starts from A2. Thank you so much.

Harun24hr Over a year ago

@RendiJulian I tested your code and just works fine to me. Can you please share a sample workbook via google drive or dropbox so that I can check.

TinMan Over a year ago

You should avoid ActiveSheet. Other than that it is pretty solid. The OP's use of a named range is very cleaver. By using the named range you can reference the data no matter where it may be (e,g, Sheets.Range("BDIIndustryGroup").CurrentRegion`. Anyway, I'm glad that I reviewed your code because it made me realize I had a mistake in my original draft.

Rendi Julian Over a year ago

@Harun24HR This is the sample workbook that I revised and uploaded on google drive drive.google.com/file/d/1rIDuijBr6dqgsYBjEBs94ja9eTuI2Jiw/… Hopefully it will be useful. Thanks in advance.

Collectives™ on Stack Overflow

Generating Combobox and Editing Textbox on Userform using VBA

1 Answer 1

5 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

5 Comments

Your Answer

Sign up or log in

Post as a guest

Related