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

srands · Nov 27, 2012

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 · Dec 18, 2012

When you say they don't work I assume you mean there is no lists in them.
I noticed that you had removed the "Worskbook_Open" event.
This ensured that the Comboboxes were filled when the sheet first appeared.
You can still fill then by first selecting the other sheet then returning to sheet "Results".
If that is the problem , I should find that other bit of code an reinstate it.

srands · Dec 18, 2012

Hi Mick, I've corrected & edited the spreadsheet to work in 2 different versions (VERTICAL or HORIZONTAL search bar/boxs), photo selection via combo box per row of singular result:

www.1sar.karoo.net/exoftable3~PhotoNonMacroHORIZsearch.xls 910 KB's

www.1sar.karoo.net/exoftable3~PhotoNonMacroVERTsearch.xls 911 KB's

Currently the RESULTS for PHOTOS will only display for the 1st two rows, I will add the rest later! That will take a few hours though!

The only other search comment I could make that to display PHOTOs, must select EACH COMBO BOX in COLUMN K of results, ANY IDEAS?

The solution I used for specific photos to appear through in data validation, instead of referring to PictureList, the cell ref is the cell of ID in COLUMN E for that ROW, for example:
RESULTS, K2, =$E$2
RESULTS, K3, =$E$3

Those corrections came to me after a bit of thought, whilst on a break at work.

srands · Dec 19, 2012

Hi Mick, with a bit of extra coding, photos are now available in all search ROWS (Not just 1st 2 rows) via selection combo box per results photo selection via combo box per row of singular result:

www.1sar.karoo.net/exoftable3~PhotoNonMacroHORIZsearch.xls 945 KB's

www.1sar.karoo.net/exoftable3~PhotoNonMacroVERTsearch.xls 941 KB's

I haven't seen a better way of displaying MULTIPLE PHOTOs in an excel spreadsheet.

The only comment I could make this time is that once search is performed using visible combo boxes, to display set of 5 PHOTOS per CAR/RESULT these are selected PER ROW of cars that meet criteria. Perhaps this can be automated? Haven't thought of a way yet! Likely to leave it like that!

MickG · Dec 19, 2012

My only problem is that when I open either of your files, and select any particulcr car/Model , No pictures are seen in sheet "Results"!!, although there are some in sheet "Worksheet".
What am I missing ????

srands · Dec 19, 2012

MickG said:
My only problem is that when I open either of your files, and select any particulcr car/Model , No pictures are seen in sheet "Results"!!, although there are some in sheet "Worksheet".
What am I missing ????

Hi Mick, Ok so for example [url]www.1sar.karoo.net/exoftable3~PhotoNonMacroHORIZsearch.xls[/URL]
Open workbook, left click WORKSHEET tab to reset data, THEN left click RESULTS tab,
THEN select for example
Car: Peugeot
Model: 206
Type: Hdi
Colour: Silver

Then to display PHOTOs:
1st row: ROW6, CELL K6, left click for COMBO BOX to appear, select ONLY AVAILABLE number to display PHOTO
2nd ROW: ROW7, CELL K7, left click for COMBO BOX to appear, select ONLY AVAILABLE number to display PHOTO
etc if there was any more photos.

Simple!

The only reason, all the cars don't have photos in WORKSHEET, is because it would just be a really big file size.

MickG · Dec 19, 2012

I've got it now !!!
It seems to work very well , congratulations.
Regrds Mick

srands · Dec 19, 2012

MickG said:
I've got it now !!!It seems to work very well , congratulations.Regrds Mick

Hi Mick, with your VB code for A to Z, sorting categories CARMAKE>MODEL>TYPE>COLOUR, it works great.1 question about your VB code, what to EDIT for the following EXAMPLE:HOW TO ADD ANOTHER COLUMN to your VB CODE?RESULTS, new COLUMN: MOBILE NUMBER between column between D and E, to make new COLUMN E.ListFillRange & Link Cell, and formula edits to generate data I'm already familiar with.

Code:

Option ExplicitDim Dic As ObjectPrivate Sub ComboBox2_Change()Dim Rng As Range, Dn As Range, n As LongDim QDim Dic1 As ObjectDim col As IntegerDim Twn As StringTwn = ComboBox1.Value & ComboBox2.ValueWith Sheets("Worksheet")    Set Rng = .Range(.Range("B2"), .Range("A" & Rows.Count).End(xlUp))End WithFor n = 3 To 4    Set Dic1 = CreateObject("scripting.dictionary")        Dic1.CompareMode = vbTextCompareFor Each Dn In Rng    col = IIf(n = 4, 18, n)    If Dn & Dn(, 2) = Twn Then        Dic1(Dn(, col).Value) = Empty    End IfNextIf Dic1.Count > 0 Then        With ActiveSheet.OLEObjects("Combobox" & n).Object            .List = Dic1.keys            .ListIndex = 0        End WithEnd IfNext nEnd SubPrivate Sub Worksheet_Activate()Dim Rng As Range, Dn As Range, n As LongDim QDim Twn As StringWith Sheets("Worksheet")    Set Rng = .Range(.Range("A2"), .Range("A" & Rows.Count).End(xlUp))End WithReDim Ray(1 To Rng.Count, 1 To 3)    Set Dic = CreateObject("scripting.dictionary")        Dic.CompareMode = vbTextCompareFor 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 IfNextDim nRayDim i As LongDim j As LongDim Temp As StringnRay = Dic.keysFor 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 jNext iComboBox1.List = nRayComboBox1.ListIndex = 0End SubPrivate Sub ComboBox1_Change()Dim DiCB As ObjectDim n As IntegerDim KDim gComboBox2.ClearComboBox3.ClearComboBox4.ClearSet 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 nRayDim i As LongDim j As LongDim Temp As StringnRay = DiCB.keysFor 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 jNext iWith ComboBox2    .List = nRay    .ListIndex = 0End With  End IfEnd Sub

srands · Dec 20, 2012

Hi Mick my message above seems to have got squashed together! So here it is again!

MickG said:
I've got it now !!!It seems to work very well , congratulations.Regrds Mick

Hi Mick, with your VB code for A to Z, sorting categories CARMAKE>MODEL>TYPE>COLOUR, it works great.
1 question about your VB code, what to EDIT for the following EXAMPLE:

HOW TO EDIT your VB CODE to account for additional COMBOBOXs/COLUMNs ?

RESULTS, new COLUMN: MOBILE NUMBER between column between D and E, to make new COLUMN E.

ListFillRange & Link Cell, and formula edits to generate data I'm already familiar with.

Code:

Option 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 As Integer
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 4
    Set Dic1 = CreateObject("scripting.dictionary")
        Dic1.CompareMode = vbTextCompare
For Each Dn In Rng
    col = IIf(n = 4, 18, n)
    If Dn & Dn(, 2) = Twn Then
        Dic1(Dn(, col).Value) = Empty
    End If
Next
If Dic1.Count > 0 Then
        With ActiveSheet.OLEObjects("Combobox" & n).Object
            .List = Dic1.keys
            .ListIndex = 0
        End With
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(, 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 · Dec 20, 2012

To add "Combobox5" to sheet for "Mobile Number":_
Place "Combobox5" on the sheet where desired.
Change the Combobox2 Code for the below:-
The Mobile numbers from sheet "Worksheet" column "Z" should be retrieved as per the other comboboxes.

Code:

Private [COLOR=navy]Sub[/COLOR] ComboBox2_Change()
[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]Dim[/COLOR] Dic1 [COLOR=navy]As[/COLOR] Object
[COLOR=navy]Dim[/COLOR] cols
[COLOR=navy]Dim[/COLOR] Twn [COLOR=navy]As[/COLOR] [COLOR=navy]String[/COLOR]
Twn = ComboBox1.Value & ComboBox2.Value
[COLOR=navy]With[/COLOR] Sheets("Worksheet")
    [COLOR=navy]Set[/COLOR] Rng = .Range(.Range("B2"), .Range("A" & Rows.Count).End(xlUp))
[COLOR=navy]End[/COLOR] With
For n = 3 To 5 '[COLOR=green][B]4[/B][/COLOR]
    [COLOR=navy]Set[/COLOR] Dic1 = CreateObject("scripting.dictionary")
        Dic1.CompareMode = vbTextCompare
[COLOR=navy]For[/COLOR] [COLOR=navy]Each[/COLOR] Dn [COLOR=navy]In[/COLOR] Rng
    cols = Array(0, 0, 0, 3, 18, 26)
    [COLOR=navy]If[/COLOR] Dn & Dn(, 2) = Twn [COLOR=navy]Then[/COLOR]
        Dic1(Dn(, cols(n)).Value) = Empty
    [COLOR=navy]End[/COLOR] If
[COLOR=navy]Next[/COLOR]
[COLOR=navy]If[/COLOR] Dic1.Count > 0 [COLOR=navy]Then[/COLOR]
        [COLOR=navy]With[/COLOR] ActiveSheet.OLEObjects("Combobox" & n).Object
            .List = Dic1.keys
            .ListIndex = 0
        [COLOR=navy]End[/COLOR] With
[COLOR=navy]End[/COLOR] If
[COLOR=navy]Next[/COLOR] n
[COLOR=navy]End[/COLOR] [COLOR=navy]Sub[/COLOR]

Regards Mick

srands · Dec 20, 2012

MickG said:

To add "Combobox5" to sheet for "Mobile Number":_
Place "Combobox5" on the sheet where desired.
Change the Combobox2 Code for the below:-
The Mobile numbers from sheet "Worksheet" column "Z" should be retrieved as per the other comboboxes.

Code:

Private [COLOR=navy]Sub[/COLOR] ComboBox2_Change()
[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]Dim[/COLOR] Dic1 [COLOR=navy]As[/COLOR] Object
[COLOR=navy]Dim[/COLOR] cols
[COLOR=navy]Dim[/COLOR] Twn [COLOR=navy]As[/COLOR] [COLOR=navy]String[/COLOR]
Twn = ComboBox1.Value & ComboBox2.Value
[COLOR=navy]With[/COLOR] Sheets("Worksheet")
    [COLOR=navy]Set[/COLOR] Rng = .Range(.Range("B2"), .Range("A" & Rows.Count).End(xlUp))
[COLOR=navy]End[/COLOR] With
For n = 3 To 5 '[COLOR=green][B]4[/B][/COLOR]
    [COLOR=navy]Set[/COLOR] Dic1 = CreateObject("scripting.dictionary")
        Dic1.CompareMode = vbTextCompare
[COLOR=navy]For[/COLOR] [COLOR=navy]Each[/COLOR] Dn [COLOR=navy]In[/COLOR] Rng
    cols = Array(0, 0, 0, 3, 18, 26)
    [COLOR=navy]If[/COLOR] Dn & Dn(, 2) = Twn [COLOR=navy]Then[/COLOR]
        Dic1(Dn(, cols(n)).Value) = Empty
    [COLOR=navy]End[/COLOR] If
[COLOR=navy]Next[/COLOR]
[COLOR=navy]If[/COLOR] Dic1.Count > 0 [COLOR=navy]Then[/COLOR]
        [COLOR=navy]With[/COLOR] ActiveSheet.OLEObjects("Combobox" & n).Object
            .List = Dic1.keys
            .ListIndex = 0
        [COLOR=navy]End[/COLOR] With
[COLOR=navy]End[/COLOR] If
[COLOR=navy]Next[/COLOR] n
[COLOR=navy]End[/COLOR] [COLOR=navy]Sub[/COLOR]

Regards Mick

Hi Mick thanks, but this 5TH COMBO BOX edit reports an error for the following, run time error, permission denied:
.List = Dic1.keys

PERHAPS THIS IS BECAUSE ALL FIELDS in 5TH COMBO BOX COLUMN ARE NUMBERS, NOT LETTERS or WORDS?
If easier how about a different column say COLUMN Q ALLOYS I guess in VB speak this COLUMN 17, instead of MOBILE.

File with edit for 5th combo box with error here http://www.1sar.karoo.net/exoftable3~PhotoNonMacroVERTsearch5COMBOBOXedit.xls

Also just trying to understand how to edit by knowing what to do.
Relying on your knowledge here currently, as the COMMAND INSTRUCTIONS & COLUMN references look unfamiliar & unobvious to me.

Can you explain what the code changes do, the only differences I can see are:

For n = 3 To 5 '4

What does this bit of code do?

Is this ordering of what to appear depending on choices made in earlier COMBO BOXES?

I would be unsure what to edit If I wanted to change. Would it matter if new COLUMN was BEFORE or AFTER previous column(s) in terms of A-Z.

cols = Array(0, 0, 0, 3, 18, 26)

How does 0, 0, 0, refer to the 1st 2 columns?
I guess 3 refers to column C, Colour.
I realise 18 refers to column R.
I realise 26 refers to column Z.

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

srands

Board Regular

MickG

MrExcel MVP

Excel Facts

srands

Board Regular

srands

Board Regular

MickG

MrExcel MVP

srands

Board Regular

MickG

MrExcel MVP

srands

Board Regular

srands

Board Regular

MickG

MrExcel MVP

srands

Board Regular

Similar threads

Forum statistics

Share this page

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

Board Regular

MrExcel MVP

Excel Facts

Board Regular

Board Regular

MrExcel MVP

Board Regular

MrExcel MVP

Board Regular

Board Regular

MrExcel MVP

Board Regular

Similar threads

Forum statistics

Share this page

We've detected that you are using an adblocker.

Which adblocker are you using?

Disable AdBlock

Disable AdBlock Plus

Disable uBlock Origin

Disable uBlock