ComboBox depending on previous selection

[Alvis]

Hello,

Hope all are well

Hoping you will be able to help me out on this one..

I'm creating supplier / customer database and I'm building "Update" button.

Basically I have the table of customers / suppliers and some of them are duplicated.

For example I have a company who deals with the printers and desktops but there is two different contact names to each of the category so we will have the information entered twice.

So example:

Company Name Alvis Desktops
Company Name Mike Printers

So what I would like the user form to do is:

Type customers name (Company name) and click search --> macro checks if that value already exists more than once (which I did already) so on this occasion macro finds two values for the same company just with two different variations which is the contact name and the hardware they are dealing with: Desktops or Printers.

So when user form finds value more than once it un-hides Combo Box which I would like to show the following options: Desktops or Printers - depending what information exists in the table (Basically to set up Combo Box as a 2nd filter to give me unique customer in the end which I would like to update it). Also it could be different hardware on the list.. Could be printers, HDD, desktop, monitor.. all depends when adding a new customer.. So I just wonder if possible for Combo Box to return the value by searching in the table depending on the Company Name.

Hope all this make sense to you guys

I've tried to find this elsewhere but couldn't find something similar to what I was looking for.

Many thanks in advance !

Kind Regards
Alvis

 

[Alvis]

No suggestions guys ?

 

[Fluff]

If your data is like

Excel 2013/2016
	A	B	C
1	Company Name	Contact	Device
2	Company A	Peter	printers
3	Company A	Paul	HDD
4	Company A	mary	fax
5	Company B	John	monitors
6	Company B	Paul	printers
7	Company B	George	desktops
8	Company B	Ringo	fax
9	Company C	Elton	monitors
Sheet1

Then you could use something like

[COLOR=#0000ff]Option Explicit
Dim Dic As Object
[/COLOR]
Private Sub ComboBox1_Click()
   Me.ComboBox2.Clear
   Me.ComboBox2.List = Dic(Me.ComboBox1.Value).Keys
End Sub

Private Sub UserForm_Initialize()
   Dim Cl As Range
   Dim Ws As Worksheet
   
   Set Ws = Sheets("Sheet1")
   Set Dic = CreateObject("Scripting.dictionary")
   For Each Cl In Ws.Range("A2", Ws.Range("A" & Rows.Count).End(xlUp))
      If Not Dic.Exists(Cl.Value) Then
         Dic.Add Cl.Value, CreateObject("scripting.dictionary")
         Dic(Cl.Value).Add (Cl.Offset(, 2).Value), Nothing
      Else
         Dic(Cl.Value)(Cl.Offset(, 2).Value) = Empty
      End If
   Next Cl
   Me.ComboBox1.List = Dic.Keys
End Sub

Lines in Blue must be at the very top of the module, before any code

 

[Alvis]

Mr Fluff !

Many thanks for your respond !

Yes it did the job for me ! Many thanks. Now back to programming, still have a lots to build ! lol

Many thanks and have a nice day

Regards
Alvis

 

[Fluff]

Glad to help & thanks for the feedback