Overwrite Existing Record with User form in VBA

  • Hello,


    I have a user form that allows for data entry, each time searching and adding the row of data on the next empty row, which works fine. The VBA also searches two ranges ( in 2 defined columns) for me.cboPart.value and me.combobox1.value and returns an "Entry already Exists - Use update function" if it finds that the entry parameters already exist. What I'm trying to do is have the existing record cells all updated (ie. Overwritten) if the above conditions are met using an "update entry" function. Below is the "enter new entry" code, followed by the "Update" code. I am by no means an expert VBA coder, and the below is an amalgamation of pieces i've found online that do the trick. I'm just really struggling with the "update" code. If any help coould be provided, I'd be eternally grateful. If anymore info is needed, id be happy to supply.


    [VBA]Private Sub cmdAdd_Click()
    Dim lRow As Long
    Dim lPart As Long
    Dim ws As Worksheet
    Set ws = Worksheets("PartsData")
    'revised code to avoid problems with
    'Excel lists and tables in newer versions
    'find first empty row in database
    ''lRow = ws.Cells(Rows.Count, 1) _
    '' .End(xlUp).Offset(1, 0).Row
    lRow = ws.Cells.Find(What:="*", SearchOrder:=xlRows, _
    SearchDirection:=xlPrevious, LookIn:=xlValues).Row + 1


    lPart = Me.cboPart.ListIndex


    If WorksheetFunction.CountIf(ws.Range("D2", ws.Cells(lRow, 1)), Me.cboPart.Value) > 0 And WorksheetFunction.CountIf(ws.Range("J2", ws.Cells(lRow, 1)), Me.ComboBox1.Value) Then
    MsgBox "Entry Exists for " & Me.cboPart.Value & " on " & Me.ComboBox1.Value & " - Use Update Function", vbCritical
    Exit Sub
    End If


    'check for a Sub component
    If Trim(Me.cboPart.Value) = "" Then
    Me.cboPart.SetFocus
    MsgBox "Please select a sub component"
    Exit Sub
    End If


    'check for a Wind Turbine number
    If Trim(Me.ComboBox1.Value) = "" Then
    Me.ComboBox1.SetFocus
    MsgBox "Please enter a Wind Turbine"
    Exit Sub
    End If



    'copy the data to the database
    With ws
    .Cells(lRow, 4).Value = Me.cboPart.Value
    .Cells(lRow, 5).Value = Me.cboType.Value
    .Cells(lRow, 6).Value = Me.cboPart.List(lPart, 1)
    .Cells(lRow, 7).Value = Me.cboLocation.Value
    .Cells(lRow, 8).Value = Me.txtDate.Value
    .Cells(lRow, 9).Value = Me.txtQty.Value
    .Cells(lRow, 10).Value = Me.ComboBox1.Value
    .Cells(lRow, 11).Value = Me.ComboBox2.Value
    .Cells(lRow, 2).Value = Application.UserName
    With .Cells(lRow, 3)
    .Value = Now
    .NumberFormat = "mm/dd/yyyy hh:mm:ss"
    End With


    End With


    'clear the data
    'ClearParts
    Me.cboType.Value = ""
    Me.cboPart.Value = ""
    Me.cboPart.RowSource = ""
    Me.ComboBox1.Value = ""
    Me.ComboBox2.Value = ""




    Me.cboLocation.Value = ""
    Me.txtDate.Value = Format(Date, "Medium Date")
    Me.txtQty.Value = 1
    Me.cboType.SetFocus
    Me.ComboBox2.SetFocus


    End Sub[/VBA]



    [VBA]Private Sub cmdClose_Click()
    Dim iRow As Long
    Dim lPart As Long
    Dim ws As Worksheet
    Set ws = Worksheets("PartsData")
    'revised code to avoid problems with
    'Excel lists and tables in newer versions
    'find first empty row in database
    ''lRow = ws.Cells(Rows.Count, 1) _
    '' .End(xlUp).Offset(1, 0).Row
    iRow = ws.Cells.Find(What:="me.cboPart.value", SearchOrder:=xlRows, _
    SearchDirection:=xlPrevious, LookIn:=xlValues).Row


    lPart = Me.cboPart.ListIndex



    'check for a Sub component
    If Trim(Me.cboPart.Value) = "" Then
    Me.cboPart.SetFocus
    MsgBox "Please select a sub component"
    Exit Sub
    End If


    'check for a Wind Turbine number
    If Trim(Me.ComboBox1.Value) = "" Then
    Me.ComboBox1.SetFocus
    MsgBox "Please enter a Wind Turbine"
    Exit Sub
    End If



    'copy the data to the database
    With ws
    .Cells(iRow, 4).Value = Me.cboPart.Value
    .Cells(iRow, 5).Value = Me.cboType.Value
    .Cells(iRow, 6).Value = Me.cboPart.List(lPart, 1)
    .Cells(iRow, 7).Value = Me.cboLocation.Value
    .Cells(iRow, 8).Value = Me.txtDate.Value
    .Cells(iRow, 9).Value = Me.txtQty.Value
    .Cells(iRow, 10).Value = Me.ComboBox1.Value
    .Cells(iRow, 11).Value = Me.ComboBox2.Value
    .Cells(iRow, 2).Value = Application.UserName
    With .Cells(iRow, 3)
    .Value = Now
    .NumberFormat = "mm/dd/yyyy hh:mm:ss"
    End With


    End With[/VBA]


    Thanks

Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!