If userForm field is empty, do not copy the value to worksheet

[leopardhawk]

Hello forum friends,

Need some help... I have a userform with eight textboxes that are set to transfer any data in them to specific cells on specific worksheets. I'm trying to figure out how to prevent the userform from transferring data from any fields that are left blank or empty by the user. They may simply want to update one field and I don't want them to have to re-enter all the other fields if that is the case. If there IS any data in a field, then it should be transferred but not if the field is blank. The way it is now, if I open the userform and then simply hit the OK button, it sends ALL the blank fields to my worksheet, something which will cause a lot of consternation with my users. I need it to only transfer data if the field contains something. I hope I am explaining this okay and that it makes sense.

Please let me know if you can help and whether or not you need more information (code etc.)

Thanks!

 

[Yongle]

For each textbox ...something like this

If Len(TextBox1.Text) > 0 Then Sheets("Sheet1").Cells(1, "A") = TextBox1.Text

Note
- if not using .Text property, use your existing method to transfer the value instead of what is in red

 

[Trebor76]

I see Yongle has beaten me to the punch. I'm wondering though that If your text box is empty it won't transfer anything to cell anyway so why do you need to do this?

 

[Yongle]

I'm wondering though that If your text box is empty it won't transfer anything to cell anyway so why do you need to do this?

@Trebor76
to avoid existing values being replaced with empty strings
"They may simply want to update one field and I don't want them to have to re-enter all the other fields if that is the case"

 

[Trebor76]

to avoid existing values being replaced with empty strings

Thanks Yongle. I totally missed that

 

[GWteB]

Another approach could be

Private Sub CommandButton1_Click()
    Dim c   As Control
    For Each c In Me.Controls
        If StrComp(Left(c.Name, 7), "TextBox", vbTextCompare) = 0 Then
            If c.Value <> "" Then
        
                Select Case c.Name
                
                    Case TextBox1
                        ' copy to sheet
                    Case TextBox2
                        ' copy to sheet
                    Case TextBox3
                        ' copy to sheet
                    Case TextBox4
                        ' copy to sheet
                
                End Select
            End If
        End If
    Next c
End Sub

 

[leopardhawk]

Okay, I just got home and saw all these responses...Thanks so much folks, this certainly gives me enough info now to try and make it work for me.

To answer Trebor76, if the textbox is blank and the destination cell already has data in it, it wipes it clean, transfers the blank and I don't want that to happen...

I will be back to y'all after I get a chance to try some ideas here.

 

[leopardhawk]

@Yongle I tried your suggestion and I'm probably screwing the whole thing up by trying to use it in my VBA.

@GWteB Same. I don't understand VBA well enough to get things working without a bit more to go on. Consequently, here is my existing code which does work, it will transfer whatever is in the textboxes, including blanks.

Private Sub OKCommandButton_Click()

Sheets(6).Activate

    Range("D36").Value = EstCPPTextBox1.Value
    Range("D37").Value = EstOASTextBox1.Value
    Range("I36").Value = EstCPPTextBox2.Value
    Range("I37").Value = EstOASTextBox2.Value
    
    Range("D47").Value = ActCPPTextBox1.Value
    Range("D49").Value = ActOASTextBox1.Value
    Range("I47").Value = ActCPPTextBox2.Value
    Range("I49").Value = ActOASTextBox2.Value
    
    Range("D52").Value = NetTextBox1.Value
    Range("I52").Value = NetTextBox2.Value
    
Sheets(7).Activate

    Range("F11").Value = RRSPTextBox1.Value
    Range("L11").Value = RRSPTextBox2.Value
    
End Sub

 

[GWteB]

Private Sub OKCommandButton_Click()

    With Sheets(6)
    
        If Len(EstCPPTextBox1.Value) > 0 Then .Range("D36").Value = EstCPPTextBox1.Value
        If Len(EstOASTextBox1.Value) > 0 Then .Range("D37").Value = EstOASTextBox1.Value
        If Len(EstCPPTextBox2.Value) > 0 Then .Range("I36").Value = EstCPPTextBox2.Value
        If Len(EstOASTextBox2.Value) > 0 Then .Range("I37").Value = EstOASTextBox2.Value
        
        If Len(ActCPPTextBox1.Value) > 0 Then .Range("D47").Value = ActCPPTextBox1.Value
        If Len(ActOASTextBox1.Value) > 0 Then .Range("D49").Value = ActOASTextBox1.Value
        If Len(ActCPPTextBox2.Value) > 0 Then .Range("I47").Value = ActCPPTextBox2.Value
        If Len(ActOASTextBox2.Value) > 0 Then .Range("I49").Value = ActOASTextBox2.Value
        
        If Len(NetTextBox1.Value) > 0 Then .Range("D52").Value = NetTextBox1.Value
        If Len(NetTextBox2.Value) > 0 Then .Range("I52").Value = NetTextBox2.Value
    End With
        
    With Sheets(7)
        If Len(RRSPTextBox1.Value) > 0 Then .Range("F11").Value = RRSPTextBox1.Value
        If Len(RRSPTextBox2.Value) > 0 Then .Range("L11").Value = RRSPTextBox2.Value
    End With

End Sub

 

[Trebor76]

Solution already posted by GWteB