CODEWITHSUNDEEP
excelvbauserform
D Brown
D Brown

Reputation: 33

Update existing row data using a userform

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

Upvotes: 0

Views: 1652

Answers (1)

Siddharth Rout
Siddharth Rout

Reputation: 149287

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.

Upvotes: 1

Related Questions