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.

 

[saelageonzon]

The dropdown needs to be edited in the macro code. Other than a maro, I would suggest using a dependent data validation for this. Can you give a list of carmakes, model per carmake, type per model and colour?
Thanks

 

[srands]

The dropdown needs to be edited in the macro code. Other than a maro, I would suggest using a dependent data validation for this. Can you give a list of carmakes, model per carmake, type per model and colour?
Thanks

Hi, sure the items you mentioned:

1). The full source LIST of populated data I described is the 2nd worksheet in the workbook, simpled called WorkSheet in the file FILE hyperlink below:
[url]http://www.1sar.karoo.net/exoftable0.xls[/URL]

Then the 1st worksheet, uses the data in the 2nd worksheet.

Perhaps the combo boxs, can perform A-Z sort without repetition from editing properties in the COMBOBOX PROPERTIES?

Maybe another CONTROL TOOLBOX button is more appropriate?

2). Editing the combo boxs in DESIGN MODE from the control toolbox, I can edit the PROPERTIES, which I did for the LinkedCell & ListFillRange.

However WHICH PROPERTIES to edit in this macro editing mode for A-Z sort, without repetation of source data/fields? i.e. Column A: If PEUGEOT selected, then to display PEUGEOT once, even though there 7 PEUGEOTs in the list, then in CAR MODEL the only visible items should be: 206, 207, 306 and 307, hence not all the car make MODEL types.

Do you know of the specific CONTROL TOOLBOX COMBOBOX PROPERTIES? Can you name them? Provide code examples or screenprints?

Cheers

 

[MickG]

Try this for a start:-
Create a new sheet in your "exoftable0.xls" workbook with comboboxes 1 to 4 on it.
Right click the sheet tab , Select "View Code" , vb window appears.
Paste the entire code into the vb Window.
Nb:- Make sure you have not got two "Option Explicits"' at the top of the code.
Close the Vb window.

Select another sheet then select the New sheet again.
Combobox1 should be filled.
Select an item from combobox1, then the other comboboxes should also be filled from Sheet "Worksheet"..
Reselect another "Car" from Combobox1 as require to refill the other comboboxes again.
If you get this working and want to continue with some code to do something with the selections let me know.

Option Explicit
[COLOR=navy]Dim[/COLOR] Dic [COLOR=navy]As[/COLOR] Object
Private [COLOR=navy]Sub[/COLOR] Worksheet_Activate()
[COLOR=navy]Dim[/COLOR] Rng [COLOR=navy]As[/COLOR] Range, Dn [COLOR=navy]As[/COLOR] Range, n [COLOR=navy]As[/COLOR] [COLOR=navy]Long[/COLOR]
[COLOR=navy]Dim[/COLOR] Q
[COLOR=navy]With[/COLOR] Sheets("Worksheet")
    [COLOR=navy]Set[/COLOR] Rng = .Range(.Range("A2"), .Range("A" & Rows.Count).End(xlUp))
[COLOR=navy]End[/COLOR] With
ReDim ray(1 To Rng.Count, 1 To 3)
    [COLOR=navy]Set[/COLOR] Dic = CreateObject("scripting.dictionary")
        Dic.CompareMode = vbTextCompare
[COLOR=navy]For[/COLOR] [COLOR=navy]Each[/COLOR] Dn [COLOR=navy]In[/COLOR] Rng
    [COLOR=navy]If[/COLOR] Not Dic.Exists(Dn.Value) [COLOR=navy]Then[/COLOR]
        ray(1, 1) = Dn(, 2)
        ray(1, 2) = Dn(, 3)
        ray(1, 3) = Dn(, 18)
        Dic.Add Dn.Value, Array(ray, 1)
    [COLOR=navy]Else[/COLOR]
        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
    [COLOR=navy]End[/COLOR] If
[COLOR=navy]Next[/COLOR]
ComboBox1.List = Dic.keys
[COLOR=navy]End[/COLOR] [COLOR=navy]Sub[/COLOR]
Private [COLOR=navy]Sub[/COLOR] ComboBox1_Change()
[COLOR=navy]Dim[/COLOR] DiCB [COLOR=navy]As[/COLOR] Object
[COLOR=navy]Dim[/COLOR] n [COLOR=navy]As[/COLOR] [COLOR=navy]Integer[/COLOR]
[COLOR=navy]Dim[/COLOR] K
[COLOR=navy]Dim[/COLOR] g
ComboBox2.Clear
ComboBox3.Clear
ComboBox4.Clear
[COLOR=navy]For[/COLOR] n = 2 To 4
[COLOR=navy]Set[/COLOR] DiCB = CreateObject("scripting.dictionary")
    DiCB.CompareMode = vbTextCompare
        [COLOR=navy]If[/COLOR] Dic.Exists(ComboBox1.Value) [COLOR=navy]Then[/COLOR]
            [COLOR=navy]For[/COLOR] g = 1 To Dic.Item(ComboBox1.Value)(1)
                DiCB(Dic.Item(ComboBox1.Value)(0)(g, n - 1)) = Empty
            [COLOR=navy]Next[/COLOR] g
        ActiveSheet.OLEObjects("Combobox" & n).Object.List = Application.Transpose(DiCB.keys)
  [COLOR=navy]End[/COLOR] If
[COLOR=navy]Next[/COLOR] n
[COLOR=navy]End[/COLOR] [COLOR=navy]Sub[/COLOR]

Regards Mick

 

[srands]

If you get this working and want to continue with some code to do something with the selections let me know.

Option Explicit
[COLOR=navy]Dim[/COLOR] Dic [COLOR=navy]As[/COLOR] Object
Private [COLOR=navy]Sub[/COLOR] Worksheet_Activate()
[COLOR=navy]Dim[/COLOR] Rng [COLOR=navy]As[/COLOR] Range, Dn [COLOR=navy]As[/COLOR] Range, n [COLOR=navy]As[/COLOR] [COLOR=navy]Long[/COLOR]
[COLOR=navy]Dim[/COLOR] Q
[COLOR=navy]With[/COLOR] Sheets("Worksheet")
    [COLOR=navy]Set[/COLOR] Rng = .Range(.Range("A2"), .Range("A" & Rows.Count).End(xlUp))
[COLOR=navy]End[/COLOR] With
ReDim ray(1 To Rng.Count, 1 To 3)
    [COLOR=navy]Set[/COLOR] Dic = CreateObject("scripting.dictionary")
        Dic.CompareMode = vbTextCompare
[COLOR=navy]For[/COLOR] [COLOR=navy]Each[/COLOR] Dn [COLOR=navy]In[/COLOR] Rng
    [COLOR=navy]If[/COLOR] Not Dic.Exists(Dn.Value) [COLOR=navy]Then[/COLOR]
        ray(1, 1) = Dn(, 2)
        ray(1, 2) = Dn(, 3)
        ray(1, 3) = Dn(, 18)
        Dic.Add Dn.Value, Array(ray, 1)
    [COLOR=navy]Else[/COLOR]
        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
    [COLOR=navy]End[/COLOR] If
[COLOR=navy]Next[/COLOR]
ComboBox1.List = Dic.keys
[COLOR=navy]End[/COLOR] [COLOR=navy]Sub[/COLOR]
Private [COLOR=navy]Sub[/COLOR] ComboBox1_Change()
[COLOR=navy]Dim[/COLOR] DiCB [COLOR=navy]As[/COLOR] Object
[COLOR=navy]Dim[/COLOR] n [COLOR=navy]As[/COLOR] [COLOR=navy]Integer[/COLOR]
[COLOR=navy]Dim[/COLOR] K
[COLOR=navy]Dim[/COLOR] g
ComboBox2.Clear
ComboBox3.Clear
ComboBox4.Clear
[COLOR=navy]For[/COLOR] n = 2 To 4
[COLOR=navy]Set[/COLOR] DiCB = CreateObject("scripting.dictionary")
    DiCB.CompareMode = vbTextCompare
        [COLOR=navy]If[/COLOR] Dic.Exists(ComboBox1.Value) [COLOR=navy]Then[/COLOR]
            [COLOR=navy]For[/COLOR] g = 1 To Dic.Item(ComboBox1.Value)(1)
                DiCB(Dic.Item(ComboBox1.Value)(0)(g, n - 1)) = Empty
            [COLOR=navy]Next[/COLOR] g
        ActiveSheet.OLEObjects("Combobox" & n).Object.List = Application.Transpose(DiCB.keys)
  [COLOR=navy]End[/COLOR] If
[COLOR=navy]Next[/COLOR] n
[COLOR=navy]End[/COLOR] [COLOR=navy]Sub[/COLOR]

Regards Mick

Hi Mick, couldn't get this to have desired effect. Can you include it to the WORKBOOK, so I can download it from a link?

I tried adding this code as MODULE 3 without any effect.
Also I tried adding this code to MODULE 2, but it returned an error, unambigious name.

I haven't seen a worksheet do this as described, I guess it is possible, perhaps you have a working example already?
I have only seen/used website with combo boxs that appear A to Z, without repeats of same word.
I realise AUTOFILTER has the same effect, I don't the source data visible though.
I realise the same is possible with DATA ACCESS PAGES with DNS, I don't want a html page, just EXCEL.
Cheers

 

[srands]

A working XL spreadsheet with combo box A-Z that doesn't repeat same word/number, would be great. Care to share? Hyperlink would be good!

I think my question here clearly explains what I want, however Macro codes can be very specific, and need editing for worksheet changes, but if Macro code is the only way, then fair enough, however always good to see a working example to know it's possible in the 1st place.

My aforementioned example works but the combo boxs needs a-z & no repeat edit:
http://www.1sar.karoo.net/exoftable0.xls 111KB'S

Also to give you a better idea, of results I'm looking for, my other version of this spreadsheet uses the worksheets and performs seperate sorts based on just formulas, see WORKSHEET for SOURCE, and example RESULTS see RESULTS206:
http://www.1sar.karoo.net/exoftable6.xls 451KB'S

 

[srands]

Not many replies here, so have posted elsewhere, crossthread:
ComboBox, Control ToolBox, Arrange A-Z without repeats of words?</SPAN>

 

[MickG]

Have a look at Your Returned Workbook:- Sheet (1)
https://www.box.com/s/cv8mybmgprmbnv6lgzxy

 

[srands]

Have a look at Your Returned Workbook:- Sheet (1)
https://www.box.com/s/cv8mybmgprmbnv6lgzxy

Hi Mick, that's a great start. I've edited your version to work with the source worksheet to display all of the relevant results, see that version here:

http://www.1sar.karoo.net/exoftable0a.xls

Works for NO-REPEATS, once macro is RUN and different CAR MAKE (A2 ComboBox) selected each time.

Can the COMBOBOXs sort A-Z for each COMBOBOX?

The VB code if I change the NUMBER and the TITLES of the COLUMNS in the WORKSHEET, should it still work?

At a glance the VB looks to read from ROWs A2 downward and from COLUMNs A to end of visible spreadsheet, is that right?

Cheers

 

[MickG]

This is the same code with additions to sort all the comboboxes.
If I where you I would Create a new sheet on the Workbook with 4 comboboxes, then Right click the sheet Tab , Select "View Code", VBwindow appears, Paste entire code into window.

=============
ReF Ranges
The code searches throught Column "A" and looks at columns "2,3 & 18" you can change these in the code (You need to change in 2 positions in the "Woeksheet activate code" i.e. Dn(,2). Dn(,3) & Dn(,18) (2 sets)

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