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!

 

[leopardhawk]

Hey guys, thanks so much for the quick response and your time. Unfortunately, I copied your code into my VBA, brought up the UserForm and hit the 'OK' button with all fields blank. Every applicable cell on the two destination sheets (6 & 7) was erased...

Maybe I am doing something wrong?

 

[leopardhawk]

@GWteB I think I know why your code didn't work for me. I have some additional code in the userform so that a $ is automatically inserted into each textbox when the userform is opened. What modification do I need to make to your code to make it work?

 

[GWteB]

@GWteB I have some additional code in the userform so that a $ is automatically inserted into each textbox when the userform is opened.

The previous code checks the length of the TextBox input. A "$" (or whathever character) results in a length > 0 (ie a not empty TextBox).
The amended code below should work as intended. It takes into account that there may be a "$" in the Textbox and considers the presence of a single "$" as an empty Textbox.
Note the use of a separate function.

Private Sub OKCommandButton_Click()

    With Sheets(6)
        If Not AllmostEmpty(EstCPPTextBox1) Then .Range("D36").Value = EstCPPTextBox1.Value
        If Not AllmostEmpty(EstOASTextBox1) Then .Range("D37").Value = EstOASTextBox1.Value
        If Not AllmostEmpty(EstCPPTextBox2) Then .Range("I36").Value = EstCPPTextBox2.Value
        If Not AllmostEmpty(EstOASTextBox2) Then .Range("I37").Value = EstOASTextBox2.Value

        If Not AllmostEmpty(ActCPPTextBox1) Then .Range("D47").Value = ActCPPTextBox1.Value
        If Not AllmostEmpty(ActOASTextBox1) Then .Range("D49").Value = ActOASTextBox1.Value
        If Not AllmostEmpty(ActCPPTextBox2) Then .Range("I47").Value = ActCPPTextBox2.Value
        If Not AllmostEmpty(ActOASTextBox2) Then .Range("I49").Value = ActOASTextBox2.Value

        If Not AllmostEmpty(NetTextBox1) Then .Range("D52").Value = NetTextBox1.Value
        If Not AllmostEmpty(NetTextBox2) Then .Range("I52").Value = NetTextBox2.Value
    End With

    With Sheets(7)
        If Not AllmostEmpty(RRSPTextBox1) Then .Range("F11").Value = RRSPTextBox1.Value
        If Not AllmostEmpty(RRSPTextBox2) Then .Range("L11").Value = RRSPTextBox2.Value
    End With

End Sub

This goes in your UserForm or in a regular module.

Public Function AllmostEmpty(argTbx As Control) As Boolean
    Dim lTmp    As Long
    AllmostEmpty = False
    If Not argTbx Is Nothing Then
        If TypeName(argTbx) = "TextBox" Then
            lTmp = Len(Trim(argTbx.Value))
            If lTmp = 0 Then AllmostEmpty = True
            If lTmp = 1 And Trim(argTbx.Value) = "$" Then AllmostEmpty = True
        End If
    End If
End Function

 

[leopardhawk]

@GWteB Thank you so much! After changing the code and doing a bit of testing, this appears to be exactly what I needed. I really appreciate your time and your efforts to help me.

 

[leopardhawk]

@GWteB I think I may have spoken too soon. I also have a couple of ComboBoxes on my UserForm and using the same logic (for the textboxes) in the VBA code, the destination cells are cleared when the OK button is clicked with a blank in the comboboxes. Hoping you have a suggestion or two...

 

[GWteB]

You could apply a similar approach. I've adjusted the function. It's now usable for either Text Boxes and Combo Boxes.
This custom function returns TRUE if the value of the passed on control consists of nothing (empty string), a bunch of spaces or a single "$", whether or not preceded or followed by spaces only.

Public Function AllmostEmpty(argBox As Control) As Boolean
    Dim lTmp    As Long
    AllmostEmpty = False
    If Not argBox Is Nothing Then
        If TypeName(argBox) = "TextBox" Or TypeName(argBox) = "ComboBox" Then
            lTmp = Len(Trim(argBox.Value))
            If lTmp = 0 Then AllmostEmpty = True
            If lTmp = 1 And Trim(argBox.Value) = "$" Then AllmostEmpty = True
        End If
    End If
End Function

Usage example

    With Sheets("Destination")

        If Not AllmostEmpty(ComboBox1) Then .Range("A1").Value = ComboBox1.Value

    End With

 

[leopardhawk]

@GWteB Thank you! Works perfectly.

 

[GWteB]

You're welcome! Glad to help & thanks for letting me know.