Excel Combo box's one loads the other

samzion

New Member
Joined
Aug 19, 2019
Messages
10
I am using 4 comboxbox's in my excel vba form "Purchase Entry". I would like the form combobox's to pull up records from a dynamic range ProductList sheet and also one combobox should load the other with the matching values. I was bit sucessfull in making my first combobox to pickup unique values. i need help in getting my other combobox's to pick up unique relavant values.

And moreover Purchase Tracker sheet should be kept visible at the background at all times though values are pulled from ProductList sheet.


Herewith i have shared the downloadable file link


https://drive.google.com/file/d/1d6CH2vUGJhrAIrJ08a1EUSyA9uyhakXM/view?usp=sharing

Please help me on this.
 

Excel Facts

How to fill five years of quarters?
Type 1Q-2023 in a cell. Grab the fill handle and drag down or right. After 4Q-2023, Excel will jump to 1Q-2024. Dash can be any character.
Put the followig code in your userform

Code:
Dim sh1 As Worksheet             '[COLOR=#0000ff]This line should go to the beginning of all the code![/COLOR]


Private Sub ComboBox1_Change()
  Dim a() As Variant, dict As Object, i As Long
  ComboBox2.Clear
  ComboBox3.Clear
  ComboBox4.Clear
  ComboBox2.Value = ""
  ComboBox3.Value = ""
  ComboBox4.Value = ""
  If ComboBox1 = "" Or ComboBox1.ListIndex = -1 Then Exit Sub
  '
  a = sh1.Range("A2", sh1.Range("B" & Rows.Count).End(xlUp))
  Set dict = CreateObject("scripting.dictionary")
  For i = 1 To UBound(a)
    If a(i, 1) = ComboBox1 Then
      If Not dict.exists(a(i, 2)) Then
        dict(a(i, 2)) = dict(a(i, 2))
        ComboBox2.AddItem a(i, 2)
      End If
    End If
  Next
End Sub


Private Sub ComboBox2_Change()
  Dim a() As Variant, dict As Object, i As Long
  ComboBox3.Clear
  ComboBox4.Clear
  ComboBox3.Value = ""
  ComboBox4.Value = ""
  If ComboBox2 = "" Or ComboBox2.ListIndex = -1 Then Exit Sub
  '
  a = sh1.Range("A2", sh1.Range("C" & Rows.Count).End(xlUp))
  Set dict = CreateObject("scripting.dictionary")
  For i = 1 To UBound(a)
    If a(i, 1) = ComboBox1 And a(i, 2) = ComboBox2 Then
      If Not dict.exists(a(i, 3)) Then
        dict(a(i, 3)) = dict(a(i, 3))
        ComboBox3.AddItem a(i, 3)
      End If
    End If
  Next
End Sub


Private Sub ComboBox3_Change()
  Dim a() As Variant, dict As Object, i As Long
  ComboBox4.Clear
  ComboBox4.Value = ""
  If ComboBox3 = "" Or ComboBox3.ListIndex = -1 Then Exit Sub
  '
  a = sh1.Range("A2", sh1.Range("D" & Rows.Count).End(xlUp))
  Set dict = CreateObject("scripting.dictionary")
  For i = 1 To UBound(a)
    If a(i, 1) = ComboBox1 And a(i, 2) = ComboBox2 And a(i, 3) = ComboBox3 Then
      If Not dict.exists(a(i, 4)) Then
        dict(a(i, 4)) = dict(a(i, 4))
        ComboBox4.AddItem a(i, 4)
      End If
    End If
  Next
End Sub


Private Sub CommandButton1_Click()
  LastRow = ThisWorkbook.Worksheets("Purchase Tracker").Cells(Rows.Count, 1).End(xlUp).Row
  ThisWorkbook.Worksheets("Purchase Tracker").Cells(LastRow + 1, 2).Value = DTPicker1.Value
  ThisWorkbook.Worksheets("Purchase Tracker").Cells(LastRow + 1, 3).Value = ComboBox1.Text
  ThisWorkbook.Worksheets("Purchase Tracker").Cells(LastRow + 1, 4).Value = ComboBox2.Text
  ThisWorkbook.Worksheets("Purchase Tracker").Cells(LastRow + 1, 5).Value = ComboBox3.Text
  ThisWorkbook.Worksheets("Purchase Tracker").Cells(LastRow + 1, 6).Value = ComboBox4.Text
  ThisWorkbook.Worksheets("Purchase Tracker").Cells(LastRow + 1, 7).Value = ComboBox5.Text
  ThisWorkbook.Worksheets("Purchase Tracker").Cells(LastRow + 1, 1).Value = TextBox1.Text
  ThisWorkbook.Worksheets("Purchase Tracker").Cells(LastRow + 1, 8).Value = ComboBox6.Text
  ThisWorkbook.Worksheets("Purchase Tracker").Cells(LastRow + 1, 9).Value = TextBox2.Text
  ThisWorkbook.Worksheets("Purchase Tracker").Cells(LastRow + 1, 10).Value = TextBox3.Text
  ThisWorkbook.Worksheets("Purchase Tracker").Cells(LastRow + 1, 11).Value = TextBox4.Text
  ThisWorkbook.Worksheets("Purchase Tracker").Cells(LastRow + 1, 12).Value = ComboBox7.Text
  ThisWorkbook.Worksheets("Purchase Tracker").Cells(LastRow + 1, 13).Value = ComboBox8.Text
  ThisWorkbook.Worksheets("Purchase Tracker").Cells(LastRow + 1, 14).Value = TextBox5.Text
End Sub


Private Sub CommandButton2_Click()
  ThisWorkbook.Sheets("Main").Activate
  Unload Me
End Sub


Private Sub UserForm_Activate()
  Dim a() As Variant, dict As Object, i As Long
  '
  For Each ctl In Me.Controls
    If TypeName(ctl) = "TextBox" Or TypeName(ctl) = "ComboBox" Then ctl.Value = ""
  Next ctl
  '
  Set sh1 = Sheets("ProductList")
  a = sh1.Range("A2", sh1.Range("A" & Rows.Count).End(xlUp))
  Set dict = CreateObject("scripting.dictionary")
  For i = 1 To UBound(a)
    If Not dict.exists(a(i, 1)) Then
      dict(a(i, 1)) = dict(a(i, 1))
      ComboBox1.AddItem a(i, 1)
    End If
  Next
End Sub
 
Upvote 0
Thanks a lot for your extended help. But unfortunately code is throwing lot of error messages when i paste it in my system. As a beginner i am unable to fix all those error messages. Could you please share with me the updated excel file through a google drive downloadable link.

Once again thanks a lot for your help.
 
Upvote 0
Upvote 0
Thank you soo... much for your help. I am greatly indebted to you. One last thing in this thread, as soon as the purchase entry form loads i want the purchase tracker sheet to be visible at the background and not the main sheet (though we pull records from productlist sheet). Please help me on this.
 
Upvote 0
I tried the following code and it started working. Thanks a lot for your help.

ThisWorkbook.Sheets("Purchase Tracker").Activate
 
Upvote 0
I tried the following code and it started working. Thanks a lot for your help.

ThisWorkbook.Sheets("Purchase Tracker").Activate

Im glad to help you. Thanks for the feedback.
 
Upvote 0

Forum statistics

Threads
1,213,492
Messages
6,113,967
Members
448,537
Latest member
Et_Cetera

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top