Henrybukowski
New Member
- Joined
- Apr 16, 2013
- Messages
- 29
I have a user form transferring data to a new line in a spreadsheet. It's something like as follows:
I have added a function to update existing data entries. when a user clicks on the active line, the data from the active line is put back into the user form, so that the user can change and update the data:
However, at the moment instead of updating the same record, my code is adding a whole new entry.
could any suggest a modification which makes it so that this function replaces the existing entry?
Code:
Private Sub CB1_Click()Dim iRow As Long
Dim ws As Worksheet
Set ws = Worksheets("DataDump")
Dim historyWks As Worksheet
Dim inputWks As Worksheet
Dim nextRow As Long
Dim oCol As Long
Dim myrng As Range
Dim myCell As Range
'''find first empty row in database
''iRow = ws.Cells(Rows.Count, 1) _
'' .End(xlUp).Offset(1, 0).Row
'revised code to avoid problems with Excel tables in newer versions
iRow = ws.Cells.Find(What:="*", SearchOrder:=xlRows, _
SearchDirection:=xlPrevious, LookIn:=xlValues).Row + 1
'copy the data to the database
ws.Cells(iRow, 1).Value = Me.cboDATE.Value
ws.Cells(iRow, 2).Value = Me.cboREF.Value
ws.Cells(iRow, 3).Value = Me.cboCI.Value
' Repeat for every text box which needs adding
I have added a function to update existing data entries. when a user clicks on the active line, the data from the active line is put back into the user form, so that the user can change and update the data:
Code:
Private Sub UserForm_Initialize()'read the data from active row
With Me
.fd1.Value = Cells(ActiveCell.Row, "X").Value
.fd2.Value = Cells(ActiveCell.Row, "Y").Value
.fd3.Value = Cells(ActiveCell.Row, "Z").Value
However, at the moment instead of updating the same record, my code is adding a whole new entry.
could any suggest a modification which makes it so that this function replaces the existing entry?