Empty textbox - no action.

[ShirleyWyl]

Hi Brains, I am a noob with userforms and vba. I have 4 textboxes in 1 userform for entering corrected data into each different cell.
txtDiet goes to cell A2, txtLocation goes to cell A4, txtIssue goes to cell A6, etc. This single userform is for the user to key-in the correct information. If the cell information is correct, no userform entry is required. That means I can skip the textbox and go to the next textbox within the same userform. But the blank textbox seems to delete my all my correct cell information and resulted in a blank state. I have copied a code as below but its not working: -

Private Sub cmdADD_Click()
'Copy input values to sheet.
Dim ws As Worksheet
Set ws = Worksheets("Data Entry")
'lRow = ws.Cells(Rows.Count, 1).End(xlUp).Offset(1, 0).Row
With ws
.Cells(4, 3).Value = Me.txtDiet.Value (If the original value of cell A4 is correct, I leave this textbox blank, but this action seems to delete the info and created a blank cell)
.Cells(4, 8).Value = Me.txtLocation.Value (blank textbox = erase of cell data.)
.Cells(5, 8).Value = Me.txtIssue.Value (blank textbox = erase of cell data.)
.Cells(16, 2).Value = Me.txtID.Value (blank textbox = erase of cell data.)
End With
'Clear input controls.
Me.txtDiet.Value = ""
Me.txtLocation.Value = ""
Me.txtIssue.Value = ""
Me.txtNurseID.Value = ""
Call PrivatePt2
Me.Hide
End Sub

Please help me to stop blank textbox from erasing data with blank cells.

 

[dmt32]

Hi,
welcome to forum

Try this update to your code & see if does what you want

Private Sub cmdADD_Click()
    'Copy input values to sheet.
    Dim ws          As Worksheet
    Set ws = Worksheets("Data Entry")
    'lRow = ws.Cells(Rows.Count, 1).End(xlUp).Offset(1, 0).Row
    With ws
        '(If the original value of cell A4 is correct, I leave this textbox blank)
        With .Cells(4, 3)
            .Value = IIf(Len(Me.txtDiet.Value) > 0, Me.txtDiet.Value, .Value)
        End With
        .Cells(4, 8).Value = Me.txtLocation.Value        '(blank textbox = erase of cell data.)
        .Cells(5, 8).Value = Me.txtIssue.Value        '(blank textbox = erase of cell data.)
        .Cells(16, 2).Value = Me.txtNurseID.Value        '(blank textbox = erase of cell data.)
    End With
    'Clear input controls.
    Me.txtDiet.Value = ""
    Me.txtLocation.Value = ""
    Me.txtIssue.Value = ""
    Me.txtNurseID.Value = ""
    Call PrivatePt2
    Me.Hide
End Sub

Updated code should allow you to leave control txtDiet blank and not delete existing data in the range.
Note: I amended the control name shown in BOLD from txtID to txtNurseID which I am guessing was a typo?

Dave

 

[ShirleyWyl]

Hi dmt32, thank you so much for your help. The code worked like a charm!! Thanks alot.​

Shirley

 

[dmt32]

You are welcome glad suggestion helps you
Appreciate the feedback

Dave