PDA

View Full Version : Help with autocomplete in textBoxes



kaj10
08-08-2008, 01:39 PM
Hello everyone i hope you are ready for the weekend. i can get mine started when I finally get this text box to work properly. My problem is when some one types a name into a certain textbox i want the rest of the text to pop up. I believe it is called auto complete and i have searched everywhere for info on this subject and i cant find it. Example if I type KY the rest of the text will appear KYLE JONES. The names are in a range called "names"

THANK YOU
KYLE JONES:banghead:

kaj10
08-08-2008, 01:56 PM
All the names are in the first column one after the other going down starting with range a2 it is there first and last name.

kiyiya
08-08-2008, 11:41 PM
Have you considered using a combobox? This behavior is native to the control and all you have to do to load the named range is set the ListFillRange to "Names".

See the example I included.

Aussiebear
08-09-2008, 03:18 AM
Someone here provided me with an example of Autocomplete a few years ago. While it only works as a sheet function, it may well be easily be adapted for a textbox. Open the attached workbook and test by typiing male names in Column A.

Then have a look at the code and you'll get some ideas as to how it might be adapted.

mdmackillop
08-09-2008, 04:03 PM
What type of textbox are you using? ActiveX, Userform or Forms toolbar?

kaj10
08-11-2008, 06:24 AM
Sorry i was not around to reply i just got back to the office I am using a userform text box MD. I will look at all your suggestions and get back to you.
Thanks everyone for your help

kaj10
08-11-2008, 06:42 AM
kiyiya your solution worked fine and it was easy enough thank you again to everyone for your help. If this helps anyone i will post my results.


Private Sub btnSubmit_Click()
Dim sh As Worksheet
Dim shName As String
Dim strDate As Date
Dim x As String
Dim repname As String
Dim foName As String
Dim foName1 As String
Dim mydir1 As String
Dim mydir2 As String
Dim PathExists As Boolean
Dim strSourceFolder As String, strDestFolder As String
Dim Counter As Integer
Dim Overwrite As String
Dim Wb As Workbook
Dim AWb As String
Dim status As String
Dim num As String
Worksheets("log").Select
Range("a2").Select
Do
If IsEmpty(ActiveCell) = False Then
ActiveCell.Offset(1, 0).Select
End If
Loop Until IsEmpty(ActiveCell) = True
ActiveCell.Offset(0, 10).Select
num = ActiveCell.Value
status = "OPEN"
AWb = ActiveWorkbook.Name
mydir1 = "s:\RG trials\trials\"
foName = "RG_" & cbxCAT.Value & cbxYEAR.Value & num
MkDir (mydir1 & foName)

Sheets("Request").Select
Sheets("Request").Copy

shName = "RG_" & cbxCAT.Value & cbxYEAR.Value & num & "REQ" & ".xls"

'Save copies as The new name and email to Admin
ActiveSheet.SaveAs FileName:="s:\RG trials\trials\" & foName & "\" & shName & ".xls"



GoTo email

email:
Dim oApp As Object
Dim oMail As Object

Dim shName1 As String

shName1 = "RG_" & cbxCAT.Value & cbxYEAR.Value & cbxVERSION.Value & "REQ" & ".xls"
'Turn off screen updating
Application.ScreenUpdating = False

'Make a copy of the active sheet and save it to
'a temporary file
ActiveSheet.Copy
Set Wb = ActiveWorkbook
FileName = shName
On Error Resume Next
Kill "S:\" & FileName
On Error GoTo 0
Wb.SaveAs FileName:="S:\" & FileName

'Create and show the outlook mail item
Set oApp = CreateObject("Outlook.Application")
Set oMail = oApp.CreateItem(0)
With oMail
'Uncomment the line below to hard code a recipient
.To = "Dale.Williams@bekaert.com"

.Subject = cbxNames.Value & " has submitted a new request form to s:\RG trials\trials\" & foName
.Attachments.Add Wb.FullName

' Comment out below line and uncomment .send if you just want to send it
.display
'.send
'WB.ActiveSheet.Delete
End With


GoTo Log
Log:
Windows.Application.Workbooks("Request Form.xls").Activate
Worksheets("request").Select
Range("b6").Copy
Worksheets("log").Select
Range("a1").Select
Dim mydate As Date
mydate = Now()
Do
If IsEmpty(ActiveCell) = False Then
ActiveCell.Offset(1, 0).Select
End If
Loop Until IsEmpty(ActiveCell) = True
ActiveCell.Value = cbxNames.Value
ActiveCell.Offset(0, 1).Select
ActiveCell.Value = cbxCAT.Value
ActiveCell.Offset(0, 1).Select
ActiveCell.Value = cbxVERSION.Value
ActiveCell.Offset(0, 1).Select
ActiveCell.Value = mydate
ActiveCell.Offset(0, 1).Select
ActiveCell.Value = (mydir1 & foName)
ActiveCell.Offset(0, 1).Select
ActiveCell.Value = status
ActiveCell.Offset(0, 1).Select
ActiveCell.PasteSpecial xlPasteValues

Range("a2000").Select
Do
If IsEmpty(ActiveCell) = False Then
ActiveCell.Offset(1, 0).Select
End If
Loop Until IsEmpty(ActiveCell) = True
ActiveCell.Value = (foName)
CutCopyMode = False
Range("range1").Clear

For Each Wb In Workbooks
If Wb.Name <> AWb Then
Wb.Close savechanges:=True
End If
Next Wb
Worksheets("log").Select
Range("a1").Select
Unload Me

End Sub