Excel text boxes to a txt file

[cmhrider]

I'm trying to have some text boxes in Excel write to a .txt file. The code I have will write as I would like but I would like the the text boxes with no data to be ignored.

Private Sub CommandButton2_Click()
    text1 = "MESSAGES" & vbCrLf
    text1 = text1 & Worksheets("Sheet1").Range("C3").Value & vbCrLf
    text1 = text1 & Worksheets("Sheet1").Range("C5").Value & vbCrLf
    text1 = text1 & Worksheets("Sheet1").Range("C7").Value & vbCrLf
    text1 = text1 & Worksheets("Sheet1").Range("C9").Value & vbCrLf
    text1 = text1 & Worksheets("Sheet1").Range("C12").Value & vbCrLf
    text1 = text1 & Worksheets("Sheet1").Range("C14").Value & vbCrLf
    text1 = text1 & Worksheets("Sheet1").Range("C16").Value & vbCrLf
     Dim fil As Integer
     Dim strTmpText As String
     fil = FreeFile()
     Filename = "U:\OQS\Case Analysis Team\Wheeler\NJE Daily copy" & "\" & "tickerdata.txt"
     Open Filename For Output As #fil
     'Open Filename For Append As #fil

         Print #fil, text1

     Close #fil
End Sub

Private Sub CommandButton10_Click()
TextBox1.Value = ""
TextBox2.Value = ""
TextBox3.Value = ""
TextBox4.Value = ""
TextBox5.Value = ""
TextBox6.Value = ""
TextBox7.Value = ""
End Sub

Private Sub CommandButton1_Click()

Filename = "U:\OQS\Case Analysis Team\Wheeler\NJE Daily copy" & "\" & "tickerdata.txt"
Open Filename For Input As #1
On Error Resume Next
Line Input #1, d1
Line Input #1, d2
TextBox1.Text = d2
Line Input #1, d3
TextBox2.Text = d3
Line Input #1, d4
TextBox3.Text = d4
Line Input #1, d5
TextBox4.Text = d5
Line Input #1, d6
TextBox5.Text = d6
Line Input #1, d7
TextBox6.Text = d7
Line Input #1, d8
TextBox7.Text = d8
Close #1
On Error GoTo 0





End Sub

Private Sub CommandButton3_Click()
TextBox1.Value = ""
End Sub

Private Sub CommandButton4_Click()
TextBox2.Value = ""
End Sub

Private Sub CommandButton5_Click()
TextBox3.Value = ""
End Sub

Private Sub CommandButton6_Click()
TextBox4.Value = ""
End Sub

Private Sub CommandButton7_Click()
TextBox5.Value = ""
End Sub

Private Sub CommandButton8_Click()
TextBox6.Value = ""
End Sub

Private Sub CommandButton9_Click()
TextBox7.Value = ""
End Sub

Private Sub TextBox1_Change()
Worksheets("Sheet1").Range("C3").Value = TextBox1.Value
End Sub

Private Sub TextBox2_Change()
Worksheets("Sheet1").Range("C5").Value = TextBox2.Value
End Sub

Private Sub TextBox3_Change()
Worksheets("Sheet1").Range("C7").Value = TextBox3.Value
End Sub

Private Sub TextBox4_Change()
Worksheets("Sheet1").Range("C9").Value = TextBox4.Value
End Sub

Private Sub TextBox5_Change()
Worksheets("Sheet1").Range("C12").Value = TextBox5.Value
End Sub

Private Sub TextBox6_Change()
Worksheets("Sheet1").Range("C14").Value = TextBox6.Value
End Sub

Private Sub TextBox7_Change()
Worksheets("Sheet1").Range("C16").Value = TextBox7.Value
End Sub

When the data in the text boxes writes to the txt file there are empty lines under the data that was in the text box. I don't want those empty lines. I'm using the txt file to populate a scrolling text field and the empty lines are "appearing" in the scrolling data. Thank you for your help. Please use as much plain english as possible I'm new to this stuff.

 

[SkipVought]

hi,

but I would like the the text boxes with no data to be ignored.

Well then test each textbox.text to determine if there is data, before concatenating.

Please post future VBA code questions in forum707, for better responses.

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[cmhrider]

Thanks! I have reposted this in the suggested forum because I'm not sure how to do what you suggested.