• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Dropdown based on search string

Manish Sharma

Manish Sharma

Member
Hi Friends,

I am trying to create a dynamic dropdown list based on a search string given by the user. Please let me explain it in detail.

- I have unique list of items which is not fixed, it can increase or decrease based on Raw data input (but it will contains unique items)
- Then i have 1 ActiveX Textbox and 1 ActiveX Combobox
- User can input his search string in Textbox1 (Search string can be either full text string from Column A or partial text from beginning, middle or end)
- If his text string is not present in Column A then he will get a popup message
- Else Combobox1 will get populated containing all the options from Column A, containing the text string

Eg; if user search for "Potato" then combobox1 should show
PotatoSpinachPotato
PotatoVegetableBeetroot
OnionPotatoPignut
CarrotPotatoSoybean

Please attached the sample file.

Hope my words are clear in explaining my problem.

Thanks & Regards,
Manish
 

Attachments

  • Dropdown based on search string.xlsm
    15.7 KB · Views: 82
Hi friends, i have got this code from another blog and i don't want any of my online friends to waste their time on solving this problem again, so here i am posting the code to solve my above problem.
Code: 
Private Sub TextBox1_Change()
Dim rng As Range, cell As Range
Me.ComboBox1.Clear
If Len(Me.TextBox1.Value) Then
Set rng = Range("C6", Range("C" & Rows.Count).End(xlUp))
If Application.CountIf(rng, "*" & Me.TextBox1.Value & "*") Then
For Each cell In rng
If InStr(1, cell.Value, Me.TextBox1.Value, 1) Then Me.ComboBox1.AddItem cell.Value
Next cell
Else
MsgBox "Entry doesn't exist in the list. ", , "No Match Found"
End If
End If
End Sub
 
Somendra Misra said:
Hi Manish,

See the attached file.

Just advise if this you want or something different from this.

Regards,
Click to expand...

Thanks Somendra, but I think there is some problem with linking the textbox value to Sheet3 A1 range.
For all the text strings entered in text value, I am getting only "-" in the combobox. I tried to link Textbox to Sheet3 of range A1 but it is not happening. Please suggest.
 
What data you are trying to enter? Because if I enter Apple I am getting all the list, If I enter a non-available word I get a pop-up message.

One thing, list will be populated only after entering the word and pressing Enter key.

Regards,
 
Somendra Misra said:
What data you are trying to enter? Because if I enter Apple I am getting all the list, If I enter a non-available word I get a pop-up message.

One thing, list will be populated only after entering the word and pressing Enter key.

Regards,
Click to expand...
Ok i Got it, but I think like me user can not press the enter key and directly go to combobox to select his options. Then this is not a good to press enter key.

Somendra, can we avoid pressing the enter key by altering the code?
 
Debraj said:
Here is another approach..

by combining Text Box & Combo at the same time..
Click to expand...

Thanks Deb for your reply, but here is one problem. If the search string is not present in the list then also it is accepting it, it should popup a message, saying search text not available or something else.
 
Hi Manish,

It is a variable which in case or error of word nor in list remains 0 and used to pop up the message other wise it will be more than 0.

Regards,
 
Can you Modify this code?
My range and my combo box is not in one page

My range is in sheet2 and my combo box is in sheet 1.

Code: 
Private Sub TextBox1_Change()
            Dim rng As Range, cell As Range
           
            Me.ComboBox1.Clear
            If Len(Me.TextBox1.Value) Then
              Set rng = Range("C6", Range("C" & Rows.Count).End(xlUp))
              If Application.CountIf(rng, "*" & Me.TextBox1.Value & "*") Then
                  For Each cell In rng
                      If InStr(1, cell.Value, Me.TextBox1.Value, 1) Then Me.ComboBox1.AddItem cell.Value
                  Next cell
              Else
                  MsgBox "Entry doesn't exist in the list. ", , "No Match Found"
              End If
            End If
End Sub

Please let me know,
Thanks GN0001
 
Last edited by a moderator:
@GN0001

You can move the ComboBox to another sheet.. and re-create the same code for the comboBox.

Please check the attched.. Now seach Box has been moved to another sheet. :)
 

Attachments

  • Search Combo like Google AnotherSheet.xlsm
    31.7 KB · Views: 61
You can move the ComboBox to another sheet.. and re-create the same code for the comboBox.

Please check the attched.. Now seach Box has been moved to another sheet. :)[/quote]

Hello Manish,
Thank you for your response.
it doesn't work at all.

I entered strings from your own list and pressed enter, but nothing happened. Any tips?

Which are the sheets, I need. The workbook is confusing, which sheets do I need to keep? how does it work?
 
Naryan,
I put your code in my own file. File is uploaded.

As far as I tested, it worked.
However I want:
1- To enter character a, it has to show all the strings that start with a, not all the strings that contain a.
2-How can I put a password on the code, that nobody can see the code.

I appreciate your help so very much.
GN0001
 

Attachments

  • Combo box 04-09-20142.xlsm
    69.4 KB · Views: 63
You must log in or register to reply here.
Back
Top