Validation in combobox items - vba

kelly mort

Well-known Member
Joined
Apr 10, 2017
Messages
1,890
Office Version
  1. 2016
Platform
  1. Windows
This is how I am loading one of my comboboxes. But I am anticipating something that I want to avoid in the future.
A situation where I try selecting a product that has already been selected in one of the product comboboxes.
So the category comboboxes are named cat1 to cat10 and the product comboboxes are also from prod1 to prod10

A category has series of products assigned to it. Which means that the category comboboxes can have same category repeating itself like I have the multiple x in this example.
Image_support-2.jpg
Image_support-1.jpg


But I don’t want same products appearing in the product comboboxes. Since I don’t really know which of the comboboxes will be having the product that I want to avoid its duplication, it is giving me some headache here and I need some help with it.

Code:
Private Sub cat1_Change()
    If Len(cat1) Then
        prod1.Enabled = True
    Else
        prod1.Enabled = False
    End If
    
    Dim e_prod As Range
    prod1.Clear
    For Each e_prod In ThisWorkbook.Names("PRODUCT_LIST").RefersToRange.Columns(1).Cells
        If e_prod.Offset(, 1) = cat1 Then
            prod1.AddItem e_prod.Value
        End If
    Next e_prod
End Sub


Thanks in advance
 

Excel Facts

Highlight Duplicates
Home, Conditional Formatting, Highlight Cells, Duplicate records, OK to add pink formatting to any duplicates in selected range.

DanteAmor

Well-known Member
Joined
Dec 3, 2018
Messages
12,619
Office Version
  1. 2007
Platform
  1. Windows
This code is with another approach.

Replace all your code with this.

The code loads the categories in the dictionary (declared at the beginning of all code) key and in the dictionary item loads the products.
When you select a category then read the dictionary of that category and get the products.

VBA Code:
Option Explicit       'At the beginning of the whole code
Dim dic As Object

Private Sub Cat1_Change()
  Call LoadProducts(1)
End Sub
Private Sub Cat2_Change()
  Call LoadProducts(2)
End Sub
Private Sub Cat3_Change()
  Call LoadProducts(3)
End Sub
'and so on...
'
Sub LoadProducts(n As Long)
  Dim cmb As MSForms.ComboBox
  Set cmb = Me.Controls("Prod" & n)
  cmb.Value = ""
  cmb.Clear
  With Me.Controls("Cat" & n)
    If .ListIndex = -1 Or .Value = "" Then Exit Sub
    cmb.List = Split(Left(dic(.Value), Len(dic(.Value)) - 1), "|")
  End With
End Sub

Private Sub UserForm_Activate()
  Dim a As Variant
  Dim i As Long
  
  Set dic = CreateObject("Scripting.Dictionary")
  a = Sheets("PRODUCT_LIST").Range("B2", Sheets("PRODUCT_LIST").Range("C" & Rows.Count).End(3)).Value2
  For i = 1 To UBound(a, 1)
    dic(a(i, 2)) = dic(a(i, 2)) & a(i, 1) & "|"
  Next
  
  For i = 1 To 10
    Me.Controls("Cat" & i).List = Application.Transpose(dic.keys)
  Next
End Sub
 

kelly mort

Well-known Member
Joined
Apr 10, 2017
Messages
1,890
Office Version
  1. 2016
Platform
  1. Windows
@DanteAmor,
You are great!

Your code has taken care of more lines that I have been writing.

I am thinking of using a for loop to check each of the comboboxes to see if a selected product already exists in any of them.

So far that's the best shot I can give. If there is a cleaner way I will be very grateful to know.

I really miss you. Have a wonderful time and weekend.
 

DanteAmor

Well-known Member
Joined
Dec 3, 2018
Messages
12,619
Office Version
  1. 2007
Platform
  1. Windows
Your code has taken care of more lines that I have been writing.
I do not understand. Did you try the code?

I am thinking of using a for loop to check each of the comboboxes to see if a selected product already exists in any of them.
I do not understand. Are you discarding my code?
Could you explain what problem my code has.
Or if my code doesn't do what you need, then you could explain what your goal is, with examples.

So far that's the best shot I can give. If there is a cleaner way I will be very grateful to know.
And finally, I don't understand what you mean either.
 

kelly mort

Well-known Member
Joined
Apr 10, 2017
Messages
1,890
Office Version
  1. 2016
Platform
  1. Windows

ADVERTISEMENT

I tried the code and I am using it. What it is not doing is that when I select X from cat1 combobox, and make a selection of say SEPTEMBER in prod1 combobox , then inside cat2 if I select X again, I want SEPTEMBER to vanish from the list of products in prod2 combobox
GUDE_CODE-1.jpg


And that's what I was talking about using the for loop to address.
 

DanteAmor

Well-known Member
Joined
Dec 3, 2018
Messages
12,619
Office Version
  1. 2007
Platform
  1. Windows
I want SEPTEMBER to vanish from the list of products in prod2 combobox

Change this part of the code:
VBA Code:
Sub LoadProducts(n As Long)
  Dim cmb As MSForms.ComboBox
  Dim i As Long
  Dim ct As Variant
  Dim agregar As Boolean
  '
  Set cmb = Me.Controls("Prod" & n)
  cmb.Value = ""
  cmb.Clear
  With Me.Controls("Cat" & n)
    If .Value = "" Or .ListIndex = -1 Then
      Exit Sub
    End If
    For Each ct In Split(Left(dic(.Value), Len(dic(.Value)) - 1), "|")
      agregar = True
      For i = 1 To 10
        If Me.Controls("Prod" & i).Value = ct Then agregar = False
      Next
      If agregar Then cmb.AddItem ct
    Next
  End With
End Sub

With the above you solve a problem.
But your intention goes further. What about the "Prod1" combo when you choose March in the "Prod2", then in the "Prod1" combo you should vanish "March".
Or what if you delete a category and that category already had a Product, then all combos should now show that product, because it is already available.

You should think of an alternative solution as the algorithm to recalculate all the possibilities is getting more and more complex.

Question: Why do you want to have 10 category combos and 10 product combos?
That is not good practice, you should have only one combo for category and only one as for product.
And every time you have the couple selected, pass it to a listbox. That way you would only be controlling one combo and not 10.
 

kelly mort

Well-known Member
Joined
Apr 10, 2017
Messages
1,890
Office Version
  1. 2016
Platform
  1. Windows

ADVERTISEMENT

Your questions are all valid.
And this is how I am validating the duplication of product selection.
If there is a better way around it, I would be very glad to have it.

This code is inside the change event of the
Code:
For i = 1 To 10
            Select Case i
                Case 1
                Case Else
                    If Me.Controls("prod" & i) = prod1 Then
                        MsgBox prod1 & " is already selected", vbExclamation, "Duplicate alert"
                        prod1 = ""
                        Exit Sub
                    End If
            End Select
        Next i
And with the issue of using more than 1 comboboxes for the products and categories, I did that in order to bypass certain forms of repetition of the data entry process – though I anticipated it is not a good practice as you have pointed out. But I want to maintain it for the mean time.
 

DanteAmor

Well-known Member
Joined
Dec 3, 2018
Messages
12,619
Office Version
  1. 2007
Platform
  1. Windows
that's to select.
did you solve it when you delete the previously selected data?
 

kelly mort

Well-known Member
Joined
Apr 10, 2017
Messages
1,890
Office Version
  1. 2016
Platform
  1. Windows
that's to select.
did you solve it when you delete the previously selected data?

I have not placed the codes for adding, editing or deleting data yet so I can't verify that atm.

I am only working on areas that I see as challenging for now.
Will work it out and report.

Thanks
 

kelly mort

Well-known Member
Joined
Apr 10, 2017
Messages
1,890
Office Version
  1. 2016
Platform
  1. Windows
@DanteAmor
I just run into a problem.

I want to use just one combobox for the category and one for the product.
This is for a different form.
So the category combobox will have the items x, y, z

And when I switch it, the product combobox should get the related products.

Since the above code was for multiple comboboxes, I am finding it difficult to amend your code to my needs.

I would be very glad if you or anyone could fix it for me.

Thanks
 

Watch MrExcel Video

Forum statistics

Threads
1,129,410
Messages
5,636,118
Members
416,900
Latest member
Mokor

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
Top