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
 
also, dawid I can not reply to your PM because I got a rejection message that your box is full.
 
Upvote 0

Excel Facts

Select all contiguous cells
Pressing Ctrl+* (asterisk) will select the "current region" - all contiguous cells in all directions.
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.
 
Upvote 0
no, I'm not in south Africa. I live in new England (USA) Connecticut specifically.
 
Upvote 0

Forum statistics

Threads
1,213,529
Messages
6,114,155
Members
448,554
Latest member
Gleisner2

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