Prev: If Value is > 0, in a named range ??
Next: Place VBA in cell on spreadsheet
From: Minitman on 20 Jul 2008 03:44
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

From: Mike H on 20 Jul 2008 04:53
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
>
>
From: Nigel on 20 Jul 2008 05:59
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
nigelnospam(a)9sw.co.uk



"Minitman" <steve(a)minitmaidsofaustin.com> wrote in message
news:09q584hijsp2oms5ird3f939lkfauoj7np(a)4ax.com...
> 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
>

From: Bob Phillips on 20 Jul 2008 07:27
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" <steve(a)minitmaidsofaustin.com> wrote in message
news:09q584hijsp2oms5ird3f939lkfauoj7np(a)4ax.com...
> 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
>


From: Minitman on 20 Jul 2008 09:16
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
<MikeH(a)discussions.microsoft.com> 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
>>
>>

 |  Next  |  Last
Pages: 1 2
Prev: If Value is > 0, in a named range ??
Next: Place VBA in cell on spreadsheet