Dropdown based on search string

[NARAYANK991]

Hi ,

See if this is OK. I have not yet put any password on the VBA code ; if you wish you can do so , by going to the VBE , by either pressing ALT F11 from within the worksheet , or by clicking on Developer , Visual Basic.

Click on Tools , VBAProject Properties.

Click on the Protection tab , and set the password to whatever you wish.

Narayan

 

[GN0001]

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

Naryan,
The code worked for me, Thank you very much.
But can you talk about it to see what it does (I mean the part below)? I have understood it partially.

If Len(Me.TextBox1.Value) Then
   Set rng = Worksheets("Sheet2").Range("A1" & ":" & "A" & Worksheets("Sheet2").Range("A" & Rows.Count).End(xlUp).Row)
   If Application.WorksheetFunction.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"

One more time, thank you very much.
GN0001

 

[GN0001]

@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.

This seems very insteresting, I would like to learn it. If I have a chance, I will be back to this. Thank you for providing this to me.
GN0001

 

[NARAYANK991]

Hi ,

The first IF statement is ensuring that we do not try and process a blank textbox ; if the textbox has even one character entered , only then this IF statement will be satisfied.

Next we set the variable rng to the entire data range in column A in Sheet2.

The next IF statement uses the COUNTIF Excel Worksheet function to find out whether the entered character occurs anywhere in any position in the data range rng ; I think you have posted the earlier code , since if you only want to see those matches where the text starts with the entered character , then the code is different. Here the portion :

"*" & Me.TextBox1.Value & "*"

means any number of characters before the entered character , and any number of characters after. The "*" is a wild-card meaning any number of characters.

However , going ahead with the posted code , if the COUNTIF function finds even one occurrence of the entered character , then the For ... Next loop goes through each cell in the range rng , and if it contains the entered text , puts it in the combobox list ; the check for whether the character occurs in the cell text is done by the INSTR function , which returns the position where the character occurs in the cell data ; if it does not occur anywhere in the cell data , the INSTR function will return 0. The INSTR function is a VBA function.

Narayan

 

[GN0001]

Hi ,

The first IF statement is ensuring that we do not try and process a blank textbox ; if the textbox has even one character entered , only then this IF statement will be satisfied.

Next we set the variable rng to the entire data range in column A in Sheet2.

The next IF statement uses the COUNTIF Excel Worksheet function to find out whether the entered character occurs anywhere in any position in the data range rng ; I think you have posted the earlier code , since if you only want to see those matches where the text starts with the entered character , then the code is different. Here the portion :

"*" & Me.TextBox1.Value & "*"

means any number of characters before the entered character , and any number of characters after. The "*" is a wild-card meaning any number of characters.

However , going ahead with the posted code , if the COUNTIF function finds even one occurrence of the entered character , then the For ... Next loop goes through each cell in the range rng , and if it contains the entered text , puts it in the combobox list ; the check for whether the character occurs in the cell text is done by the INSTR function , which returns the position where the character occurs in the cell data ; if it does not occur anywhere in the cell data , the INSTR function will return 0. The INSTR function is a VBA function.

Narayan

Please give me some time to learn it and then come back to you, I appreciate for writing all this to me.
GN0001

 

[GN0001]

Hi ,

The first IF statement is ensuring that we do not try and process a blank textbox ; if the textbox has even one character entered , only then this IF statement will be satisfied.

Next we set the variable rng to the entire data range in column A in Sheet2.

The next IF statement uses the COUNTIF Excel Worksheet function to find out whether the entered character occurs anywhere in any position in the data range rng ; I think you have posted the earlier code , since if you only want to see those matches where the text starts with the entered character , then the code is different. Here the portion :

"*" & Me.TextBox1.Value & "*"

means any number of characters before the entered character , and any number of characters after. The "*" is a wild-card meaning any number of characters.

However , going ahead with the posted code , if the COUNTIF function finds even one occurrence of the entered character , then the For ... Next loop goes through each cell in the range rng , and if it contains the entered text , puts it in the combobox list ; the check for whether the character occurs in the cell text is done by the INSTR function , which returns the position where the character occurs in the cell data ; if it does not occur anywhere in the cell data , the INSTR function will return 0. The INSTR function is a VBA function.

Narayan

Narayan,
I understand what code does.

But I have problem with blue colored font, what does the blue part do?

Set rng = Worksheets("Sheet2").Range("A1" & ":" & "A" & Worksheets("Sheet2").Range("A" & Rows.Count).End(xlUp).Row)

 

[NARAYANK991]

Hi ,

The blue part is what makes the range adjust to whatever be its size i.e using this construct makes the range dynamic.

The portion Worksheets("Sheet2").Range("A" & Rows.Count).End(xlUp).Row returns the row number of the last row of data.

On your Excel worksheet suppose you go to row 1048576 , which is the last row in the worksheet , in say column A , and then press the keys END UP , this will take you to the last row which has data ; thus if your column A has data till A33 , positioning the cursor in A1048576 and pressing the keys END UP will position the cursor in cell A33.

The code is doing this same action. Rows.Count returns the number of rows in a worksheet ; in early versions of Excel till version 2003 , it will return 65536 ; in Excel 2007 and above , it will return 1048576. Using End(xlUp) takes the cursor to the last row which is non-blank i.e. the last row which has data ; Using Row on this returns the row number.

As you add more and more data in column A , this statement will automatically take it into consideration because of the way it has been written.

Narayan

 

[GN0001]

Hi ,

The blue part is what makes the range adjust to whatever be its size i.e using this construct makes the range dynamic.

The portion Worksheets("Sheet2").Range("A" & Rows.Count).End(xlUp).Row returns the row number of the last row of data.

On your Excel worksheet suppose you go to row 1048576 , which is the last row in the worksheet , in say column A , and then press the keys END UP , this will take you to the last row which has data ; thus if your column A has data till A33 , positioning the cursor in A1048576 and pressing the keys END UP will position the cursor in cell A33.

The code is doing this same action. Rows.Count returns the number of rows in a worksheet ; in early versions of Excel till version 2003 , it will return 65536 ; in Excel 2007 and above , it will return 1048576. Using End(xlUp) takes the cursor to the last row which is non-blank i.e. the last row which has data ; Using Row on this returns the row number.

As you add more and more data in column A , this statement will automatically take it into consideration because of the way it has been written.

Narayan

Hello Narayan,
I understood. Thank you very much.

 

[Amit Modi]

thank you dude but I think this file is corrupted and has two extensions .xlsm I had tried lot by changing it many time.but its not opening.please do needful.

 

[NARAYANK991]

Hi Amit ,

I do not know which file you are referring to ; I downloaded the file from this forum , and there does not seem to be any problem.

Narayan

 

[Somendra Misra]

@Amit Modi

File from which comment # you are trying?

Regards,

 

[sasuke]

how to apply that in data validation? i want search letter "a" and all the letter that has letter "a" must be see in the list.

 

[Somendra Misra]

how to apply that in data validation? i want search letter "a" and all the letter that has letter "a" must be see in the list.

Hi,

Welcome to the forum.

I would request you to start a new thread with a clear definition of the problem in a sample file.

Regards,

 

[sasuke]

can you code this for me? i have data validation in the file and i want to have a search there
Ex: When i type "a" all data that has letter "a" must be show in the in data validation list. Thanks