Excel Combo box's one loads the other

[samzion]

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.

 

[DanteAmor]

Put the followig code in your userform

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

 

[samzion]

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.

 

[DanteAmor]

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.

You have to replace your code with mine.

Try the following file:

https://www.dropbox.com/s/df0b2tg8za80mfi/battery details.xlsm?dl=0

 

[samzion]

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.

 

[samzion]

I tried the following code and it started working. Thanks a lot for your help.

ThisWorkbook.Sheets("Purchase Tracker").Activate

 

[DanteAmor]

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.

 

[samzion]

Dear Mr.Dante Amor, Sorry that I am troubling you a lot. I will be very much thankful if you can help me in completing my project.

Here is my requirement:

In the Sales Entry form

1. As i click the add to cart button a seperate sub form should open where i can see the items added to cart. And if i want to remove an item i should be able to remove it.

2. A maximum of 3 items can be added to the cart. Anything beyond 3 items should not be allowed and should display a message "Maxmimum limit reached create new invoice".

3.Once on clicking finalize cart button... All the items added in the cart should move to the invoice (Sales entry) sheet.

4. Once I press the update tracker button all the items that was entered in the sales entry form should be updated in "Sales Tracker" sheet.


Thank again thank you and sorry for troubling you beyond the limit..

Updated workbook download link:

[url]https://drive.google.com/file/d/1TpMEV7UNY5do0u7V_9J13tgyrqnA-L-E/view?usp=sharing
[/URL]

 

[samzion]

Dear Mr.Dante Amor, Sorry that I am troubling you a lot. I will be very much thankful if you can help me in completing my project.

Here is my requirement:

In the Sales Entry form

1. As i click the add to cart button a seperate sub form should open where i can see the items added to cart. And if i want to remove an item i should be able to remove it.

2. A maximum of 3 items can be added to the cart. Anything beyond 3 items should not be allowed and should display a message "Maxmimum limit reached create new invoice".

3.Once on clicking finalize cart button... All the items added in the cart should move to the invoice (Sales entry) sheet.

4. Once I press the update tracker button all the items that was entered in the sales entry form should be updated in "Sales Tracker" sheet.


Thank again thank you and sorry for troubling you beyond the limit..

Updated workbook download link:

https://drive.google.com/file/d/1TpMEV7UNY5do0u7V_9J13tgyrqnA-L-E/view?usp=sharing

Please help me..