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

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

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

--

Regards,
Nigel




"Minitman" wrote in message
...
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

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" wrote in message
...
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

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

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



On Sun, 20 Jul 2008 10:59:59 +0100, "Nigel"
wrote:

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

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.

-Minitman

On Sun, 20 Jul 2008 12:27:04 +0100, "Bob Phillips"
wrote:

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

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

Will you always enter them in the first/lastname format, and always want
them reversed?

--
HTH

Bob

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

"Minitman" wrote in message
...
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

Hey Bob,

Will you always enter them in the first/lastname format, and always want
them reversed?
Yes and yes. (Please note the TextBox5 exemption noted below)

TextBox1 and 3 are reserved for 1st names. TextBox 2 and 4 are
reserved for last names. TextBox6 is where these are combined.

The contents of the four TextBoxes will fall into 3 major categories:

1) Single, one person - TextBox 1 & 2 should have content but 3 & 4
should have none and appear in TextBox6 like so:

"LastName(1), 1sName(1)".

2) Room mates - All four should have content and appear in TextBox6
like so:

"LastName(1), 1stName(1) & LastName(2), 1stName(2)".

3) Married - if last names are same then I need something like this in
TextBox6 -

"LastName(1), 1stName(1) & 1stName(2)".

If Last name is different then I handle it as if they were room mates
(see room mates example above).

There is one last exemption, TextBox5. If there is any thing in
TextBox5, then only that content is entered into TextBox6 unmodified.
It's a company name and supercedes any individuals name in TextBox6

I hope this clarifies what I am trying to accomplish, if not I would
be more then happy to fill in whatever is missing.

Thanks again for your help.

-Minitman



On Mon, 21 Jul 2008 09:32:59 +0100, "Bob Phillips"
wrote:

Will you always enter them in the first/lastname format, and always want
them reversed?

--
HTH

Bob

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

"Minitman" wrote in message
.. .
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