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.

 

[MickG]

There is a link here to your file modified with combobox5:-
https://www.box.com/s/5qfp04zjuxzh2hkkyzir

For n = 3 To 5 '4
The "n" refers to the combobox number.
Combobox2 2 need to update combobox 3 to 5
Nut the actual columns required in sheet "Worksheet" are 3,18 & 26, so by creating an array as below
cols = Array(0, 0, 0, 3, 18, 26) (The first element of array is 0 not 1)
I can use the number "n", then Cols where n = 3 then = cols(3) the next "n2 = 3 = cols(18) and the last "n" = 5 = Cols(26).
It really a fiddle to make use of the variable "n".

 

[srands]

There is a link here to your file modified with combobox5:-
https://www.box.com/s/5qfp04zjuxzh2hkkyzir

That FILE above does work with the 5 combo vb code edit, however if I do either of the following an ERROR occurs:
i). when I add that VB code 5th edit, to my most current spreadsheet with a 5th COMBO BOX
ii). when I edit that VB code 5th edit, for a 6th COMBO BOX, eg:
For n = 3 To 6
...
cols = Array(0, 0, 0, 3, 18, 17, 16)

Don't know what I'm doing wrong there!

Perhaps further on in the code something else needs editing, hence for example i) the items in green or following line may need edit:

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
    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
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, 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
With ComboBox2
    .List = nRay
    .ListIndex = 0
End With
  End If
End Sub

 

[MickG]

Would it be better if you send the file with the comboboxes you want and a note for which columns from sheet "Worksheet" you in them, and I'll try to alter the code.
I did note that some of the original code is now a bit redundant as we have progressed, but does no harm.
Something that happens is that the original code was meant to fill all the Boxes ,but when you fill one box it can creates a "ChangeEvent" in another box which results in that code running .

 

[srands]

Would it be better if you send the file with the comboboxes you want and a note for which columns from sheet "Worksheet" you in them, and I'll try to alter the code.
I did note that some of the original code is now a bit redundant as we have progressed, but does no harm.
Something that happens is that the original code was meant to fill all the Boxes ,but when you fill one box it can creates a "ChangeEvent" in another box which results in that code running .

Hi Mick, good idea, re-edited file here http://www.1sar.karoo.net/exoftable3~PhotoNonMacroHORIZsearch~11+EDIT.xls 976 KB's

File has 11 ComboBoxs, simply numbered left to right Combo Box 1 to 11,
and for simplicity, referring from Worksheet Columns A to K with ListFillRange.

Qi). Can you make the suggested changes for the re-edited file already with ADDITIONAL COMBO BOXs?

Qii). Could you edit out the redundant code?
Since the "ChangeEvent" replaces older code, then the current code maybe will be more obvious to me!

Qiii). Am I right in presuming if I didn't need a certain COMBO BOX, I could simply delete that specific COMBO BOX, the VB code would perform as normal, without causing an error?

The purpose, so if I need the function of this file, when re-creating then the COLUMNS of most importance I would place at furthest left, and least important furthest right.

Below is a simple table for quick reference, however since in order should be easier to remember in comparison to referring to columns in different orders:

COLUMN TITLE	COLUMN LETTER in WORKSHEET	COLUMN NUMBER vb ref EQUIVALENT
Car Make	A	1
Model	B	2
Type	C	3
Litre	D	4
Desc	E	5
IG	F	6
Petrol	G	7
Turbo	H	8
R/C/L	I	9
SPORTS	J	10
3 DOOR	K	11

<TBODY>
</TBODY>

NOTE: The FORMULA code edits I have already done, including Link Cell, and comparison check worksheet AK:AW, for RESULTS A6:E27.

 

[MickG]

There is not much to do , but I did find, all the Comboboxes from 4 to 11 had to deleted and replace with the same, as for some reason "Excel" did not like them, don't know why !!!!
Replace this bit of code with the similar bit in "Combobox2_Change", the comboboxes shoulds then fill, any problems and I'll post your file back.

For n = 3 To 11
    Set Dic1 = CreateObject("scripting.dictionary")
        Dic1.CompareMode = vbTextCompare
For Each Dn In Rng
    If Dn & Dn(, 2) = Twn Then
        Dic1(Dn(, n).Value) = Empty
    End If
Next

The following lines in Red seem redundent .
[/code]
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
Set DiCB = CreateObject("scrip
[/code]

If you want to delete odd "Comboboxes" you will need to add a bit of code, Like my "Fiddle" bit to loop through them properly, let me know if you want it !!!

 

[srands]

There is not much to do , but I did find, all the Comboboxes from 4 to 11 had to deleted and replace with the same, as for some reason "Excel" did not like them, don't know why !!!!
Replace this bit of code with the similar bit in "Combobox2_Change", the comboboxes shoulds then fill, any problems and I'll post your file back.

For n = 3 To 11
    Set Dic1 = CreateObject("scripting.dictionary")
        Dic1.CompareMode = vbTextCompare
For Each Dn In Rng
    If Dn & Dn(, 2) = Twn Then
        Dic1(Dn(, n).Value) = Empty
    End If
Next

If you want to delete odd "Comboboxes" you will need to add a bit of code, Like my "Fiddle" bit to loop through them properly, let me know if you want it !!!

Hi Mick edited as described, even replaced comboboxes 4 to 11, however returns error.

Hence could you please post working file with these changes, thanks.

Also, could you post the code needed to loop through old deleted ComboBoxs, that would be great.


NOTE:
Here are the errors that returned on my excel 2003:
Run-time error'-2147467259 (80004005)':
Unspecified error.
on DEBUG the code highlighted is within:
Private Sub Worksheet_Activate
...
ComboBox1.ListIndex = 0
and also
Private Sub ComboBox1_Change()
...
ComboBox4.Clear

 

[MickG]

Try this:-
You will see in "Combobox2.code" I have added the "Fiddle" code and removed the Input for Combobox8 as an example , There is a note in the code to reinstate it.
Hope this works for you !!!
https://www.box.com/s/ilel8zxzunin01whgtz9

 

[srands]

see "Combobox2.code "Fiddle" code, Combobox8 example, code to reinstate. Hope this works for you !!! https://www.box.com/s/ilel8zxzunin01whgtz9

Hi Mick, you the man, now we're dancing. My edited working version here:
http://www.1sar.karoo.net/exoftable3~PhotoNonMacro~AZ~HORIZsearch~11+.xls

The only issues I had with this was because previously the only 4 combo boxes were WORDS only, and this 11 combo boxes also used NUMBERS.

This works however COLUMNS with NUMBERS appear to cause it occassional issues, perhaps there is some redundant/conflicting VB code, or alternatively does the code in "WORKSHEET ACTIVATE" need extending to include the other rows? Code I've highlighted in GREEN below:

ption Explicit
Dim Dic As Object
Private Sub ComboBox2_Change()
Dim Rng As Range, Dn As Range, n As Long
Dim Q
Dim Dic1 As Object
Dim col
Dim Twn As String
Twn = ComboBox1.Value & ComboBox2.Value
With Sheets("Worksheet")
    Set Rng = .Range(.Range("B2"), .Range("A" & Rows.Count).End(xlUp))
End With
For n = 3 To 11
    col = Array(0, 1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11)
   'Above I have Replaced "8" (combobox8) with a "0",
   'to reinstate it replace back with "8"
   
   If Not col(n) = 0 Then
    Set Dic1 = CreateObject("scripting.dictionary")
        Dic1.CompareMode = vbTextCompare
For Each Dn In Rng
    If Dn & Dn(, 2) = Twn Then
        Dic1(Dn(, n).Value) = Empty
    End If
Next
If Dic1.Count > 0 Then
        With ActiveSheet.OLEObjects("Combobox" & col(n)).Object
            .List = Dic1.keys
            .ListIndex = 0
        End With
End If
End If
Next n
End Sub
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
  If Not Dic.Exists(Dn.Value) Then
        Ray(1, 1) = Dn(, 2)
'        Ray(1, 2) = Dn(, 3)
'        Ray(1, 3) = Dn(, 4)
        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(, 4)
      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
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, 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
With ComboBox2
    .List = nRay
    .ListIndex = 0
End With
  End If
End Sub

MICK, perhaps since COMBO BOXs are in ORDER of exactly from LEFT to RIGHT, COLUMNS A to K, does code need editing out since the combobox Change Event code replaces 1st draft code from initial 4 combo box version.

ALSO, on another matter since 1st draft combo boxes were all WORD type, those in NUMBER from link cell checked FORMULA for exactness, needed editing to work, hence my FORMULA to check that selection meets specific row needed altering for the COLUMNS with NUMBERS, I have edited instead of a simple = (Equals), now with a LOOKUP formula for COLUMN AN "LITRE" and COLUMN AP "Insurance Group":

WORD CHECK STYLE FORMULA example Worksheet, COLUMN AM, Car TYPE:
=IF(AND(A2=0),"",IF(AND(RESULTS!$T$1=WorkSheet!C2),"Y","N"))

NUMBER CHECK STYLE FORMULA example COLUMN AN, LITRE:
=IF(AND(A2=0),"",IF(LOOKUP(RESULTS!$U$1,RESULTS!$U$1:$U$1,WorkSheet!D2:D2),"Y","N"))

I have tested deleting combo boxes, then editing the VB code, and it works!

Obviously as long as I remember to edit COLUMNs no longer relevant in WORKSHEET AV2:AV35 This checks that words listed in link cell, checked against specific row, and that all checks made are "Y" if not then COLUMN AV car is marked is no and COLUMN AW doesn't count this ROW as meeting criteria.

 

[srands]

Hi Mick, you the man, now we're dancing. My edited working version here:
http://www.1sar.karoo.net/exoftable3~PhotoNonMacro~AZ~HORIZsearch~11+.xls
The only issues I had with this was because previously the only 4 combo boxes were WORDS only, and this 11 combo boxes also used NUMBERS. ANY IDEAS?

Hello Mick, I've made some improvements I've included a feature to ADD/UPDATE/DELETE/BROWSE/SEARCH records on a seperate tab called INPUT, here are the different versions:

4 CATEGORY SEARCH HORIZONTAL:
http://www.1sar.karoo.net/exoftable3~PhotoNM~AZ~HORIZsearch4ADDeditDELETE.xls 1 MB

4 CATEGORY SEARCH VERTICAL:
http://www.1sar.karoo.net/exoftable3~PhotoNM~AZ~VERTsearch4&ADDeditDELETE.xls 1 MB

11 CATEGORY SEARCH HORIZONTAL:
http://www.1sar.karoo.net/exoftable3~PhotoNM~AZ~HORIZsearch11&ADDeditDELETE.xls 1 MB

The INPUT tab simple to use, a USERFORM alternative to just entering data in the worksheet.

If you want to edit for your own own, in each of these workbooks there hidden instructions, FORMAT, SHEET, UNHIDE, INSTRUCTIONS:
see L/H column for making your own PHOTO edits.
see R/H column for making your own INPUT/WORKSHEET edits.

EDITS might be seem a bit involved, the 1st time, is there much more to do, so the instructions go into detail:

VB code is also in MODULES, easily copied & pasted. Instructions note COLUMN REFs and DEFINED NAMES that need changing if you change the number COLUMNS

DEFINED NAMES for PICTURES & LOOKUP LISTS:
FORMAT, SHEET, UNHIDE, LOOKUP LISTS

DATA, VALIDATION OF CELLS to change of DISPLAY PICTURES

Note: If you copy spreadsheets from any of these XL workbooks to another XL workbook remember to COPY ALL OF THESE: WORKSHEET, INPUT, RESULTS, LOOKUPLISTS.
Remember to UNHIDE LOOKUPLISTS: FORMAT, SHEET, UNHIDE,
Also if there an UPDATE SOURCE REQUEST, then re-point, EDIT, LINK, change source, select your CURRENT WORKBOOK.

Alternatively just change headings/titles to suit your data, after all it's just an example.

I know when working with other peoples FORMULAS and VB CODES, I often think these are awkward on purpose to cover their tracks, so other ppl can't use/edit their code easily. Especially VB COLUMN REFERENCEs, of course the TITLES will be different, and the DATA will change, but there are so many examples of VB code, that would need lengthy re-editing, however if REF'd just as COL A, COL B, etc, other then specifically naming COL REF and HEADER TITLE, then would be so much easier for future edits, that is the SPREADSHEET wouldn't need LENGTH EDITS EVERYTIME, just the 1st time of XL workbook creation, then just edit TITLES and enter new DATA.

I've kept it mostly GENERIC but LISTS need NAMES, and for LOOKUPLISTS, your COLUMNS will need to be in different columns:

ANYWAY HERE IS THE SIMPLE INSTRUCTIONS for the INPUT tab:

To ADD NEW information, press CLEAR, then enter details in COLUMN D, scroll down to complete in FULL then left click ADD.

To SEARCH / find for a CAR, select REF of CARS in WORKSHEET,
OR flick through records using arrows by left clicking:
< PREVIOUS RECORD
> NEXT RECORD
<< FIRST RECORD
>> LAST RECORD

To EDIT, search for CARfirst, as above, then simply edit cells in column D from D5, with revised details, then when finished left click UPDATE.

To CLEAR records without deleting them, then left click CLEAR.

To DELETE records, then first of all search ref number or browse for certain CAR, then left click DELETE.

I think it's a great feature to an already great spreadsheet, the INPUT SHEET, on browsing also automatically displays PHOTOS available for that specific car.

 

[srands]

Anyway I think this is 1 of the most functional useful spreadsheets I've ever seen.

However I know when I make spreadsheets following the FORMULA/CODE might appear to be a neverending trail for persons wanting to make their own edits, to customise it to their needs, however this spreadshhet is an amalgamation of ideas, of 1 different thing to another, this isn't to distract, it's just the most appropriate FORMULA or VB code I know of, few FORUMULAS/VB CODES are a solution to all of purposes of the spreadsheet.

Since everybody likes cars, such an example I thought would be the most appropriate.

As then most people will be able to differentiate from:
CATEGORIES and actual DATA. ie:
MAKE = VW,
MODEL = GOLF,
COLOUR = SILVER
REF = 1.