Concatener si textbox non vide

[Joulsy]

Bonjour à tous,
J'ai besoin de concatener le contenu de Texbox (20 en tout) mais seulement si elle ne sont pas vide.
J'ai ce code qui ne prend pas en compte les cellules vides et comme vous l'imaginez, ce n'est pas très ... beau.
Y a t'il une solution plus simple ? (ou plus compliqué, mais qui fonctionne)

With Sheets("Mouvement")
   Dim x As Long
x = .Columns(1).Find("", .Cells(Rows.Count, 1), xlValues, , 1, 1, 0).Row
   .Cells(x, 1) = TextBox4.Value
     .Cells(x, 5) = ASSdep1.Value & "," & " " & ASSdep1.Value & "," & " " & ASSdep2.Value & "," & " " & ASSdep3.Value & "," & " " & ASSdep4.Value & "," & " " & ASSdep5.Value & "," & " " & ASSdep6.Value & "," & " " & ASSdep7.Value & "," & " " & ASSdep8.Value & "," & " " & ASSdep9.Value & "," & " " & ASSdep10.Value & "," & " " & ASSdep11.Value & "," & " " & ASSdep12.Value & "," & " " & ASSdep13.Value & "," & " " & ASSdep14.Value & "," & " " & ASSdep15.Value & "," & " " & ASSdep16.Value & "," & " " & ASSdep17.Value & "," & " " & ASSdep18.Value & "," & " " & ASSdep19.Value & "," & " " & ASSdep20.Value
 
  End With
End Sub

Merci à vous

 

[VDAVID]

Re : Concatener si textbox non vide

​

Bonjour Joulsy,
Peut-être comme ceci:

With Sheets("Mouvement")
   Dim x As Long, z As Byte
x = .Columns(1).Find("", .Cells(Rows.Count, 1), xlValues, , 1, 1, 0).Row
   .Cells(x, 1) = TextBox4.Value
For z = 1 To 20
If Me.Controls("ASSdep" & z).Value <> "" Then
If z = 1 Then
.Cells(x,5).Value = Me.Controls("ASSdep" & z).Value
Else
.Cells(x,5).Value = .Cells(x,5).Value & ", " & Me.Controls("ASSdep" & z).Value
End if
Next z 
  End With
End Sub

Bonne journée

 

[Pierrot93]

Re : Concatener si textbox non vide

Bonjour,

fais peut être une boucle, regarde ceci, espérant que cela puisse t'aider à avancer, à utiliser dans le module de l'userform où sont placé les "textbox" :

Dim i As Byte, t As String
For i = 1 To 20
    With Me.Controls("TextBox" & i)
        If .Value <> "" Then t = t & .Value & ", "
    End With
Next i
Cells(1, 1) = Mid(t, 1, Len(t) - 2)

bon après midi
@+

Edition : bonjour VDAVID, un peu en retard moi....