Overwriting fields

[James Shaw]

I have an input menu that allows users to input the serial number of a product and then check off what listed parts are either missing or pulled from said product. However, I want to be able to update certain serial number's fields without creating a whole new row for the same serial number.

The serial numbers are stored in column A and its parts are listed in the row next to them.

Is there a way I can have a message box pop up informing the user that the serial number is already in the sheet and query if the user wants its old data overwritten?

 

[Yongle]

try something like this
- amend sheet name and source of value to match your requirements

    Dim aValue As String, inRange As Range, r As Long, msg As String
    
    aValue = [COLOR=#ff0000]TextBox1.Value[/COLOR]
    Set inRange = Sheets("[COLOR=#ff0000]ProductList[/COLOR]").Columns(1)
    msg = "Serial number already exists" & vbCr & "Click YES to overwrite related data"

    On Error Resume Next
        r = inRange.Find(aValue, lookat:=xlWhole).Row
    On Error GoTo 0
    
    If r > 0 Then
        msg = aValue & vbCr & vbCr & msg
        Select Case MsgBox(msg, vbYesNo, "User confirmation")
            Case vbYes
                [COLOR=#006400][I]'overwrite allowed code here[/I][/COLOR]
            Case Else
                [COLOR=#006400][I]'overwrite prevented code here[/I][/COLOR]
        End Select
    Else
       [COLOR=#006400][I] 'serial number not in database[/I][/COLOR]
    End If

 

[James Shaw]

This is perfect, thanks a bunch!
Appreciate you taking the time. ?

 

[Yongle]

thanks for your fedback