Edit Userform

[Noz2k]

At the moment I have a userform which when submitted posts the data to the next empty row in excel. However I have made it so that the user can view other records on the userform. However I don't know how to enable the user to then amend that record. At the moment it just posts all the information into the next cell.

The code to view an existing record is

Private Sub UserForm_Initialize()
Dim lngLastRow As Long
lngLastRow = Cells(Rows.Count, "A").End(xlUp).Row
 
 
cboUniqueID.List = Worksheets("Sheet1").Range("A3:A" & lngLastRow).Value
End Sub
 
Private Sub cboAccidentID_Change()
 
If cboUniqueID.ListIndex <> -1 Then
txtFirstName.Value = Range("A" & cboUniqueID.ListIndex + 3).Offset(, 1) 'etc
End Sub
 
ActiveWorkbook.Sheets("Sheet1").Activate
 
Range("A3").Select
 
Do
If IsEmpty(ActiveCell) = False Then
ActiveCell.Offset(1, 0).Select
 
End If
 
Loop Until IsEmpty(ActiveCell) = True
 
ActiveCell.Value = cboUniqueID.Value 'etc
End Sub

So, I guess I need to put a clause in which specifies that if the uniqueID value exists in Range("A3:A" & lngLastRow) Then Activecell = where this is true.

but I can't figure out how to say this. Hope that makes sense to somebody. Thanks in advance.

 

[Andrew Poulsom]

You know where the record came from because you have:

txtFirstName.Value = Range("A" & cboUniqueID.ListIndex + 3).Offset(, 1)

Therefore you know where to put any amendments.

 

[Noz2k]

I know, but as I already have a lot of code specifying where to post new information

ActiveCell.Offset(, 1) = txtFirstName.Value etc (for around 50 controls), I was hoping for a way to just change the value for the ActiveCell, rather than pasting in all of the listindex + 3 type data, as I didn't want to make the code so long, and couldn't figure out how to do this.

Have got it going now though, I just had my End If in the wrong place

If cboUniqueID.ListIndex <> -1 Then
Range("A" & cboUniqueID.ListIndex + 3).Select
 
Else
    Range("A3").Select
 
Do
    If IsEmpty(ActiveCell) = False Then
        ActiveCell.Offset(1, 0).Select
    End If
 
 
Loop Until IsEmpty(ActiveCell) = True
End If