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

  1.
    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
    1. thesentry Posted messages 1092 Status Member 16
       
      You're welcome!
      0
  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
    1. thesentry Posted messages 1092 Status Member 16
       
      Succeeded.
      0