Ignore blank textboxes when joining strings using vba

Question

A UserForm has 6 TextBoxes. These are to record addresses.

I.E. address line 1, address line 2 and so on.

TB1 and TB6 (post code /zip) are mandatory.

Using the following, I can concatenate all six into one cell, seperated by a comma, on a spreadsheet.

The problem is when there are one or more empty TBs the result looks a bit messy with too many commas.

code:

.Range("F" & lngNewRow).Value = Me.TextBox1.Value & ", " & _ Me.TextBoxa2.Value & ", " & Me.TextBox3.Value _

 & vbLf & Me.TextBox4.Value & ", " & Me.TextBox5.Value & ", " & Me.TextBox6.Value

The result would look something like this:

15 High Street,,

,,SE13 2EX

Not Brilliant. Any ideas?

Thanks.

Answer 1

I think the following is a cleaner code and easily scaleable for any number of textboxes.

Note: I used Option Base 1 to force the array having Lbound=1 , this way it matches the textbox index , but if you prefer working with zero base array then remove it and change the loop from 0 to 4.

Option Explicit

Option Base 1

Private Sub CommandButton1_Click()

Dim li As Long

Dim aTextBoxes()

Dim strConcatenate As String

aTextBoxes = Array(TextBox2, TextBox3, TextBox4, TextBox5, TextBox6)

strConcatenate = TextBox1.Text

For li = 1 To 5

  If aTextBoxes(li) <> vbNullString Then

     strConcatenate = strConcatenate & "," & aTextBoxes(li)

  End If

Next

.Range("F" & lngNewRow).Value = strConcatenate

End Sub

---

Best regards, Harvey

Answer 2

To do it right, you will need a temporary String variable to manipulate while you straighten the text out. Here is the code snippet to do that...

Dim TempString As String

....

....

TempString = Me.TextBox1.Value & ", " & Me.TextBox2.Value & ", " & Me.TextBox3.Value & vbLf & _

             Me.TextBox4.Value & ", " & Me.TextBox5.Value & ", " & Me.TextBox6.Value

Do While InStr(TempString, ", , ")

  TempString = Replace(TempString, ", , ", ", ")

Loop

TempString = Replace(TempString, ", " & vbLf, vbLf)

TempString = Replace(TempString, vbLf & ", ", vbLf)

If Left(TempString, 2) = ", " Then TempString = Mid(TempString, 3)

If Right(TempString, 2) = ", " Then TempString = Left(TempString, Len(TempString) - 2)

.Range("F" & lngNewRow).Value = TempString

---

NOTE: Please mark the message or messages (yes, you can mark more than one) that answer your question as the "Answer" so that others will know your question has been resolved.

Answer 3

I noticed that you have this annoying habit of replying to solutions that are superior to yours, and you are in such a hurry that you don’t even read the text you quoted.

I usually don’t respond to confused people who are beyond help, but this was really just too good to pass.

>> You forgot TextBox1

And you defiantly need to see a ophthalmologist, or you just can't read a simple code?!  

>> I probably would affix either ".Text" or ".Value" after each of the TextBox names in your Array function call (I hate relying on default properties as they make code harder to read).

You are entitled to your own opinion, and can hate as much as you want.  I don’t have problem reading the code an everyone with basic knowledge of VBA knows what is the default property of a textbox. If you had formal traing in CS or SE and had some expierence progarmming OO languages you wouldn't make that comment 

>> Also, the OP's code shows there is a space after the comma which you left out

Really !

>>I would not cast your code as a macro (Sub) because it is missing the With statement that the dot in front of the Range statement is referring to (which probably means there is other attending code missing as well)

I used OPs code there. My code was just a an addition to his code. Not intended to rewrite the whole sub.

Have you looked at your reply , where is the with statement in your code??! Don't you really realize that thousands of people are going to read this a laugh at you with such a foolish comments ?

>> Finally, I am guessing you Option Base set to 1 since you start iterating your array at 1 instead of the 0 most users would need to use (I am assuming most users are letting their Option Base default to the default value of 0)... if that is in fact your setting, then you should mention it so as not to confuse readers trying to use your posted code.

Seriously you need to see a ophthalmologist , I highly recommend a neurologist as well . Don’t you really see the Bold font and my note !!!?

And finally ! get a life !

---

Best regards, Harvey

Answer 4

I think the following is a cleaner code and easily scaleable for any number of textboxes.

Note: I used Option Base 1 to force the array having Lbound=1 , this way it matches the textbox index , but if you prefer working with zero base array then remove it and change the loop from 0 to 4.

Option Explicit

Option Base 1

Private Sub CommandButton1_Click()

Dim li As Long

Dim aTextBoxes()

Dim strConcatenate As String

aTextBoxes = Array(TextBox2, TextBox3, TextBox4, TextBox5, TextBox6)

strConcatenate = TextBox1.Text

For li = 1 To 5

  If aTextBoxes(li) <> vbNullString Then

     strConcatenate = strConcatenate & "," & aTextBoxes(li)

  End If

Next

.Range("F" & lngNewRow).Value = strConcatenate

End Sub

It look like you did not insert the Line Feed character that the OP shows being inserted between the text from the 3rd and 4th textbox and you forgot TextBox1. I probably would affix either ".Text" or ".Value" after each of the TextBox names in your Array function call (I hate relying on default properties as they make code harder to read). Also, the OP's code shows there is a space after the comma which you left out. Also, I would not cast your code as a macro (Sub) because it is missing the With statement that the dot in front of the Range statement is referring to (which probably means there is other attending code missing as well). Finally, I am guessing you Option Base set to 1 since you start iterating your array at 1 instead of the 0 most users would need to use (I am assuming most users are letting their Option Base default to the default value of 0)... if that is in fact your setting, then you should mention it so as not to confuse readers trying to use your posted code.

---

NOTE: Please mark the message or messages (yes, you can mark more than one) that answer your question as the "Answer" so that others will know your question has been resolved.

Answer 5

Use something like this:

Dim strAddy As String

strAddy = Me.TextBox1

If Me.TextBox2 <> "" Then

    strAddy = strAddy & ", " & Me.TextBox2

End If

...

...

.Range("F" & lngNewRow).Value = strAddy