Update existing row data using a userform

Question

I am using a userform to update existing data on a worksheet. I can create new records just fine. I created an update userform with a combobox to search for the names. It pulls the persons data just fine and I am able to change the information. But when I go to click the update button, an error occurs. Before it was adding a totally new line which I did not want to happen so I adjusted my code. I just want to update an existing line of data with the edited information.

I have tried to use the MATCH function in VBA after it was replicating records.

Private Sub Update_record_Click()
Dim sh As Worksheet
Set sh = ThisWorkbook.Sheets("Master")
Dim n As Long
Dim empname As String

empname = Application.Match(VBA.CStr(Me.Update_record.Value), 
sh.Range("C:C"), 0)

sh.Range("A" & empname).Value = Me.First_Name.Value
sh.Range("B" & empname).Value = Me.Last_Name.Value
sh.Range("D" & empname).Value = Me.MainPX.Value
sh.Range("E" & empname).Value = Me.AltPX.Value
sh.Range("F" & empname).Value = Me.Job_Role.Value
sh.Range("G" & empname).Value = Me.WristBand.Value
sh.Range("H" & empname).Value = Me.Team.Value
sh.Range("I" & empname).Value = Me.Unit.Value


Range("A2:J" & n).Sort key1:=Range("A2:A" & n), order1:=xlAscending, 
Header:=xlNo

 Me.First_Name.Value = ""
 Me.Last_Name.Value = ""
 Me.MainPX.Value = ""
 Me.AltPX.Value = ""
 Me.Job_Role.Value = ""
 Me.WristBand.Value = ""
 Me.Team.Value = ""
 Me.Unit.Value = ""


 MsgBox "Record has been updated", vbInformation
 End Sub

This is where the application is erroring out...It stops here....on this line

empname = Application.Match(VBA.CStr(Me.Update_record.Value), 
sh.Range("C:C"), 0)

So the data never gets updated to the row. Below are two screen shots... one of the worksheet and one of the userform.

Worksheet Screenshot

Userform Screenshot

What's the error? Also, you start off with sh.Range qualified Range calls, and then sort with unqualified Range("A2:J" & n).Sort Key1:=Range(...) ...they should be qualified as well. But Match will return a Variant/Error if it doesn't find a match, and you're coercing that return value into a String -- I'm betting the error you're getting is a type mismatch. — Mathieu Guindon
– Mathieu Guindon, Commented Sep 12, 2019 at 20:16
Always capture the result of Application.AnyLateBoundWorksheetFunction into a Variant, then only proceed to treat that value as something usable when Not IfError(thatVariantResult). — Mathieu Guindon
– Mathieu Guindon, Commented Sep 12, 2019 at 20:17
What does Me.Update_Record.Value look like? Is it more like 54204 or more like CA0041? If the former (i.e. a numeric ID), then CStr could be responsible for Match failing. — Mathieu Guindon
– Mathieu Guindon, Commented Sep 12, 2019 at 20:21
@MathieuGuindon... the error is a "Runtime Error 13" Type Mismatch...and it highlights that line when I ask it to debug....I'm lost as to why. — D Brown
– D Brown, Commented Sep 12, 2019 at 20:37
@MathieuGuindon I even changed empname to Long instead of string and got the same error. — D Brown
– D Brown, Commented Sep 12, 2019 at 20:46

Siddharth Rout · Accepted Answer · 2019-09-13 03:19:08Z

There are three ways that I can immediatley think to go about it. I haven't tested the first two so let me know if you face any problem.

WAY ONE

Dim fName As String
Dim lName As String
Dim NameToSearch As String
Dim RecRow As Long
Dim sh As Worksheet

Set sh = ThisWorkbook.Sheets("Master")

fName = FirstNameTextbox.Value '<~~ First Name textBox
lName = LastNameTextbox.Value  '<~~ Last Name textBox

NameToSearch = fName & ", " & lName

empname = Application.WorksheetFunction.Match(NameToSearch, sh.Range("C:C"), 0)

WAY TWO

This method uses .Find.

Dim fName As String
Dim lName As String
Dim NameToSearch As String
Dim aCell As Range
Dim ws As Worksheet
Dim RecRow As Long

fName = FirstNameTextbox.Value
lName = LastNameTextbox.Value

NameToSearch = fName & ", " & lName

Set ws = ThisWorkbook.Sheets("Master")

With ws
    Set aCell = .Columns(3).Find(What:=NameToSearch, LookIn:=xlValues, _
        LookAt:=xlWhole, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
        MatchCase:=False, SearchFormat:=False)

    If Not aCell Is Nothing Then
        RecRow = aCell.Row '<~~ This is the row where the data is
    Else
        MsgBox SearchString & " not Found"
    End If
End With

WAY THREE (I prefer This)

Insert a column in the worksheet in Col A and call it ID. This will have unique serial numbers(row numbers?). When reading the item, read that as well and when writing it back to the cells, use that ID to write back. No need to search for the record using Match or .Find

In this case you will always update the row (ID + 1) assuming, the serial number starts at 1 from row 2.

WAY THREE. These type out of box performance boosters make working in Excel VBA enjoyable. Always looking for such ideas.

Collectives™ on Stack Overflow

Update existing row data using a userform

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related