Userform - Autofill

[wrightyrx7]

Hello all,

I have created a userform for a spreadsheet that already holds alot of data. Just to make it easier to input in the future.

How can i get the Textbox's to autofill from data already on the spreadsheet?

Private Sub cmdAdd_waiting_list_Click()
Dim iRow As Long
Dim ws As Worksheet
Set ws = Worksheets("Waiting List")

'find first empty row in database
iRow = ws.Cells(Rows.Count, 1) _
.End(xlUp).Offset(1, 0).Row


'copy the data to the database
ws.Cells(iRow, 1).Value = Me.TextBox1.Value
ws.Cells(iRow, 2).Value = Me.TextBox2.Value
ws.Cells(iRow, 3).Value = Me.TextBox3.Value
ws.Cells(iRow, 4).Value = Me.TextBox13.Value
ws.Cells(iRow, 5).Value = Me.TextBox11.Value
ws.Cells(iRow, 6).Value = Me.ComboBox1.Value
ws.Cells(iRow, 7).Value = Me.TextBox4.Value
ws.Cells(iRow, 8).Value = Me.TextBox5.Value
ws.Cells(iRow, 9).Value = Me.TextBox6.Value
ws.Cells(iRow, 10).Value = Me.TextBox7.Value
ws.Cells(iRow, 11).Value = Me.TextBox8.Value
ws.Cells(iRow, 12).Value = Me.TextBox9.Value
ws.Cells(iRow, 13).Value = Me.TextBox10.Value
ws.Cells(iRow, 14).Value = Me.TextBox12.Value
ws.Cells(iRow, 15).Value = Me.TextBox15.Value
ws.Cells(iRow, 16).Value = Me.TextBox16.Value
ws.Cells(iRow, 17).Value = Me.TextBox14.Value
ws.Cells(iRow, 18).Value = Me.TextBox17.Value
ws.Cells(iRow, 19).Value = Me.TextBox18.Value
ws.Cells(iRow, 20).Value = Me.TextBox19.Value
ws.Cells(iRow, 21).Value = Me.TextBox20.Value
ws.Cells(iRow, 22).Value = Me.TextBox25.Value
ws.Cells(iRow, 23).Value = Me.TextBox24.Value
ws.Cells(iRow, 24).Value = Me.ComboBox21.Value
ws.Cells(iRow, 25).Value = Me.TextBox22.Value
ws.Cells(iRow, 26).Value = Me.TextBox23.Value


'clear the data
Me.TextBox1.Value = ""
Me.TextBox2.Value = ""
Me.TextBox3.Value = ""
Me.TextBox4.Value = ""
Me.TextBox5.Value = ""
Me.TextBox6.Value = ""
Me.TextBox7.Value = ""
Me.TextBox8.Value = ""
Me.TextBox9.Value = ""
Me.TextBox10.Value = ""
Me.TextBox11.Value = ""
Me.TextBox12.Value = ""
Me.TextBox13.Value = ""
Me.TextBox14.Value = ""
Me.TextBox15.Value = ""
Me.TextBox16.Value = ""
Me.TextBox17.Value = ""
Me.TextBox18.Value = ""
Me.TextBox19.Value = ""
Me.TextBox20.Value = ""
Me.ComboBox21.Value = ""
Me.TextBox22.Value = ""
Me.TextBox23.Value = ""
Me.TextBox24.Value = ""
Me.TextBox25.Value = ""
Me.ComboBox1.Value = "Unknown"
Me.TextBox1.SetFocus
MsgBox ("Data Successfully added to Waiting List")

End Sub
Private Sub UserForm_Initialize()

ComboBox1.List = Array("Mr", "Mrs", "Miss", "Ms", "Dr")
ComboBox21.List = Array("Moston", "Openshaw", "External")
Me.TextBox22.Text = Format(Date, "dd/mm/yyyy")
Me.TextBox23.Text = Format(Date, "dd/mm/yyyy")
End Sub

Private Sub TextBox17_Change()
TextBox18_Change
End Sub
Private Sub TextBox18_Change()
TextBox19.Enabled = False
TextBox20.Enabled = False
If IsNumeric(TextBox18.Value) Then
TextBox19 = TextBox18.Value * 0.95
End If
If IsNumeric(TextBox17.Value) And _
IsNumeric(TextBox19.Value) Then
TextBox20 = TextBox19.Value * TextBox17.Value
End If
End Sub

Private Sub cmdClose_Click()
Unload Me
End Sub

Many thanks
Chris

 

[AD_Taylor]

I can see what you're doing so far with the UserForm

How exactly do you want the populating from the worksheet to work?

 

[wrightyrx7]

Hello thanks for the reply, I just want the texbox to look at the column it inputs to and as I start typing in the textbox its has some sort of autocomplete..which finishes the word off. Hope I explained that well enough.

Thanks again
Chris

 

[AD_Taylor]

Hello thanks for the reply, I just want the texbox to look at the column it inputs to and as I start typing in the textbox its has some sort of autocomplete..which finishes the word off. Hope I explained that well enough.

Thanks again
Chris

Just so I know I've got this right...

You want a similar sort of thing to how Excel knows what entries are already in which columns and when you type the first few characters of a string into one of the text boxes, you want it to try and find these potential matches?

If so, its either extremely difficult or impossible to do. I'd air on the side of impossible myself...

I've just had a look and there is a function that you can call from a key press while in a text box. So you could build some code around that key press event that looks in the appropriate column and returns possible matches.
The only problem then is where does it return the matches too? If its into the text box then it'll just place the whole word in for you, not leave the character in the same place and show the rest of the word...

 

[wrightyrx7]

Maybe i could change the textbox to a combobox an point it to the column that it puts that data into?

You think this will be possible?

Thanks
Chris