Updating recalled data to a userform to its current cell rather than next free cell

[nigelreilly]

Good Morning,

I am currently tryin gto create a Sales Pipleine userform.

I have created it to save the data (40 data fields) entered into the form to an excel sheet named "raw data".

I have added a search form also which will find a previously entered form and recall the data to the userform.

When amending a previous record which is recalled into the userform, I would like to save over the data which goes into the excel sheet rather than create a new record.

At the moment to save the data onto the spreadsheet I use this button and code:

Private Sub cmdOK_Click()
    Dim sData As String
   Dim lRowNum As Long

ActiveWorkbook.Sheets("Raw Data").Activate
Range("A1").Select
 
Do
If IsEmpty(ActiveCell) = False Then
    ActiveCell.Offset(1, 0).Select
End If
Loop Until IsEmpty(ActiveCell) = True
 
ActiveCell.Offset = Scheme.Value
ActiveCell.Offset(0, 1) = Scheme.Value
ActiveCell.Offset(0, 2) = ListBox5.Value
ActiveCell.Offset(0, 3) = Field.Value
ActiveCell.Offset(0, 4) = District.Value
ActiveCell.Offset(0, 5) = Jobtitle.Value
ActiveCell.Offset(0, 6) = Contact.Value
ActiveCell.Offset(0, 7) = Address1.Value
ActiveCell.Offset(0, 8) = Address2.Value
ActiveCell.Offset(0, 9) = Address3.Value
ActiveCell.Offset(0, 10) = Postcode.Value
ActiveCell.Offset(0, 11) = Email.Value
ActiveCell.Offset(0, 12) = Directline.Value
     
ActiveCell.Offset(0, 13) = Mobilenumber.Value
ActiveCell.Offset(0, 14) = ListBox2.Value
     
ActiveCell.Offset(0, 15) = Potentialfirstorder.Value
ActiveCell.Offset(0, 16) = Annualorderforcasr.Value
ActiveCell.Offset(0, 17) = Notes.Value
ActiveCell.Offset(0, 18) = ListBox1.Value
ActiveCell.Offset(0, 19) = Originnotes.Value
ActiveCell.Offset(0, 20) = ListBox3.Value
ActiveCell.Offset(0, 21) = Qualifyreasons.Value
ActiveCell.Offset(0, 22) = meetingdate1.Value
ActiveCell.Offset(0, 23) = meetingagenda1.Value
ActiveCell.Offset(0, 24) = meetingdate2.Value
ActiveCell.Offset(0, 25) = meetingagenda2.Value
ActiveCell.Offset(0, 26) = meetingdate3.Value
ActiveCell.Offset(0, 27) = meetingagenda3.Value
ActiveCell.Offset(0, 28) = ListBox4.Value
ActiveCell.Offset(0, 29) = Contractreasons.Value
ActiveCell.Offset(0, 30) = Firstorder.Value
ActiveCell.Offset(0, 31) = Firstorder£.Value
ActiveCell.Offset(0, 32) = Dateoforder.Value
ActiveCell.Offset(0, 33) = Ordernumber.Value
ActiveCell.Offset(0, 34) = ListBox6.Value
ActiveCell.Offset(0, 35) = Contractlength.Value
ActiveCell.Offset(0, 36) = Criteria.Value
ActiveCell.Offset(0, 37) = Reporting.Value
ActiveCell.Offset(0, 38) = Reportingdates.Value
ActiveCell.Offset(0, 39) = Accountcoordinator.Value
ActiveCell.Offset(0, 40) = Contractnotes.Value
        
MsgBox "Do not forget - please delete the previous row of data if you are updating a prospect." _
& vbCrLf & "Thank You"
 
End Sub

Could somebody point me in the right direction of making either this button also update if it is an existing code, or, use a new code as an "edit / Update" button which will save over the previous record. This would be CommandButton9.

Many thanks in advance.
And please accept my apologies for if the code is not in its correct standard for this forum.

Nigel

 

[dmt32]

So you are calling this procedure:

Call Pipeline.retrieveRec(recNo)

yet your procedure is called:

Sub retrieveRec(recNo)

I am probably missing something here but where does the "Pipeline" bit come from?

Dave

 

[nigelreilly]

Pipeline " is the name of the userform in which the data is recalled into.


So I can recall the data once, and edit it. save it over the current row (from your code). But once this row of data has been edited, it cannot be recalled.

Nige