Overwriting fields

James Shaw

New Member
Joined
Feb 12, 2019
Messages
2
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?
 

Excel Facts

Whats the difference between CONCAT and CONCATENATE?
The newer CONCAT function can reference a range of cells. =CONCATENATE(A1,A2,A3,A4,A5) becomes =CONCAT(A1:A5)
try something like this
- amend sheet name and source of value to match your requirements

Code:
    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
 
Last edited:
Upvote 0

Forum statistics

Threads
1,213,495
Messages
6,113,992
Members
448,538
Latest member
alex78

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
Back
Top