Empty textbox - no action.

ShirleyWyl

New Member
Joined
Mar 10, 2021
Messages
2
Office Version
  1. 365
  2. 2019
Platform
  1. Windows
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.
 

Attachments

  • UserForm.JPG
    UserForm.JPG
    19 KB · Views: 1

Excel Facts

Copy formula down without changing references
If you have =SUM(F2:F49) in F50; type Alt+' in F51 to copy =SUM(F2:F49) to F51, leaving the formula in edit mode. Change SUM to COUNT.

dmt32

Well-known Member
Joined
Jul 3, 2012
Messages
6,523
Office Version
  1. 2019
Platform
  1. Windows
Hi,
welcome to forum

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


Rich (BB code):
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
 
Solution

ShirleyWyl

New Member
Joined
Mar 10, 2021
Messages
2
Office Version
  1. 365
  2. 2019
Platform
  1. Windows

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

Shirley
 

dmt32

Well-known Member
Joined
Jul 3, 2012
Messages
6,523
Office Version
  1. 2019
Platform
  1. Windows
You are welcome glad suggestion helps you
Appreciate the feedback

Dave
 

Watch MrExcel Video

Forum statistics

Threads
1,130,220
Messages
5,640,962
Members
417,183
Latest member
CuteLeo

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top