COMBO BOX with INDEX/MATCH. Arrange COMBO BOX A to Z, with no repetition, only relevant?

[srands]

FILE hyperlink below is spreadsheet of COMBO BOXs
http://www.1sar.karoo.net/exoftable0.xls file size 111kb's

It is a simple DATABASE of CARS, with a RESULTS PAGE, much like an excel version of AUTOTRADER, a basic example without photos & descriptions.

The Combo Boxs are:
COLUMN A: CAR MAKE
COLUMN B: CAR MODEL
COLUMN C: CAR TYPE
COLUMN D: CAR COLOUR

The criteria of Combo Box's in 'ComboBox CHOICE & RESULTS', sorts results from 'WORKSHEET'.

My spreadsheet looks ok, however the combo box's I've made lack the functionality I need, and to edit them further I'm unfamiliar with, hence they need editing to:

i) Arrange COMBO BOXs to display in Alphabetical order, A to Z of available fields

ii) or even better, arrange COMBO BOX A to Z, with no repetition

iii) Combo Box's following from Previous, left to right, only to display relevant fields possible based on previous selections , not all fields in that column listed in COMBO BOX, PROPERTIES, ListFillRange, some examples below:

Example 1:
COLUMN A: If PEUGEOT is selected, then in
COLUMN B: The only choices would be 206 or 207 or 306 or 307
etc

or

Example 2:
COLUMN A: If PEUGEOT is selected, then in
COLUMN B: If 206 is selected, then in
COLUMN C: only HDi would be available, then in
COLUMN D: The only choices would be SILVER or BLUE

Perhaps there are better, also easier to use spreadsheets, obviously the WORKSHEET spreadsheet is very good, and the autofilter is fast & effective at the same tasks described here, however I want a COMBO BOX spreadsheet without seeing the total full list of entries in the WORKSHEET.

However I haven't seen/found or worked with such an example so far. My intention is that copies for other purposes can easily changed, by changing the titles, and then results worksheet sort is left unchanged hence will only display fields to choose from.

 

[srands]

Right click new sheet Tab , Select "View Code", VBwindow appears, Paste entire code into window.

=============

Option Explicit
Dim Dic As Object
Private Sub Worksheet_Activate()
Dim Rng As Range, Dn As Range, n As Long
Dim Q
With Sheets("Worksheet")
    Set Rng = .Range(.Range("A2"), .Range("A" & Rows.Count).End(xlUp))
End With
ReDim Ray(1 To Rng.Count, 1 To 3)
    Set Dic = CreateObject("scripting.dictionary")
        Dic.CompareMode = vbTextCompare
For Each Dn In Rng
    If Not Dic.Exists(Dn.Value) Then
        Ray(1, 1) = Dn(, 2)
        Ray(1, 2) = Dn(, 3)
        Ray(1, 3) = Dn(, 18)
        Dic.Add Dn.Value, Array(Ray, 1)
    Else
        Q = Dic.Item(Dn.Value)
        Q(1) = Q(1) + 1
        Q(0)(Q(1), 1) = Dn(, 2)
        Q(0)(Q(1), 2) = Dn(, 3)
        Q(0)(Q(1), 3) = Dn(, 18)
        Dic.Item(Dn.Value) = Q
    End If
Next
Dim nRay
Dim i As Long
Dim j As Long
Dim Temp As String
nRay = Dic.Keys
For i = 0 To UBound(nRay)
    For j = i To UBound(nRay)
        If nRay(j) < nRay(i) Then
            Temp = nRay(i)
            nRay(i) = nRay(j)
            nRay(j) = Temp
        End If
    Next j
Next i
ComboBox1.List = nRay
ComboBox1.ListIndex = 0
End Sub
Private Sub ComboBox1_Change()
Dim DiCB As Object
Dim n As Integer
Dim K
Dim g
ComboBox2.Clear
ComboBox3.Clear
ComboBox4.Clear
For n = 2 To 4
Set DiCB = CreateObject("scripting.dictionary")
    DiCB.CompareMode = vbTextCompare
        If Dic.Exists(ComboBox1.Value) Then
            For g = 1 To Dic.Item(ComboBox1.Value)(1)
                DiCB(Dic.Item(ComboBox1.Value)(0)(g, n - 1)) = Empty
            Next g
        Dim nRay
Dim i As Long
Dim j As Long
Dim Temp As String
nRay = DiCB.Keys
For i = 0 To UBound(nRay)
    For j = i To UBound(nRay)
        If nRay(j) < nRay(i) Then
            Temp = nRay(i)
            nRay(i) = nRay(j)
            nRay(j) = Temp
        End If
    Next j
Next i
ActiveSheet.OLEObjects("Combobox" & n).Object.List = nRay
  End If
Next n
End Sub

Hi Mick thanks, but that doesn't work with exisiting or new spreadsheet, error message, 'Ambiguous name detected: ComboBox1_Change.

Can you add your new A-Z code to the lastest version? Link here [url]http://www.1sar.karoo.net/exoftable0a.xls[/URL]

 

[MickG]

Your Linked Workbook has new Modified Code
Look Here :-

https://www.box.com/s/i2cpqxrjm1i05fm4iato

 

[srands]

Your Linked Workbook has new Modified Code
Look Here :-

https://www.box.com/s/i2cpqxrjm1i05fm4iato

Hi Mick, that's great.

1 problem, the only comment I could make is that when there are cars of the same MODEL, then TYPE includes non-relevant fields, that it displays for that car MAKE all possible TYPES.

For example there is more then 1 PEUGEOT 206, there are 3 to be exact.
In worksheet all these PEUGOET 206's are TYPE: HDi.
HOWEVER when selecting CAR MAKE: PEUGEOT and MODEL: 206 then wrongly TYPE displays:
GTi
TD
HDi

For some reason TYPE has shown all possible TYPES for PEUGEOT, not just for PEUGEOT (COLUMN A CAR MAKE) and 206 (COLUMN B CAR MODEL).
It does this error when there are 2+ same MODELS (COLUMN B).

ANY IDEAS? A code fix for this MICK?

 

[srands]

Your Linked Workbook has new Modified Code
Look Here :-
https://www.box.com/s/i2cpqxrjm1i05fm4iato

In other much clearer words MICK the problem is:
For CAR MAKE (Column A) with a number of MODELS (Column B),
then TYPE (Column C) displays all possible TYPES for that CAR MAKE (Column A).

Eg: Vauxhall there are 2 entries:
i) Vauxhall, Astra, VXR
ii) Vauxhall, Nova, SRi

When searching
MAKE: VAUXHALL
MODEL: ASTRA
Then wrongly TYPE displays:
SRI
VXR

Here is another example of that error in more detail, in other words when there are cars of the same MAKE, then for each MODEL, then TYPE includes non-relevant fields, that it displays for that car MAKE all possible TYPES.
For example there is more then 1 PEUGEOT 206, there are 3 to be exact.
In worksheet all these PEUGOET 206's are TYPE: HDi.

HOWEVER when selecting CAR MAKE: PEUGEOT and MODEL: 206 then wrongly TYPE displays:
GTi
TD
HDi

For some reason TYPE has shown all possible TYPES for PEUGEOT, not just for PEUGEOT (COLUMN A CAR MAKE) and 206 (COLUMN B CAR MODEL).
It does this error when there are 2+ same MODELS (COLUMN B).

ANY IDEAS? A code fix for this MICK?

 

[MickG]

I could arrange combobox1 "Car" to shop each "Make/Type" Then the remaining comboboxes would show the right information relating to that "Make/Type"

 

[srands]

I could arrange combobox1 "Car" to shop each "Make/Type" Then the remaining comboboxes would show the right information relating to that "Make/Type"

Hi Mick, that would be great. Sounds like the way to resolve the error, I described in my most recent post.

I honestly thought A-Z sort, and relevant sort depending on previous combo box choices, would be a choice already listed with properties, edited in vb.

 

[MickG]

Right click the Sheet tag of the sheet named "Combobox" .select "View Code".
Vbwindow appears with code.
Alter the Code below as shown in red, this will either be a replacement bit or an alteration.
(Not much to do really !!)
When you Reselect this sheet, Combobox "Car" should show "Car/Model" and the other comboboxes should alter when a new selection is made

Private Sub Worksheet_Activate()
Dim Rng As Range, Dn As Range, n As Long
Dim Q
Dim Twn As String
With Sheets("Worksheet")
    Set Rng = .Range(.Range("A2"), .Range("A" & Rows.Count).End(xlUp))
End With
ReDim Ray(1 To Rng.Count, 1 To 3)
    Set Dic = CreateObject("scripting.dictionary")
        Dic.CompareMode = vbTextCompare
For Each Dn In Rng
    Twn = Dn & " / " & Dn(, 2)
    If Not Dic.Exists(Twn) Then
        Ray(1, 1) = Dn(, 2)
        Ray(1, 2) = Dn(, 3)
        Ray(1, 3) = Dn(, 18)
        Dic.Add Twn, Array(Ray, 1)
    Else
        Q = Dic.Item(Twn)
        Q(1) = Q(1) + 1
        Q(0)(Q(1), 1) = Dn(, 2)
        Q(0)(Q(1), 2) = Dn(, 3)
        Q(0)(Q(1), 3) = Dn(, 18)
        Dic.Item(Twn) = Q
    End If
Next
Dim nRay
Dim i As Long
Dim j As Long
Dim Temp As String
nRay = Dic.Keys
For i = 0 To UBound(nRay)
    For j = i To UBound(nRay)
        If nRay(j) < nRay(i) Then
            Temp = nRay(i)
            nRay(i) = nRay(j)
            nRay(j) = Temp
        End If
    Next j
Next i
ComboBox1.List = nRay
ComboBox1.ListIndex = 0
End Sub

 

[srands]

When you Reselect this sheet, Combobox "Car" should show "Car/Model" and the other comboboxes should alter when a new selection is made

Private Sub Worksheet_Activate()
Dim Rng As Range, Dn As Range, n As Long
Dim Q
Dim Twn As String
With Sheets("Worksheet")
    Set Rng = .Range(.Range("A2"), .Range("A" & Rows.Count).End(xlUp))
End With
ReDim Ray(1 To Rng.Count, 1 To 3)
    Set Dic = CreateObject("scripting.dictionary")
        Dic.CompareMode = vbTextCompare
For Each Dn In Rng
    Twn = Dn & " / " & Dn(, 2)
   If Not Dic.Exists(Twn) Then
        Ray(1, 1) = Dn(, 2)
        Ray(1, 2) = Dn(, 3)
        Ray(1, 3) = Dn(, 18)
        Dic.Add Twn, Array(Ray, 1)
    Else
        Q = Dic.Item(Twn)
        Q(1) = Q(1) + 1
        Q(0)(Q(1), 1) = Dn(, 2)
        Q(0)(Q(1), 2) = Dn(, 3)
        Q(0)(Q(1), 3) = Dn(, 18)
        Dic.Item(Twn) = Q
    End If
Next
Dim nRay
Dim i As Long
Dim j As Long
Dim Temp As String
nRay = Dic.Keys
For i = 0 To UBound(nRay)
    For j = i To UBound(nRay)
        If nRay(j) < nRay(i) Then
            Temp = nRay(i)
            nRay(i) = nRay(j)
            nRay(j) = Temp
        End If
    Next j
Next i
ComboBox1.List = nRay
ComboBox1.ListIndex = 0
End Sub

Hi Mick, nearly there! Would it be possible that the CAR MAKE in A2 remains just CAR make, NOT MAKE/MODEL.
The reason, because the LINK CELL M1 in ComboBox, is formula checked by the WorkSheet for the exact WORD of the MAKE in COLUMNS AF2:AF35.

I edited the workbook as you described, see this link [url]http://www.1sar.karoo.net/exoftable0ac.xls[/URL] However the error above occurs, that is formula results in worksheet 'COMBOBOX' A6:D30 do NOT work because the LINK CELL M1 is different to any of the CAR MAKES in 'WORKSHEET' A2:A35.

 

[MickG]

Try this:-
https://www.box.com/s/brp8hywjklp5otyijvs5

 

[srands]

Try this:-
https://www.box.com/s/brp8hywjklp5otyijvs5

Hi Mick, I think that's the finished article, great! You're a VB genius.

I guess this is a VB/MACRO code edit, how is this different?

Formulas I can follow, however VB I can edit ROMS/COLUMNS CELLs, that's about it!