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

MixedUpExcel

Board Regular
Joined
Apr 7, 2015
Messages
222
Office Version
  1. 365
Platform
  1. Windows
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
 

Excel Facts

What is the shortcut key for Format Selection?
Ctrl+1 (the number one) will open the Format dialog for whatever is selected.
Hi,

I think I've found a possible solution which I'm trying to tailor to my needs but I keep getting errors.

Can anyone look at the below code and tell me anything that is obviously wrong?

This code is in the Worksheet Module called SETUP and the cell reference for the formula which produces #N/A's (when the value isn't found on the list) is K24

The idea is that RememberMe stores the data (usually a mix of numbers and text) in the String

Then as the value in the linked cell smartrngcell changes it will put the last value (stored in RememberMe) until the vlookup finds an answer which doesn't result in an error.

Code:
Sub Worksheet_Calculate()
Dim rCell As Range
Dim sMyString As String
Dim RememberMe As String
Sheets("SETUP").Range("K24").Select
RememberMe = ActiveCell.Text
On Error GoTo ErrorHandle
Set rCell = Range("K24")
If IsError(rCell.Value) Then
Range("K24").Value = RememberMe
Else
    
    Sheets("SETUP").Range("K24").Select
    ActiveCell.FormulaR1C1 = "=VLOOKUP(smartrngcell,DATA!A:E,5,0)"
End If
BeforeExit:
Set rCell = Nothing
Exit Sub
ErrorHandle:
MsgBox Err.Description & " Error in procedure CellCheck."
Resume BeforeExit
End Sub

The first error message I get is:

Run-time error '10042:
Select method of Range class failed

Any ideas what I have done wrong above.. or even from my explanation, you can think of (show me) a better way to do this.

Thanks.

Simon
 
Upvote 0
Hi,

Well this is where I've got upto - trying to simplify the code:

It almost works!!

I've added a helper cell - K26 and put my vlookup formula in there.

If the formula results in an error - then exit sub.

If the formula results in an actual result, I get it to copy that to K24.



Code:
Private Sub Worksheet_calculate()

Dim RememberMe As Variant
Dim NewRange As Variant

RememberMe = Worksheets("SETUP").Cells(24, 11).Value

If IsError(Range("K26")) = True Then
    
    Exit Sub

Else

NewRange = Worksheets("SETUP").Cells(26, 11).Value

Worksheets("SETUP").Cells(24, 11).Value = NewRange

End If

End Sub


The above works... however, it appears to go into some kind of loop - linking up to the below code which AlphaFrog got working for me (regarding my Combo Box)

Code:
Private Sub ComboBox1_Change()
    Dim v As Variant, i As Long, j As Long
    With ComboBox1
        .MatchEntry = fmMatchEntryNone
        .ListRows = 10
        If .Text = "" Or .ListIndex > -1 Then
            .ListFillRange = "dropdownrngnames"
        Else
            v = Application.Transpose(Sheets("DATA").Range("dropdownrngnames"))
            .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 Then
                .List = Array("No Match")
            Else
                ReDim Preserve v(1 To j)
                .List = v
            End If
            .DropDown
        End If
    End With
End Sub

Any ideas why this would be causing a loop at the end?

** I'm also going to post a link to this post on my original **

Thanks in advance.

Simon
 
Upvote 0
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?

There may be an easier way to do this.

Unlink the Sheet2!C1 cell from the combobox. Then just have VBA populate it when there is a selection from the combobox's list.

You don't need the Worksheet_Calculate event procedure.

Code:
Private Sub ComboBox1_Change()
    Dim v As Variant, i As Long, j As Long
    With ComboBox1
        .MatchEntry = fmMatchEntryNone
        .ListRows = 10
        If .Text = "" Or .ListIndex > -1 Then
            .ListFillRange = "dropdownrngnames"
        Else
            v = Application.Transpose(Sheets("DATA").Range("dropdownrngnames"))
            .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 Then
                .List = Array("No Match")
            Else
                ReDim Preserve v(1 To j)
                .List = v
            End If
            .DropDown
        End If

        [COLOR=green]'Test if an item is selected and populate the target cell[/COLOR]
        [COLOR=red]If .ListIndex > -1 Then Sheets("Sheet2").Range("C1").Value = .Text[/COLOR]

    End With
End Sub
 
Upvote 0
AlphaFrog,

Saved me again. :)

Thank you so much for the quick reply.

That worked just how I had hoped - I'm just not familiar with all of the code you've put above (original thread) but I will be trying to learn it to use and manipulate again in the future.

Really appreciate your time on this.

Thanks.

Simon
 
Upvote 0
Hi,

As I seem to have a few questions about Comboboxes at the moment, is there somewhere I can read up on the various code you can use for a Combobox?

I now have a new query (I won't cause it an issue as nothing is crashing!).

When the user has selected from the list, the focus is on the Item that has been selected.

When the user selects another cell or item on the sheet, the focus comes off the text box.

I would like the user to be able to click back to the text box within the Combobox and all the text be highlighted when they click in. This is so they don't have to manually highlight all of the text to either delete or type over.

I've looked through google and can't see a suitable answer, not sure if there is one, if so, please can someone advise.

Thanks.

Simon
 
Upvote 0
Hi,

Well I finally found what I needed to highlight the item in the Combobox if you single click into the text area.

If anyone else should need this, just make sure you put it in the Sheet Module.
Code:
Private Sub ComboBox1_GotFocus()

With ComboBox1
 .SelStart = 0
 .SelLength = Len(.Text)
 
End With

End Sub

Thanks.

Simon
 
Upvote 0
Hi AlphaFrog,

From your code:

Code:
Private Sub ComboBox1_Change()
    Dim v As Variant, i As Long, j As Long
    With ComboBox1
        .MatchEntry = fmMatchEntryNone
        .ListRows = 10
        If .Text = "" Or .ListIndex > -1 Then
            .ListFillRange = "dropdownrngnames"
        Else
            v = Application.Transpose(Sheets("DATA").Range("dropdownrngnames"))
            .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 Then
                .List = Array("No Match")
            Else
                ReDim Preserve v(1 To j)
                .List = v
            End If
            .DropDown
        End If

        [COLOR=green]'Test if an item is selected and populate the target cell[/COLOR]
        [COLOR=red]If .ListIndex > -1 Then Sheets("Sheet2").Range("C1").Value = .Text[/COLOR]

    End With
End Sub

After another Macro is run which resets the sheet I'm working on, I get the text put into Combobox1 which is:

Code:
Me.ComboBox1.Text = "Choose Range or Product Type First"

Unfortunately, when it resets with the above wording.. it triggers the 'No Match' to appear as per the code above.

I've tried various If statements in the code to get it to not show No Match when that wording is in.. but I can't get it right.

Can you please advise what I need to do to adjust the code to not show the 'No Match' box (not show any box) when the Combobox says 'Choose Range or Product Type First'?

Thanks in advance.

Simon
How
 
Upvote 0
Code:
[COLOR=darkblue]Private[/COLOR] [COLOR=darkblue]Sub[/COLOR] ComboBox1_Change()
    [COLOR=green]'ActiveX Combobox w\search-as-you-type dynamic dropdown list[/COLOR]
    [COLOR=green]' set the combobox MatchEntry property to fmMatchEntryNone[/COLOR]
    [COLOR=darkblue]Dim[/COLOR] v [COLOR=darkblue]As[/COLOR] [COLOR=darkblue]Variant[/COLOR], i [COLOR=darkblue]As[/COLOR] [COLOR=darkblue]Long[/COLOR], j [COLOR=darkblue]As[/COLOR] Long
    
    [COLOR=darkblue]With[/COLOR] ComboBox1
        [COLOR=darkblue]If[/COLOR] .Text = "" [COLOR=darkblue]Or[/COLOR] .ListIndex > -1 [COLOR=red]Or .Text = "Choose Range or Product Type First"[/COLOR] [COLOR=darkblue]Then[/COLOR]
            .ListFillRange = "MyList"
        [COLOR=darkblue]Else[/COLOR]
 
Upvote 0
Hi AlphaFrog,

Thanks for that.

Unfortunately, somethings not quite working.

It had got rid of the No Match but the little white box that No Match appeared in, still shows.

Basically, this is what I have on my front page:

2 Comboboxes

Combobox 1 - A list of ranges eg. Product Range 1; Product Range 2; Product Range 3 etc.

Combobox 2 is a list of all of the products in the Product Range (these products appear in cells E14 to approx E700) - I've got this Combobox to match the row in this range and make this row the top active row.

When I press another Active X button - it resets both of the Comboboxes above. Combobox1 shows the 1st item in it's list which is 'SELECT RANGE'; I want Combobox 2 to show: 'Choose Range or Product Type First' which is what it is doing.

With your last post, the code now stops the 'No Match' from showing but due to the little white box that is appearing, I can't make any selection in Combobox 1 until I have clicked somewhere else on the sheet. I think the focus is still on Combobox 2 due to the little white box (an empty list box).

If I shift the focus back to Combobox1, would this remove this problem, and if so, how would I do that please?

Thanks in advance for all the help you've been giving me.

Simon
 
Upvote 0

Forum statistics

Threads
1,213,528
Messages
6,114,154
Members
448,553
Latest member
slaytonpa

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top