edit data in userform and update spreadsheet

**nunans** · 03-07-2012, 05:35 PM

Good morning everyone,
I am having a problem getting my code to work. I am trying to find a record in a userform, edit it and then save it back to the spreadsheet. I have successfully used a combobox to locate the individuals name and show their details, and a hidden textbox to store the ID number. The code that I have sourced and customised works for changes in the first field only. Any changes I make to any other fields does not show on the spreadsheet. Any comments on the code would be appreciated.

Private Sub cmdUpdate_Click()
Dim Rw As Range
For Each Rw In Range(cboName.RowSource)
        If cboName.Column(1) = txtID.Text Then
       
            cboName.Column(2) = cboContactType.Value
            cboName.Column(3) = cboSalutation.Value
            cboName.Column(4) = txtFirstname.Value
            cboName.Column(5) = txtSurname.Value
            cboName.Column(6) = txtJobTitle.Value
            cboName.Column(7) = txtCompany.Value
            cboName.Column(8) = txtStreetAddress.Value
            cboName.Column(9) = txtSuburb.Value
            cboName.Column(10) = cboState.Value
            cboName.Column(11) = txtPostcode.Value
            cboName.Column(12) = txtWorkPhone.Value
            cboName.Column(13) = txtMobile.Value
            cboName.Column(14) = txtEmail.Value
            cboName.Column(15) = txtStartDate.Value
            cboName.Column(16) = txtHourlyRate.Value
            cboName.Column(17) = txtComments.Value
                        
         End If
 Next Rw


MsgBox ("Details updated")
ActiveWorkbook.Save


End Sub

Cheers,
Nunans

**reddy14** · 03-07-2012, 06:03 PM

Did you check how many times the for loop is executed?

I can debug if you can upload the xls that you are working on.

**nunans** · 03-08-2012, 04:44 AM

I actually ended up going with another piece of code.

Private Sub cmdUpdate_Click()

Dim CurrentRow As Integer
Dim vFIND As Range

On Error Resume Next
With ActiveWorkbook.Sheets("EmployeeInfo")
Set vFIND = .Range("B:B").Find(txtID.Value, LookIn:=xlValues, LookAt:=xlWhole)
vFIND.Select

CurrentRow = ActiveCell.Row
Me.cboName.Value = ""


Range("c" & CurrentRow) = Me.cboContactType.Text
Range("d" & CurrentRow) = Me.cboSalutation.Text
Range("e" & CurrentRow) = Me.txtFirstname.Text
Range("f" & CurrentRow) = Me.txtSurname.Text
Range("g" & CurrentRow) = Me.txtJobTitle.Text
Range("h" & CurrentRow) = Me.txtCompany.Text
Range("i" & CurrentRow) = Me.txtStreetAddress.Text
Range("j" & CurrentRow) = Me.txtSuburb.Text
Range("k" & CurrentRow) = Me.cboState.Value
Range("l" & CurrentRow) = Me.txtPostcode.Text
Range("m" & CurrentRow) = Me.txtWorkPhone.Text
Range("n" & CurrentRow) = Me.txtMobile.Text
Range("o" & CurrentRow) = Me.txtEmail.Text
Range("p" & CurrentRow) = Me.txtStartDate.Text
Range("q" & CurrentRow) = Me.txtHourlyRate.Text
Range("r" & CurrentRow) = Me.txtComments.Text


MsgBox ("Details updated")
ActiveWorkbook.Save



End With

End Sub

Cheers,
Nunans