Solved: Userform updating worksheet incorrectly

[buckchow]

The userform is set up to allow the user to enter data in multiple textboxes which is then copied to a row on the worksheet. If the user goes back and updates some of the data at a later date in just one or two textboxes on the userform I only want those cells in the worksheet to be updated and not change any of the other cells on that row. What currently is happening is that all the cells corresponding to the user form are being updated, not just the changed cells, resulting in data in the updated cells in the worksheet and blanks in rest of the cells on that row in the worksheet.

[Tommy]

Hi buckchow, :hi:

Welcome to VBAX! :thumb

Before updating the cells check the textboxes for Null


If UserForm1.TextBox1.Text <> vbNullString Then
ActiveSheet.Cells(1, 2).Value = UserForm1.TextBox1.Text
End If


HTH

[buckchow]

Thanks for the input Tommy. I already have vbNullString in my code but maybe I don't have it entered correctly. Please review by code:


Private Sub cmdAdd_Click()
Dim strRange As String

If txtBatch = vbNullString Then
MsgBox "No batch number", vbCritical
txtBatch.SetFocus
Exit Sub

End If

strRange = txtBatch.RowSource

If txtBatch.ListIndex > -1 Then

With Range(strRange).Cells(txtBatch.ListIndex + 1, 1)
.Offset(0, 5) = txtQty
.Offset(0, 6) = txtAcc
.Offset(0, 7) = txtRej



End With
Else
With Range(strRange).Cells(Range(strRange).Rows.Count + 1, 1)
.Value = txtBatch

End With

End If

txtBatch = vbNullString

'clear the data
Me.txtBatch.Value = ""
Me.txtQty.Value = ""
Me.txtAcc.Value = ""
Me.txtRej.Value = ""
Me.txtBatch.SetFocus

End Sub


Edit: Added VBA Code Tags Tommy

[Tommy]

I didn't test this, but what I did is check the textboxes for vbnullstring replace with what is there if it is vbnullstring. :)

Private Sub cmdAdd_Click()
Dim strRange As String
If txtBatch = vbNullString Then
MsgBox "No batch number", vbCritical
txtBatch.SetFocus
Exit Sub

End If
strRange = txtBatch.RowSource
If txtBatch.ListIndex > -1 Then
With Range(strRange).Cells(txtBatch.ListIndex + 1, 1)
.Offset(0, 5) = IIf(txtQty <> vbNullString, txtQty, .Offset(0, 5))
.Offset(0, 6) = IIf(txtAcc <> vbNullString, txtAcc, .Offset(0, 6))
.Offset(0, 7) = IIf(txtRej <> vbNullString, txtRej, .Offset(0, 7))
End With
Else
With Range(strRange).Cells(Range(strRange).Rows.Count + 1, 1)
.Value = txtBatch
End With
End If
txtBatch = vbNullString
'clear the data
Me.txtBatch.Value = ""
Me.txtQty.Value = ""
Me.txtAcc.Value = ""
Me.txtRej.Value = ""
Me.txtBatch.SetFocus
End Sub

HTH

[buckchow]

Thanks Tommy!!!!! I just tested it and it works perfectly.