Search as you type function in User form

DawidV

New Member
Joined
Jul 14, 2016
Messages
29
Hi There

I am using Excel 2016 and windows 8.1. I am very new to VBA. I have developed a user form to help me search a excel ws and with the info populate the user form. The making of the user-form i understand, but the VBA is another story.......

Everything works well. The button opens the user-form and the Close button works as well. My problem is the Search.

I would like to Search on the Name field and to search as I type. As you can see on the example I included the Name field does not confirm to the usual Surname & Ini format. I do not know if this is the problem.

As you can see in the code I only did the the first three fields on the user-form. When I test the user-form i get no error message but I get the message that is in the code - The search item does not exist
Please I REALLY need help with this.

Here is a copy of how the we looks. The search must work on the Name Column. The ws has 522 rows in it. between the different sections is a blank line. in the example it is between row 17 and 20. Each section has a section heading as in above row 8 and 20. i do not know if this will have an influence on how the search will work.

I tried to include a printscreen/picture of the form but i do not know how to do that.

S NOROOM NOFORCE NOAPPOINTMENTRANKNAMETEL (W)* NOCELL NODATE OF BIRTHGENDERID NO
2
clip_image002.png

<tbody>
</tbody>
3
5th FLOOR
FINANCESFAX: 012 339 6012 / 6020
8A5-0496092630MCASST DIR (BUD MAN)MRM.M. NETSIANDA* (MANDLA)012 339 6010*5133079 158 333103 MAR 67M670303 7197 087
9A5-1097018451CASSA - MECHMSD.M.J. MTHIMUMNYE012 339 6013*5160061 315 467811 DEC 81F811211 0749 087
10A5-0282563723CSSSA - MOT N & SMRL.M. SWANEPOEL(MARIUS)'012 339 6011*5157082 728 678527 MAR 66M660327 5102 080
11A5-06CAC - MECH012 339 6015
12A5-1400049320CASSA - MOT N & SMRG.T. MUDAU (GIVAN)*5177078 764 003813 JUN 84M840613 5738 087
13A5-1592090943CSSSA - MRC.M. NHOLE012 339 6014079 648 181320 JUL 67M670720 5710 084
14A5-1285003242CASAC - MECHMRSV.C. BOONZAAIER (VALERIE)012 339 6019072 742 184908 SEP 69F690908 0059 088
15A5-220005998OCASAC - MOT N & SMSM.P. NEMUTAMBA (MULALO)012 339 6017082 751 905424 DEC 84F841224 1188 084
16A5-1894790797CSSAC - CENTRAL BUDGETMRM.B. TSHAYIMPI (BEDFORD)012 339 6016073 025 846105 OCT 63M631005 6017 087
17A5-0800036376MRCOMPLIANCE MAN CLERK2LTP. MULUVHU* 012 339 6021082 500 872520 OCT 84M841020 5714 085
BLANK LINE
RESEARCH AND DEVELOPMENT SECTIONFAX: 012 339 6036
20A5-1172414345PESSO R & DCOLD. SWART012 339 6024*5294082 342 882013 NOV 56M561113 5030 087
21A5-0994819281PESO1 PROJECTSLT COLS.S. NTSUNGUZI012 339 6025078 459 726521 DEC 68M681221 5722 085
22A5-0776509686PESO1 R&DLT COLC.A. GRUNEWALD012 339 6028071 688 462803 MAR 60M600303 5105 087
23A5-1394077468PESO1 R&DLT COLM.Z. RADEBE012 339 6026073 193 010521 APR 70M700421 5436 085
24A5-1394681830PESO1 R&DLT COLI.N. NDZAMELA012 339 6035073 905 212623 DEC 63M631223 6025 089
25A5-1689367544PESO2 R & DMAJW. COETZER012 339 6030*5689082 577 544224 JAN 73M730124 5062 083
26A5-1600035659MCR&D OFFICERCAPTK.M. PHOLOBA012 339 6030*5690073 478 989704 FEB 84M840204 5872 082

<tbody>
</tbody>


This is how far I got with the code:

Code:
Private Sub cmdClose_Click()
'USER FORM TO DISAPPEAR IMMEDIATELY WHILE ITS MACRO IS EXECUTING USE
'THE HIDE METHOD AT THE  TOP OF  THE PROCEDURE
Me.Hide
'SET SCREEN UPDATING TO TRUE TO FORCE EXCEL TO HIDE THE USER FORM COMPLETELY
    Application.ScreenUpdating = True
    For r = 1 To 10000
        Cells(r, 1) = r
Next r
'THE LAST  STATEMENT IN THE PROCEDURE UNLOADS THE USER FORM
Unload Me
End Sub


Private Sub cmdSearch_Click()
Dim rng As Range, fnd As Range


'SEARCH ACTIVE WORKSHEET WITH NAME (tbxName) FIELD TO POPULATE THE USER FORM


Set rng = Sheet1.Range("F8:F" & Sheet1.Cells(Rows.Count, 2).End(xlUp).Row - 2)
Set fnd = rng.Find(What:=tbxName.Text, LookAt:=xlWhole)
If Not fnd Is Nothing Then


tbxName.Text = Range("F" & fnd.Row).Value
tbxRank.Text = Range("E" & fnd.Row).Value
tbxAppointment.Text = Range("D" & fnd.Row).Value
Else
MsgBox "The search item does not exist", vbOKOnly, "Search"
End If
End Sub
 

Excel Facts

Bring active cell back into view
Start at A1 and select to A9999 while writing a formula, you can't see A1 anymore. Press Ctrl+Backspace to bring active cell into view.
Hi There

Thanks for responding so fast. I really appreciate it.

However i answer this question will show you how stupid i am in Excel VBA.....

Here goes..

I see in my code in the example I did not.
The Name Field in the ws will sometimes contain the following L.M. SWANEPOEL(MARIUS)
Most often only the Ini and Surnames in CAPS. Sometimes this field will be blank in the row when someone is not occupying the office.

Guessing now it should be something as

Dim ws As String

Greetings

Dawid
 
Upvote 0
yes, you need to have whatever the user types as a search into that variable so you can use it. also, force caps on whatever they enter and force caps on the search value of the cells. then case wont matter. for example assume that the name field is in column F and you want to return what is in column G. I think this will work, im certainly not an expert.

Code:
dim ws as string, fnd as variant
ws=ucase(tbxName.Text)
for n = 1 to 1000
   if ws = ucase(cells(n,6).value) then
      fnd = cells(n,7).value
   else msgbox "no data found"
  end if
next
 
Upvote 0
Hi

I have changed the code as you made it. i also included the code here for in case i pasted it at the wrong place.
If i now type any part of the name tbxName field i get a message "data not found" and the program goes into a continuous loop - the only way that i can stop the program is with Ctr+Alt+Del.

What I am really after here is while user type in the field the - without click on search button - the particulars that is linked to the search must populate the user form.

I am not sure if a search box linked to a grid view/list box will be a better bet. If u then double click on your choice in the grid view the user form must then be populated. Maybe I can include the view and the userform.
maybe u can advise me in this.

I an using Excel 2016 Power Programming with VBA to learn VBA.

I have another question on another project I am working on. Would you terribly mind if i send that to you as well. It concerns the Paste function to a ws.

Again Thanx for helping. I really appropriate this help.

Private Sub cmdSearch_Click()
Dim ws As String, fnd As Variant
ws = UCase(tbxName.Text)
For n = 1 To 1000
If ws = UCase(Cells(n, 6).Value) Then
fnd = Cells(n, 7).Value
Else: MsgBox "no data found"
End If
Next


'SEARCH ACTIVE WORKSHEET WITH NAME (tbxName) FIELD TO POPULATE THE SEARCH FORM


Set rng = Sheet1.Range("F8:F" & Sheet1.Cells(Rows.Count, 2).End(xlUp).Row - 2)
Set fnd = rng.Find(What:=tbxName.Text, LookAt:=xlWhole)
If Not fnd Is Nothing Then


tbxName.Text = Range("F" & fnd.Row).Value
tbxRank.Text = Range("E" & fnd.Row).Value
tbxAppointment.Text = Range("D" & fnd.Row).Value
tbxTelWork.Text = Range("G" & fnd.Row).Value
tbxCellNumber.Text = Range("I" & fnd.Row).Value
tbxRoomNumber.Text = Range("B" & fnd.Row).Value


Else


'MsgBox "The search item does not exist", vbOKOnly, "Search"


End If
End Sub
 
Upvote 0
Hi

I have changed the code as you made it. i also included the code here for in case i pasted it at the wrong place.
If i now type any part of the name tbxName field i get a message "data not found" and the program goes into a continuous loop - the only way that i can stop the program is with Ctr+Alt+Del.use ctrl+break

What I am really after here is while user type in the field the - without click on search buttonnot sure I understand this...how would the program know when to start searching? I guess you could do it "on_keypress" but you would constantly be getting errors while typing, or possibly on_exit. the particulars that is linked to the search must populate the user form.

I am not sure if a search box linked to a grid view/list box will be a better bet. If u then double click on your choice in the grid view the user form must then be populated. Maybe I can include the view and the userform.
maybe u can advise me in this.you can certainly do this way, I do it all the time with my forms/code

I an using Excel 2016 Power Programming with VBA to learn VBA.

I have another question on another project I am working on. Would you terribly mind if i send that to you as well. It concerns the Paste function to a ws.

Again Thanx for helping. I really appropriate this help.

Private Sub cmdSearch_Click()
Dim ws As String, fnd As Variant
ws = UCase(tbxName.Text)
For n = 1 To 1000
If ws = UCase(Cells(n, 6).Value) Then
fnd = Cells(n, 7).Value
Else: MsgBox "no data found"
End If
Next


'SEARCH ACTIVE WORKSHEET WITH NAME (tbxName) FIELD TO POPULATE THE SEARCH FORM


Set rng = Sheet1.Range("F8:F" & Sheet1.Cells(Rows.Count, 2).End(xlUp).Row - 2)
Set fnd = rng.Find(What:=tbxName.Text, LookAt:=xlWhole)
If Not fnd Is Nothing Then


tbxName.Text = Range("F" & fnd.Row).Value
tbxRank.Text = Range("E" & fnd.Row).Value
tbxAppointment.Text = Range("D" & fnd.Row).Value
tbxTelWork.Text = Range("G" & fnd.Row).Value
tbxCellNumber.Text = Range("I" & fnd.Row).Value
tbxRoomNumber.Text = Range("B" & fnd.Row).Value


Else


'MsgBox "The search item does not exist", vbOKOnly, "Search"


End If
End Sub
///
 
Upvote 0
and I have more time now then I did yesterday I will fix the sub routine for you
 
Upvote 0
see if this works you man

Code:
Dim txEnt As Variant
Private Sub tbxName_Change()
     txEnt = tbxName.Text
End Sub

Private Sub CommandButton1_Click()
Dim i As Integer
Dim cntr As Double
Dim names()
Dim tempN As Variant

'**validate data was entered**
     If txEnt = "" Then
          MsgBox "please enter value"
               tbxName.SetFocus
          Call tbxName_Change
          Exit Sub
     End If
'*****************************

'**find how many cells have data**
     Set Data = ActiveSheet.Range("f1:f" & Range("f" & Rows.Count).End(xlUp).Row) '
     cntr = 0
          For Each r In Data
               cntr = cntr + 1
                    If r.Offset(1) = vbNullString And r.Offset(2) = vbNullString Then
                    End If
          Next r
'******************************

'**resize array for max possible names, in case of duplicates**
     ReDim names(cntr) As Variant
'******************************

'**load array with names matching user entered value**
i = 0
For nA = 1 To UBound(names)
tempN = ""
tempN = Cells(nA, 6).Value
     If UCase(txEnt) = UCase(tempN) Then
          names(i) = Cells(nA, 7).Value
          i = i + 1
     End If
Next
If i = 0 Then
MsgBox "data entered was not found"
tbxName = ""
tbxName.SetFocus
End If
'*****************************************************

'**poluate list box************
    ListBox1.List = names
'*****************************
End Sub
 
Upvote 0
Hi

I Entered the code in my Search function and I get the error as i marked it in the code.

Again thanx very much for the willingness to help me.

Regards

Dawid

**validate data was entered**
If txEnt = "" Then
MsgBox "please enter value"
tbxName.SetFocus
Call tbxName_Change - sub or function not defined
Exit Sub
End If
 
Upvote 0
ok, that's my fault I should have instructed you about that. if you look at my code there are 2 functions. and a dim statement above the first(to make that variable global) I did it this way so that variable you are searching on can be used by lots of other subs you may write in the future.
Code:
Dim txEnt As Variant-->[COLOR="#FF0000"]put this statement at the very top of all your code, above any other sub or function[/COLOR]
Private Sub tbxName_Change()-->[COLOR="#FF0000"]please put this as its own sub(put it just above the commandButton search, it can go any where but this will make it easier to read later on[/COLOR]
     txEnt = tbxName.Text         
End Sub
 
Upvote 0

Forum statistics

Threads
1,213,549
Messages
6,114,264
Members
448,558
Latest member
aivin

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