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
(E-Mail Removed)



"Minitman" <(E-Mail Removed)> wrote in message
news:(E-Mail Removed)...
> 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" <(E-Mail Removed)> wrote in message
news:(E-Mail Removed)...
> 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
<(E-Mail Removed)> 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"
<nigel-(E-Mail Removed)> 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"
<(E-Mail Removed)> 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
<(E-Mail Removed)> 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
><(E-Mail Removed)> 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" <(E-Mail Removed)> wrote in message
news:(E-Mail Removed)...
> 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
> <(E-Mail Removed)> 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
>><(E-Mail Removed)> 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"
<(E-Mail Removed)> 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" <(E-Mail Removed)> wrote in message
>news:(E-Mail Removed)...
>> 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
>> <(E-Mail Removed)> 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
>>><(E-Mail Removed)> 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
>>>>>
>>>>>
>>
>