Drop down box link to cell - only show option from list

[MixedUpExcel]

Hi,

Thanks to AlphaFrog on this thread of mine:
http://www.mrexcel.com/forum/excel-...ombo-box-using-visual-basic-applications.html

I have the Drop Down List (Combo Box) on Sheet 1 and my list of items for the Drop Down List on Sheet 2.

I have a cell, say Sheet2!C1, which is the cell linked to the Combo Box (I have named the cell as a range for this to work)

I have a vlookup which is triggered by the result which is in Sheet2!C1 but whenever I clear out the Search Field, Sheet2!C1 is also blank.

This results in the vlookup resulting in #N/A.

I have rows unhiding / hiding based on this result.

My Question:

Is it possible to type into the Blank Field of the Combo Box and the linked cell Sheet2!C1 to only show a change of value when an item from the list is selected in the Drop Down Combo Box?

OR

Is there a piece of code I could put into the Sheet that would stop the cell Sheet2!C1 from changing until it matches an item from the list

OR

Can you make a Vlookup or Index Match formula only change it's result if there is an Exact Match to an item on the list (not like putting true or false in the formula) but if the answer from the formula doesn't match an item on the list - it doesn't change?

Thank in advance for anyone who can post possible solutions.

Simon

 

[AlphaFrog]

In the command button procedure, before setting the combobox text to "Choose Range or Product Type First", set the ListIndex = -1

ComboBox2.ListIndex = -1
ComboBox2.Text = "Choose Range or Product Type First"

 

[MixedUpExcel]

Hi,

Unfortunately, putting that line in has now resulted in the 'No Match' wording appearing again in the white box under the Combobox.

ComboBox2.ListIndex = -1
ComboBox2.Text = "Choose Range or Product Type First"

My understanding, rightly or wrongly, is that I run my Reset Macro - which includes the Combobox2.Text line.

Because I am making a change to Combobox2 - it will then trigger Private Sub ComboBox2_Change() - which is the code you provided me.

At the end of that, it has found that I have changed the text in the Combobox2.Text section and there isn't a Match, so it tells me No Match.

Below is my actual code - unchanged from the ORDER PAD Sheet Module.

Private Sub ComboBox2_Change()
Application.ScreenUpdating = False
Application.Calculation = xlCalculationManual
    Dim v As Variant, i As Long, j As Variant
    With ComboBox2
        .MatchEntry = fmMatchEntryNone
        .ListRows = 10
        If .Text = "" Or .ListIndex > -1 Then
            .ListFillRange = "allproducts"
        Else
            v = Application.Transpose(Sheets("ORDER PAD").Range("allproducts"))
            .ListFillRange = ""
            For i = 1 To UBound(v)
                If LCase(v(i)) Like "*" & LCase(.Text) & "*" Then
                    j = j + 1
                    v(j) = v(i)
                End If
            Next i
            
            If j = 0 And j <> "Choose Range or Product Type First" Then
                .List = Array("No Match")

            Else
                ReDim Preserve v(1 To j)
                .List = v
            End If
            .DropDown
        End If
        'Test if an item is selected and populate the target cell
        If .ListIndex > -1 Then Sheets("ORDER PAD").Range("E9").Value = .Text
        If .ListIndex > -1 Then Call FindMeNow
                     
    End With
    
    Application.ScreenUpdating = True
    Application.Calculation = xlCalculationAutomatic
    
End Sub

I've included the FindMeNow Sub Routine just in case that is causing a problem (but I don't think it is)

Private Sub FindMeNow()
Dim searchval1 As Long
Dim rng As Range
Dim productrng As Range
Set rng = Sheet5.Range("E9")
Set productrng = Worksheets("ORDER PAD").Range("allproducts")
    searchval1 = Application.Match(rng, productrng, 0)
    searchval2 = searchval1 + 14
    
' SHOW ROW SEARCH RESULT AS TOP VISIBLE ROW UNDER THE FREEZE PANEL
On Error Resume Next
    Range("A" & searchval2 - 1).Select
    ActiveWindow.ScrollRow = ActiveCell.Row
End Sub

Thanks.

Simon

 

[MixedUpExcel]

Hi AlphaFrog,

Unless this is going to be an easy fix, don't worry about it too much.

I'm going to take my list of products, put them on another sheet, remove duplicates and add the first line as being Choose Range or Product Type First so that it can default to that line like I do on SELECT RANGE for Combobox1.

Thanks for your time though in offering solutions.. I do appreciate that as I've just not been able to find any other solutions than those that you offered.

Thanks again.

Simon

 

[AlphaFrog]

You still need this...

    With ComboBox2
        .MatchEntry = fmMatchEntryNone
        .ListRows = 10
        If .Text = "" Or .ListIndex > -1 [COLOR=#FF0000]Or .Text = "Choose Range or Product Type First"[/COLOR] Then
            .ListFillRange = "MyList"

Then use this to reset.

[COLOR=#FF0000]ComboBox2.ListIndex = -1[/COLOR]
ComboBox2.Text = "Choose Range or Product Type First"

Your understanding of how it works is mostly correct. The not-so-obvious thing the combo box does when you use this command ComboBox2.Text = "Anything not on the list", is to trigger the ComboBox2_Change procedure twice. I think it triggers the 1st time because the .ListIndex changes to -1 (at this point it doesn't recognize the text change yet). Then the 2nd trigger is the text change.

The two red items in the suggestion above prevent the .Dropdown portion of the code from executing for both triggers.

 

[MixedUpExcel]

AlphaFrog,

That appears to have done the job perfectly.

Again, many thanks for your time on this.

I think this project is just about complete and I've learnt a lot about Comboboxes in the last week

Thanks.

Simon