2 words in A Cell split into 2 textBoxes on userform

rlink_23

Board Regular
Joined
Oct 30, 2015
Messages
149
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!! :)
 
This is the best I have come up with and still object fails... :/


Code:
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)
 
Last edited:
Upvote 0

Excel Facts

What is the shortcut key for Format Selection?
Ctrl+1 (the number one) will open the Format dialog for whatever is selected.
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
 
Upvote 0
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
 
Upvote 0
This is the best I have come up with and still object fails... :/


Code:
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)
 
Last edited:
Upvote 0
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?
 
Upvote 0
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?

Code:
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
 
Last edited:
Upvote 0

Forum statistics

Threads
1,223,099
Messages
6,170,107
Members
452,302
Latest member
TaMere

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