Combining TextBox Values With Spaces

[Minitman]

Greetings,

I have four Textboxes on a UserForm that may or may not have anything
in them. I am trying to combine the contents of the 4 TextBoxes plus
one space between each value into a 5th Textbox. I tried brute force
and ended up with a long sub (about 130 lines - long lines wrapped for
legibility. Will post if needed).

The TextBoxes contain text. Which is why I need spaces between the
text. I have no way of knowing which of the four are going to have
anything in them.

Any ideas as to a better way to do this?

Any help will be appreciated.

-Minitman

 

[Mike H]

Hi,

It's hard to judge whether this way is 'better' because you don't post you
code. However, this routine initiated by a command button on the sheet (It
could be called in other ways) loops through all textboxes on a userform and
if a box contains text the strings are concatenated together.

Private Sub CommandButton1_Click()
For Each Cont In Me.Controls
If TypeName(Cont) = "TextBox" Then
If Len(Cont.Text) > "" Then
mystring = mystring & Cont.Text & " "
End If
End If
Next Cont
MsgBox mystring
End Sub

Mike

 

[Nigel]

Assuming you want all spaces except between textbox values removed then try
this one liner......

TextBox5.Value = Trim(Trim(TextBox1.Value) & " " & _
Trim(Trim(TextBox2.Value) & " " & _
Trim(Trim(TextBox3.Value) & " " & _
Trim(TextBox4.Value))))

 

[Bob Phillips]

Dim i As Long

With Me
For i = 1 To 4
.TextBox5.Text = .TextBox5.Text & _
IIf(.Controls("TextBox" & i).Text <> "", .Controls("TextBox"
& i).Text, "") & _
IIf(i < 4, " ", "")
Next i
End With

--
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)

 

[Minitman]

Hey Mike,

Thanks for reply.

Your code modified gets me very close.

Here is the modification:

_________________________________________________________________________

Public Sub CompositeAddress(sAdd As String)
Dim i As Integer
Dim iNameTo As Integer
Dim iAdd1 As Integer
Dim myString As String

Select Case sAdd
Case "BA"
iAdd1 = 14
iNameTo = 3
Case "SA"
iAdd1 = 22
iNameTo = 4
Case Else
MsgBox "You must use BA for Billing Address " _
& "or SA for Service Address ONLY"
Exit Sub
End Select

myString = vbNullString
For i = 0 To 3
If Not Me.Controls("C_" & iAdd1 + i).Text = vbNullString _
Then myString = _
myString & Me.Controls("C_" & iAdd1 + i).Text & " "
Next i
Me.Controls("C_" & iNameTo ).Text = MyString

End Sub
___________________________________________________________________

This code works great except for one minor item, it appears that this
code will always leave a trailing space after the For/Next loop is
complete.

Is there a way to remove this trailing space from the finale result?

Again, thanks for this code, it is a LOT smaller then what I had.

-Minitman



Sun, 20 Jul 2008 01:53:01 -0700, Mike H

 

[Minitman]

Hey Nigel,

Thanks for the reply.

Trim was indeed the command I needed to add to Mike's code to finish
this question.

Thank you for showing me that.

-Minitman

 

[Minitman]

Hey Bob,

Thanks for the reply.

At first I thought IIF was a typo! But then I check it out in msHelp
and found that it was not a typo, but a command I had never heard of!

It looks very interesting indeed.

As for the spacing problem, it was solved with Trim (see previous
posts).

I'm going to check out this IIF and see where it leads me.

Thank you for show me that command.

-Mini