Mutiline Cell to return values to (3) textboxes on userform

[JaredMcCullough]

I am working on a project where one cell will have a the conjunction of (3) textbox values from a userform. I then would like to reverse populate the textboxes on a separate userform. So essentially what I need is userform initialize to do such:

The outgoing code works as such:

Rng.Offset(, 2).Value = Textbox1.Value & Chr(10) & Textbox2.Value & Chr(10) & Textbox3.Value

So the incoming code to the userform will need to do the opposite

Populate Textbox1 = The first string of text in the multiline cell
Populate Textbox2 = The second string of text
Populate Textbox3 = The third string of text

I am not sure if this is possible but if it would help I have also thought of using indicators such as

Rng.Offset(, 2).Value = [A] & Textbox1.Value & Chr(10) & & Textbox2.Value & Chr(10) & [C] Textbox3.Value

and using some form of value to return the string in manner where Textbox.Value = String between [A] and

 

[Rick Rothstein]

Put this with your variable declarations...

Dim Lines() As String

and assuming Rng is set somewhere earlier in the code, use this to populate the TextBoxes...

Lines = Split(Rng.Offset(, 2).Value, vbLf)
TextBox1.Value = Lines(0)
TextBox2.Value = Lines(1)
TextBox3.Value = Lines(2)

 

[JaredMcCullough]

Put this with your variable declarations...

Dim Lines() As String

and assuming Rng is set somewhere earlier in the code, use this to populate the TextBoxes...

Lines = Split(Rng.Offset(, 2).Value, vbLf)
TextBox1.Value = Lines(0)
TextBox2.Value = Lines(1)
TextBox3.Value = Lines(2)

Rick,

As this is the first exposure I have really had to something of the such will this correlate to lines that carry over (at least visually) onto the next line. Meaning that if I have a string that at least in the cell goes onto the next line will it split this text up.

 

[Rick Rothstein]

Rick,

As this is the first exposure I have really had to something of the such will this correlate to lines that carry over (at least visually) onto the next line. Meaning that if I have a string that at least in the cell goes onto the next line will it split this text up.

You said you wanted to "reverse" this...

Rng.Offset(, 2).Value = Textbox1.Value & Chr(10) & Textbox2.Value & Chr(10) & Textbox3.Value

That is what the code I posted does... the vbLf that I used in my code is a built-in VB constant equivalent to Chr(10)... the Split function separates delimited text into a zero-based array where each delimited text gets assigned (automatically) to its own array element.

 

[JaredMcCullough]

You said you wanted to "reverse" this...

Rng.Offset(, 2).Value = Textbox1.Value & Chr(10) & Textbox2.Value & Chr(10) & Textbox3.Value

That is what the code I posted does... the vbLf that I used in my code is a built-in VB constant equivalent to Chr(10)... the Split function separates delimited text into a zero-based array where each delimited text gets assigned (automatically) to its own array element.

Rick I have tried the following code but it does not seem to work:

This is what opens the userform as well as sets the range:

Private Sub CommandButton1_Click()
  CVF05.TextBox1.Value = ComboBox1.Value
  CVF05.Show
End Sub

This in turn opens userform "CVF05" which has the following code:

Private Sub CommandButton1_Click()
Dim Rng As Range
Dim Lines() As String

Set Rng = Sheets("Variance Database").Range("A7:A" & Sheets("Variance Database").Range("A" & Rows.count).End(xlUp).Row).Find(Me.TextBox1.Value)
    
Lines = Split(Rng.Offset(, 15).Value, vbLf)
Me.TextBox4.Value = Lines(0)
Me.TextBox5.Value = Lines(1)
Me.TextBox6.Value = Lines(2)
End Sub

 

[Rick Rothstein]

Rick I have tried the following code but it does not seem to work:

This is what opens the userform as well as sets the range:

Private Sub CommandButton1_Click()
  CVF05.TextBox1.Value = ComboBox1.Value
  CVF05.Show
End Sub

This in turn opens userform "CVF05" which has the following code:

Private Sub CommandButton1_Click()
Dim Rng As Range
Dim Lines() As String

Set Rng = Sheets("Variance Database").Range("A7:A" & Sheets("Variance Database").Range("A" & Rows.count).End(xlUp).Row).Find(Me.TextBox1.Value)
    
Lines = Split(Rng.Offset(, 15).Value, vbLf)
Me.TextBox4.Value = Lines(0)
Me.TextBox5.Value = Lines(1)
Me.TextBox6.Value = Lines(2)
End Sub

The Split function works on a single text string... you are setting Rng to multiple cells. Split won't work for multiple cells, but assuming your multiple cells are correct, exactly how did you want them to fill three individual TextBoxes?

 

[JaredMcCullough]

The Split function works on a single text string... you are setting Rng to multiple cells. Split won't work for multiple cells, but assuming your multiple cells are correct, exactly how did you want them to fill three individual TextBoxes?

Rick.

The function of setting the range from my understanding provides for a row in the defined spreadsheet "variance database" to be set based upon it being equal to the Textbox1.Value

Rng.Offset(, 15) should be the column O cell value for the row that matches Textbox1.value

So essentially if Textbox1.Value is in Row4 then it should be O4.

Unless I am interpreting this incorrectly?