2 words in A Cell split into 2 textBoxes on userform

[rlink_23]

I always start my new thread as "I have this sheet" or "I have this userform. I am not going to do that today.. lol I am going to start by thanking all of you and your contributions to helping me learn and understand VBA. I took this project on that it might be over my head a little bit and actually have only gotten stuck a few times. and There has always been someone to help me when needed. I appreciate all of you so very much. And I really mean that. Even if I have a post that gets no comments it drives me to figure it out. Ok, now on to my Workbook.

This may be simple or it may not be doable, but on My UserForm I have First Name & Last Name, When the save button is clicked it saves the info in my sheet in one cell. But now I want the people using the workbook to be able to pull info back into the UserForm (I have this set up already except for these last few textBoxes) I can't figure out how to split the cell words back into their respected TextBoxes. Is this doable? am I able to Split the contents of a cell into 2 different TextBoxes? Thank you for any input you may have!!

 

[jproffer]

TextBox1.Value = Left(Range("A1").Value,Instr(Range("A1").Value," ")-1)

Textbox2.Value = Right(Range("A1").Value,Len(Range("A1").Value)-Instr(Range("A1").Value)


ehhhh. I think . I won't be able to test it until tomorrow, but it looks right. See what happens.

 

[rlink_23]

I will test it here shortly! Thank you!!

 

[rlink_23]

I also just thought I have one cell that has 3 words in it. So that would be left right and mid?

 

[jproffer]

Correct. You would have to use the Instr to find the second space to stop it though. I'll have to be at work to do that because I KNOW I'd miss an opening or closing parenthesis or something with that one

If nobody else gets to it, I'll do a little testing tomorrow and get back to ya.

 

[rlink_23]

Well Its not just a particular cell so I tried to add it to this code in the same way but changed a few things to closely match the rest. Any other idea? It gave me a run time error 'Method Range of Object_Global Failed

Dim I As Long, LastRow As LongLastRow = Sheets("Quotes").Range("A" & Rows.Count).End(xlUp).Row
For I = 2 To LastRow
If Sheets("Quotes").Cells(I, "A").Value = TextBox9 Or _
Sheets("Quotes").Cells(I, "A").Value = Val(TextBox9) Then
Me.quotenumber = Sheets("Quotes").Cells(I, "A").Value
Me.Date1 = Sheets("Quotes").Cells(I, "B").Value
[COLOR=#ff0000][B]FirstName.Value = Left(Range("Quotes").Cells(I, I).Value, InStr(Range(I, I).Value, " ") - 1)[/B][/COLOR]
Me.size = Sheets("Quotes").Cells(I, "D").Value
Me.ComboBox1 = Sheets("Quotes").Cells(I, "E").Value
Me.Cost = Sheets("Quotes").Cells(I, "F").Value
Me.custnumber = Sheets("Quotes").Cells(I, "G").Value
Me.company = Sheets("Quotes").Cells(I, "H").Value
Me.Phone1 = Sheets("Quotes").Cells(I, "J").Value
Me.City = Sheets("Quotes").Cells(I, "K").Value
Me.State = Sheets("Quotes").Cells(I, "L").Value
Me.ZipCode = Sheets("Quotes").Cells(I, "M").Value
Me.Email = Sheets("Quotes").Cells(I, "N").Value
Me.Initals = Sheets("Quotes").Cells(I, "P").Value
Me.shipadd1 = Sheets("Quotes").Cells(I, "Q").Value
Me.shipadd2 = Sheets("Quotes").Cells(I, "R").Value
Me.shipco = Sheets("Quotes").Cells(I, "S").Value
Me.shipadd1 = Sheets("Quotes").Cells(I, "U").Value
Me.shipadd2 = Sheets("Quotes").Cells(I, "V").Value
Me.shipcity = Sheets("Quotes").Cells(I, "W").Value
Me.shipstate = Sheets("Quotes").Cells(I, "X").Value
Me.shipzip = Sheets("Quotes").Cells(I, "Y").Value
Me.shipphone = Sheets("Quotes").Cells(I, "Z").Value
Me.shipemail1 = Sheets("Quotes").Cells(I, "AA").Value
Me.TAW = Sheets("Quotes").Cells(I, "AB").Value
End If
Next
CommandButton6.Enabled = False
CommandButton7.Enabled = False

End Sub

Basically this pulls the info back into the Userform when the "Quotenumber is input it to TextBox9

 

[Rick Rothstein]

Here is another way...

Dim Words() As String
Words = Split(Range("A1").Value)
TextBox1.Value = Words(0)
TextBox2.Value = Words(1)
If UBound(Words) >1 Then TextBox3.Value = Words(2)

 

[jproffer]

FirstName.Value = Left(Range("Quotes").Cells(I, I).Value, InStr(Range("Quotes").Cells(I, I).Value, " ") - 1)

Only thing I see without testing it (at work tomorrow, unfortunately) is this. Specify the range and the cells within.


EDIT: I like Rick's way better than mine . See if that works for you.

 

[rlink_23]

Hmmmm, I will play with it. Not sure how to incorporate Ricks way into the sub.

 

[rlink_23]

I added it but get a compile error Expected array

Private Sub TextBox9_Change()
Dim I As Long, LastRow As Long
[COLOR=#ff0000]Dim Words As String[/COLOR]
[COLOR=#ff0000]Words = Split(Range("I:I").Value)[/COLOR]
LastRow = Sheets("Quotes").Range("A" & Rows.Count).End(xlUp).Row
For I = 2 To LastRow
If Sheets("Quotes").Cells(I, "A").Value = TextBox9 Or _
Sheets("Quotes").Cells(I, "A").Value = Val(TextBox9) Then
Me.quotenumber = Sheets("Quotes").Cells(I, "A").Value
Me.Date1 = Sheets("Quotes").Cells(I, "B").Value
[COLOR=#ff0000]FirstName.Value = Words(0)[/COLOR]