Search as you type function in User form

[DawidV]

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 NO	ROOM NO	FORCE NO	APPOINTMENT	RANK	NAME	TEL (W)	* NO	CELL NO	DATE OF BIRTH	GENDER	ID NO
2					<tbody> </tbody>
3
8	A5-04	96092630MC	ASST DIR (BUD MAN)	MR	M.M. NETSIANDA* (MANDLA)	012 339 6010	*5133	079 158 3331	03 MAR 67	M	670303 7197 087
9	A5-10	97018451CA	SSA - MECH	MS	D.M.J. MTHIMUMNYE	012 339 6013	*5160	061 315 4678	11 DEC 81	F	811211 0749 087
10	A5-02	82563723CS	SSA - MOT N & S	MR	L.M. SWANEPOEL(MARIUS)	'012 339 6011	*5157	082 728 6785	27 MAR 66	M	660327 5102 080
11	A5-06		CAC - MECH			012 339 6015
12	A5-14	00049320CA	SSA - MOT N & S	MR	G.T. MUDAU (GIVAN)		*5177	078 764 0038	13 JUN 84	M	840613 5738 087
13	A5-15	92090943CS	SSA -	MR	C.M. NHOLE	012 339 6014		079 648 1813	20 JUL 67	M	670720 5710 084
14	A5-12	85003242CA	SAC - MECH	MRS	V.C. BOONZAAIER (VALERIE)	012 339 6019		072 742 1849	08 SEP 69	F	690908 0059 088
15	A5-22	0005998OCA	SAC - MOT N & S	MS	M.P. NEMUTAMBA (MULALO)	012 339 6017		082 751 9054	24 DEC 84	F	841224 1188 084
16	A5-18	94790797CS	SAC - CENTRAL BUDGET	MR	M.B. TSHAYIMPI (BEDFORD)	012 339 6016		073 025 8461	05 OCT 63	M	631005 6017 087
17	A5-08	00036376MR	COMPLIANCE MAN CLERK	2LT	P. MULUVHU*	012 339 6021		082 500 8725	20 OCT 84	M	841020 5714 085
		FAX: 012 339 6036
20	A5-11	72414345PE	SSO R & D	COL	D. SWART	012 339 6024	*5294	082 342 8820	13 NOV 56	M	561113 5030 087
21	A5-09	94819281PE	SO1 PROJECTS	LT COL	S.S. NTSUNGUZI	012 339 6025		078 459 7265	21 DEC 68	M	681221 5722 085
22	A5-07	76509686PE	SO1 R&D	LT COL	C.A. GRUNEWALD	012 339 6028		071 688 4628	03 MAR 60	M	600303 5105 087
23	A5-13	94077468PE	SO1 R&D	LT COL	M.Z. RADEBE	012 339 6026		073 193 0105	21 APR 70	M	700421 5436 085
24	A5-13	94681830PE	SO1 R&D	LT COL	I.N. NDZAMELA	012 339 6035		073 905 2126	23 DEC 63	M	631223 6025 089
25	A5-16	89367544PE	SO2 R & D	MAJ	W. COETZER	012 339 6030	*5689	082 577 5442	24 JAN 73	M	730124 5062 083
26	A5-16	00035659MC	R&D OFFICER	CAPT	K.M. PHOLOBA	012 339 6030	*5690	073 478 9897	04 FEB 84	M	840204 5872 082

<tbody>
[TD="colspan: 11"] 5th FLOOR [/TD]

[TD="colspan: 3"] FINANCES [/TD]

[TD="colspan: 7"] FAX: 012 339 6012 / 6020 [/TD]

[TD="colspan: 12"]BLANK LINE[/TD]

[TD="colspan: 3"] RESEARCH AND DEVELOPMENT SECTION [/TD]

</tbody>

This is how far I got with the 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

 

[synergy16]

also, dawid I can not reply to your PM because I got a rejection message that your box is full.

 

[DawidV]

eeeeeeSH!!!

I got the email message as well. I am really very stupid with these forums etc to read them....

i went to my messages and deleted all that is in my box. hope it is ok now.

plse try again otherwise i will paste her if it is still a problem.

I also do not understand this site if i would like to send yuo a copy/jpg screenprint of what i am doing. i always feel pictures or printscreen of the excel file helps me better to explain things.

Thanx

Just for my own curiosity are you in South Africa.

 

[synergy16]

no, I'm not in south Africa. I live in new England (USA) Connecticut specifically.

 

[DawidV]

Ok. Got that.

Was just curious.