Textbox value updates sheet without changing the other cells

[Jayliam]

Need some guidance. I am using a textbox to update adjacent cells based on the value in column A. Everything works except if there is data in the other cells it deletes it. How can I update specific textboxes without deleting the other data? Below is the code I have so far.

Option Explicit

Private Sub UPDATE_NCP_Click()
Dim m As Variant
Dim iRow As Long
Dim ws As Worksheet
Dim FindString As String
Dim Rng As Range
Set ws = Worksheets("DATALOG")

iRow = ws.Cells.Find(What:="*", SearchOrder:=xlRows, _
SearchDirection:=xlPrevious, LookIn:=xlValues).Row

If Len((Me.TextBox1.Value)) > 0 Then

m = Application.Match(Val(Me.TextBox1.Value), ws.Range("A:A"), False)
If Not IsError(m) Then
m = CLng(m)

With ws

.Cells(iRow, 17).Value = Me.TextBox2.Value
.Cells(iRow, 19).Value = Me.TextBox3.Value
.Cells(iRow, 20).Value = Me.TextBox4.Value
.Cells(iRow, 24).Value = Me.TextBox5.Value
.Cells(iRow, 25).Value = Me.TextBox6.Value
.Cells(iRow, 26).Value = Me.TextBox7.Value
.Cells(iRow, 27).Value = Me.TextBox8.Value
.Cells(iRow, 30).Value = Me.TextBox9.Value
.Cells(iRow, 23).Value = Me.TextBox10.Value
.Cells(iRow, 29).Value = Me.TextBox11.Value
.Cells(iRow, 32).Value = Me.TextBox12.Value
.Cells(iRow, 18).Value = Me.ComboBox1.Value

End With

Me.TextBox2.Value = ""
Me.TextBox3.Value = ""
Me.TextBox4.Value = ""
Me.TextBox5.Value = ""
Me.TextBox6.Value = ""
Me.TextBox7.Value = ""
Me.TextBox8.Value = ""
Me.TextBox9.Value = ""
Me.TextBox10.Value = ""
Me.TextBox11.Value = ""
Me.TextBox12.Value = ""
Me.ComboBox1.Value = ""

Else
MsgBox "NCP NUMBER: " & Me.TextBox1 & Chr(10) & "Record Not Found", 48, "Not Found"
Me.TextBox1.SetFocus
End If

End If

End Sub

Private Sub ComboBox1_DropButtonClick()
With Me.ComboBox1
.List = Worksheets("Failure_Modes").Range("A2", Worksheets("Failure_Modes").Cells(Rows.Count, "A").End(xlUp)).Value
.ListRows = Application.WorksheetFunction.Min(6, .ListCount)
.DropDown
End With
End Sub

Private Sub CommandButton2_Click()
Unload Me
End Sub

 

[dmt32]

Hi,
try this update to the code & see if does what you want

Private Sub UPDATE_NCP_Click()
    Dim m           As Variant, Search As Variant
    Dim i           As Long, c As Long
    Dim ws          As Worksheet
   
    Search = Me.TextBox1.Value
    If Len(Search) = 0 Then Exit Sub
    If IsNumeric(Search) Then Search = Val(Search)
   
    Set ws = ThisWorkbook.Worksheets("DATALOG")
   
    m = Application.Match(Search, ws.Range("A:A"), False)
    If Not IsError(m) Then
        m = CLng(m)
       
        For i = 1 To 12
            c = Choose(i, 17, 19, 20, 24, 25, 26, 27, 30, 23, 29, 32, 18)
            With Me.Controls(IIf(i = 12, "ComboBox1", "TextBox" & i + 1))
              If Len(.Value) > 0 Then ws.Cells(CLng(m), c).Value = .Value
                .Value = ""
            End With
        Next i
       
        MsgBox "NCP NUMBER: " & Search & Chr(10) & "Record Updated", 64, "Updated"
       
    Else

        MsgBox "NCP NUMBER: " & Search & Chr(10) & "Record Not Found", 48, "Not Found"

    End If
     Me.TextBox1.SetFocus
End Sub

Dave

 

[Jayliam]

Hi,
try this update to the code & see if does what you want

Private Sub UPDATE_NCP_Click()
    Dim m           As Variant, Search As Variant
    Dim i           As Long, c As Long
    Dim ws          As Worksheet
  
    Search = Me.TextBox1.Value
    If Len(Search) = 0 Then Exit Sub
    If IsNumeric(Search) Then Search = Val(Search)
  
    Set ws = ThisWorkbook.Worksheets("DATALOG")
  
    m = Application.Match(Search, ws.Range("A:A"), False)
    If Not IsError(m) Then
        m = CLng(m)
      
        For i = 1 To 12
            c = Choose(i, 17, 19, 20, 24, 25, 26, 27, 30, 23, 29, 32, 18)
            With Me.Controls(IIf(i = 12, "ComboBox1", "TextBox" & i + 1))
              If Len(.Value) > 0 Then ws.Cells(CLng(m), c).Value = .Value
                .Value = ""
            End With
        Next i
      
        MsgBox "NCP NUMBER: " & Search & Chr(10) & "Record Updated", 64, "Updated"
      
    Else

        MsgBox "NCP NUMBER: " & Search & Chr(10) & "Record Not Found", 48, "Not Found"

    End If
     Me.TextBox1.SetFocus
End Sub

Dave

Dave,

It worked perfectly. Thank you for your assistance.

 

[dmt32]

Dave,

It worked perfectly. Thank you for your assistance.

Most welcome & appreciate your feedback

Dave