View original (French)

Store the TextBox value if not empty.

Solved
thesentry Posted messages 1092 Status Member -  
thesentry Posted messages 1092 Status Member -
Bonjour,

I have several textboxes on a UserForm. Some textboxes must be filled in, while others do not have to be.

I would like to store on line 1 of my Excel sheet the data from the non-empty textboxes.

I tried this: 

Private Sub CommandButton1_Click()
Dim X As Variant

X = ""

For i = 1 To 10
 If UserForm1.Controls("TextBox" & i).Value <> "" Then
 UserForm1.Controls("TextBox" & i).Value = X
 Sheets("Feuil1").Range("Z1").End(xlToLeft).Offset(0, 1).Value = X
 X = ""
 End If
Next i
End Sub

but I didn't get what I expected; all my textboxes are emptied and nothing is recorded on my sheet...

Any ideas? Thanks

Configuration: Windows XP / Firefox 10.0.7

2 answers

Answer 1 / 2
f894009 Posted messages 17417 Registration date   Status Member Last intervention   1 717
 
Hello,

it will be better like this: 

Private Sub CommandButton1_Click()
 Dim X As Variant
 For i = 1 To 10
 If UserForm1.Controls("TextBox" & i).Value <> "" Then
 Sheets("Feuil1").Range("Z1").End(xlToLeft).Offset(0, 1).Value = UserForm1.Controls("TextBox" & i).Value
 End If
 Next i

End Sub
0
thesentry Posted messages 1092 Status Member 16
 
You're welcome!
0
Answer 2 / 2
thesentry Posted messages 1092 Status Member 16
 
Euh... Re

Now, I have everything on line 1 of my Excel cell

I would like to display, in another textbox, a summary composed as follows:

Sheets("Sheet1").Range("A1").Value & vbCrLf _
Sheets("Sheet1").Range("B1").Value & vbCrLf _
Sheets("Sheet1").Range("C1").Value & vbCrLf _
Sheets("Sheet1").Range("D1").Value & vbCrLf _
Etc...

But I can't write it like that since the length of the line depends on what the user has entered. So I need to generalize this.

A big command that reads from right to left, up to the LastFilledCell and displays everything while returning to the next line each time.
0
thesentry Posted messages 1092 Status Member 16
 
Succeeded.
0