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!!

 

[rlink_23]

This is the best I have come up with and still object fails... :/

Private Sub TextBox9_Change()
Dim I As Long, LastRow As Long
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
Dim Words() As String, LastColumn As Long
LastColumn = Sheets("Quotes").Range("I" & Rows.Count).End(xlUp).Row
Words = Split(Range("I, I").Value)
Me.quotenumber = Sheets("Quotes").Cells(I, "A").Value
Me.Date1 = Sheets("Quotes").Cells(I, "B").Value
FirstName.Value = Words(0)

 

[Rick Rothstein]

This is the best I have come up with and still object fails... :/

On which line of code did the "object fails" occur on?

Also, what is the error number and exact error message?

 

[rlink_23]

Give me a few minutes Rick my home computer did an update and it's now in the dreaded could not update windows undoing changes loop. I'm pulling out my laptop now to give u the info you ask for

 

[rlink_23]

It says Run-time Error 1004

Method range of object_global failed

And the line it errors on is Words = Split(Range(I, "I").Value

 

[rlink_23]

and I have it (I, "I") not like above only one "

 

[Rick Rothstein]

This is the best I have come up with and still object fails... :/

Private Sub TextBox9_Change()
Dim I As Long, LastRow As Long
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
Dim Words() As String, LastColumn As Long
LastColumn = Sheets("Quotes").Range("I" & Rows.Count).End(xlUp).Row
[B][COLOR="#FF0000"]Words = Split(Range("I, I").Value)[/COLOR][/B]
Me.quotenumber = Sheets("Quotes").Cells(I, "A").Value
Me.Date1 = Sheets("Quotes").Cells(I, "B").Value
FirstName.Value = Words(0)

Try changing the highlighted line of code to this and see if that makes it work...

Words = Split(Cells(I, "I").Value)

 

[rlink_23]

That worked! My deepest thanks to the both of you for helping me!!!

 

[rlink_23]

But now I have 3 words in Colum C, that need to the same thing. Do I use "words" still and add to the existing code or do I add another string?

 

[rlink_23]

Nevermind I got it!!!!! YAAASSSS!!!!

 

[rlink_23]

So here is the final coding I have. But the only issue I have is when I clear TextBox9 it clears all of the textBoxes except the ones that we have split. Is there a way that if there is no number in textbox9 it clears those boxes as well?

Private Sub TextBox9_Change()
Dim I As Long, LastRow As Long
Dim Words() As String, LastColumn As Long
Dim Cars() As String, LastCell As Long
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
LastColumn = Sheets("Quotes").Range("I" & Rows.Count).End(xlUp).Row
LastCell = Sheets("Quotes").Range("I" & Rows.Count).End(xlUp).Row
Words = Split(Cells(I, "I").Value)
Cars = Split(Cells(I, "C").Value)
Me.quotenumber = Sheets("Quotes").Cells(I, "A").Value Else TextBox9 ""
Me.Date1 = Sheets("Quotes").Cells(I, "B").Value
On Error Resume Next
FirstName.Value = Words(0)
On Error Resume Next
LastName.Value = Words(1)
Year.Value = Cars(0)
Make.Value = Cars(1)
Model.Value = Cars(2)
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.TextBox7 = Sheets("Quotes").Cells(I, "Q").Value
Me.TextBox8 = 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