PDA

View Full Version : VBA UserForm: Updating Existing Data



redstarx
04-07-2015, 11:01 AM
Hello Everyone,

I created a userform that allows me to input, save, load and edit data but I'm having an issue when it comes to saving back the changes I make to the data after I edit an existing record. Hopefully someone can help me out with this.

This is the part of my code that allows me to search (pick from a combobox) an ID and it loads onto the form all of the information I want to edit.



Private Sub IDSearch_Change()
Dim ws As Worksheet
Dim LastRow As Long
Dim C As Range


Set ws = Worksheets("Employees")


With ws
LastRow = .Cells(.Rows.Count, "A").End(xlUp).Row

With .Range("A2:A" & LastRow)
Set C = .Find(what:=Me.IDSearch, LookIn:=xlValues)
If Not C Is Nothing Then
Me.txtEmpID = C.Offset(0, 0)
Me.txtStatus = C.Offset(0, 1)
Me.txtFirstName = C.Offset(0, 2)
Me.txtLastName = C.Offset(0, 3)
Me.txtFullName = C.Offset(0, 4)
FullNameSearch = C.Offset(0, 4) 'I use this to populate another combobox I have for searching by name. Technically this just displays the name of the ID selected.
Me.txtInitialDate = C.Offset(0, 73)
Me.cbCity = C.Offset(0, 78)
Me.cbState = C.Offset(0, 79)
Me.txtLeaveDate = C.Offset(0, 89)
Me.cbDept = C.Offset(0, 100)
Me.cbSch = C.Offset(0, 101)

Me.IDSearch.Tag = C.Row 'This part is suppsoed to Tag the row where the data is going back to.
End If
End With
End With


And this is the code that "should" allow me to save back the changes I make to the data but it's not working, it does nothing.



Private Sub UpdateData_Click()


Dim irow As Long
Dim ws As Worksheet
Set ws = Worksheets("Employees")


irow = Me.IDSearch.Tag 'This is the tag from the previous code, I don't think I'm using it right.


With ws

.Range("A" & irow) = txtEmpID.Value
.Range("B" & irow) = txtStatus
.Range("C" & irow) = txtFirstName
.Range("D" & irow) = txtLastName
.Range("E" & irow) = txtFullName
.Range("BV" & irow) = txtInitialDate
.Range("CA" & irow) = cbCity
.Range("CB" & irow) = cbState
.Range("CL" & irow) = txtLeaveDate
.Range("CW" & irow) = cbDept
.Range("CX" & irow) = cbSch

End With

Call Clear_Form

End Sub



I've been seeking help on this subject and no luck so far. So you'll have to excuse me if you find something similar elsewhere but I'm a bit frustrated right now.

Thanks in advance.