Opps, something else came up that I do not know how to do!!!

I made up a little test worksheet to test out Mike's code but when I
tried a slightly different set of data, I ran into a limitation.

If I put two names into the four TextBoxes (first name & last name of
the first person into the first two TextBoxes and the first & last
name of the second person into TextBoxes 3 & 4)

Eg. Billy Smith in the 1st two TextBoxes and Sue Davis in the Second
two TextBoxes Will show in TextBox5 as:

Billy Smith Sue Davis

But what I would like to see is:

Smith, Billy & Davis, Sue

Can I get this from this code?
_______________________________________

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

Any held will be appreciated.

-Minitman


On Sun, 20 Jul 2008 08:16:13 -0500, Minitman
wrote:

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
wrote:

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

"Minitman" wrote:

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