Help creating a search button on excel

[chichaspowa]

Hello All,

I need some help creating a search button which generates a search box and returns all instances of a partial word in a listbox, and if i double click it the value will return to my active cell.

I would like it to search in "Clientes!" worksheet in column G.

Thank u in advance

[Tinbendr]

Try this out. (See attachment)

Cross posted here (http://www.mrexcel.com/forum/showthread.php?t=504163)

[chichaspowa]

Thank you!!!!!

Thats exactly what i need! You are a life saver :)

[chichaspowa]

im trying to figure out how to copy and use on my file

[Tinbendr]

Open both files.

Open VBEditor (Alt-F11)

Expand the Userform and Modules branches.

Drag the Module and Userform to your source file.

Go back and save the source with a different name and as a Macro enable workbook if it's not already, just in case there's a problem.

On the source WB, select the destination folder. Select the Developer tab. Select Design mode. Select Insert, mouse down to ActiveX and select command button. Click on the sheet where you want the button to appear. Right click on the button, Edit Code. In the sub Add ufSearch.Show

Go back to developer tab and unselect design mode.

Post back if you have more trouble.

Good luck!

[chichaspowa]

i have managed to open it and edit including removing the userform when opening the file and only when i push the button....

There seems to be something wrong with the "sheet names" i cant get it to go to the client sheet to search and the list comes up empty.

I noticed that the code has sheet 1 in 2 places.

Would like to know if its also possible to select the names before pushing enter

Thanks David

[Tinbendr]

What is the sheet name where the output is expected?

Make sure Sheetname "Clientes" is spelled correctly in the code (or on the sheet tab). It has to match the actual sheet name.

I noticed that the code has sheet 1 in 2 places.That's where I had to go back and adjust for the other named sheet. The name in the parentheses is the Sheet name. The name on the left is the sequential sheet index Excel automatically gives when a sheet is added.

Would like to know if its also possible to select the names before pushing enterI don't follow.

[chichaspowa]

The sheet name Clientes is spelt correctly and doesnt show the client list on the userform list.

When using the search box i have to push enter before clicking the client name, can that be changed by clicking the client name before typing enter?

[chichaspowa]

i have corrected it by changing the sheet name clientes to sheet5 :) ... what do i need to do to change the form to let user scroll or choose the client before puching enter. Another option would be not showing the client list before pushing enter.

Thanks David

[Tinbendr]

i have corrected it by changing the sheet name clientes to sheet5 :) ...I was a little inconsistant in my sheet naming convension. Sorry about that.


what do i need to do to change the form to let user scroll or choose the client before punching enter.I think I understand. If I clicked on a Client before entering anything in the textbox, the list would disappear. Since the textbox entry is really just a filter, I had the test for an empty textbox. I had that out of place. I added a worksheet object to point to the Clientes Worksheet.

Here is the whole sub.
Private Sub txtSearchItem_Exit(ByVal Cancel As MSForms.ReturnBoolean)

Dim aRow As Long
Dim LastRow As Long
Dim WS As Worksheet
Set WS = ActiveWorkbook.Worksheets("Clientes")

If Me.txtSearchItem <> "" Then
Me.lboxClientes.Clear
With Me.lboxClientes

LastRow = WS.Cells.Find("*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
For aRow = 2 To LastRow
If InStr(UCase(WS.Range("G" & aRow).Value), UCase(Me.txtSearchItem)) <> 0 Then
.AddItem WS.Range("G" & aRow).Value
End If
Next
End With
End If

End Sub

[chichaspowa]

thanks David that did the trick :)