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

 

[nigelreilly]

just a wee bump

 

[nigelreilly]

 

[dmt32]

not tested but following may do what you want.
I have assumed search field is in Col 1 (A) - you will need to adjust code as required.
Hope helps

Dave

Private Sub cmdOK_Click()
    Dim WS1 As Worksheet
    Dim Search As String
    Dim Foundcell As Range
    Dim newrecord As Boolean
    Dim msg As Integer

    Set WS1 = Worksheets("Raw Data")

    Search = Scheme.Value    '< search value in Col 1 (A)
    newrecord = False
    Set Foundcell = WS1.Columns(1).Find(Search, _
                                        LookIn:=xlValues, _
                                        LookAt:=xlWhole)

    If Foundcell Is Nothing = True Then
        Set Foundcell = WS1.Columns(1).Find(what:="", _
                                            LookIn:=xlValues, _
                                            LookAt:=xlWhole)

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

 

[nigelreilly]

Thank you DMT32.

I will have a look and play with this code and let you know how I get on.

Nigel

 

[dmt32]

Thank you DMT32.

I will have a look and play with this code and let you know how I get on.

Nigel

Thanks for feedback - should be easy to understand & able to adjust yourself as needed.

Dave

 

[nigelreilly]

Hi again,

I made a new command button which will edit the data & used your code which works fantastically!!! Thank you so much.
However, when i use my search button and fill out the form to edit it again it throws up an error

"run time error 380:
could not set the value propert. invalid property vale ."

[PHP]Private Sub cmd_Find_Click()
Dim itmX As ListItem

With Worksheets("raw data").Cells
lastCol = .Cells(1, Columns.Count).End(xlToLeft).Column

ListView1.ListItems.Clear
ListView1.View = lvwReport

ListView1.ColumnHeaders.Clear
ListView1.ColumnHeaders.Add 1, "key1", "Rec No"
For c = 1 To lastCol
ListView1.ColumnHeaders.Add c + 1, "key" & c + 1, .Cells(1, c)
Next c

Set found = .Find(Me.txt_Find.Text, LookIn:=xlValues)
If Not found Is Nothing Then
firstAddress = found.Address
Do
myRow = found.Row
Set itmX = ListView1.ListItems.Add(, , myRow - 1)
For c = 1 To lastCol
itmX.SubItems(c) = .Cells(myRow, c)
Next c
Set found = .Cells(found.Row, Columns.Count)

Set found = .FindNext(found)
Loop While Not found Is Nothing And found.Address <> firstAddress
End If
End With
Call AutoSizeColumnLv(Me.ListView1)
Call LVFullRowSelect(Me.ListView1)
End Sub
Private Sub cmd_SelRec_Click()
Dim recNo As Long
recNo = 0
On Error Resume Next
recNo = ListView1.SelectedItem
On Error GoTo 0
If recNo > 0 Then
Call Pipeline.retrieveRec(recNo)End If
Unload Me
End Sub

[/PHP][code]

The  part is where the error is. "Call Pipeline.retrieveRec(recNo)"

Thanks for your help so far. I feel like I am getting there now.

Nigel

 

[nigelreilly]

Therefore the problem is basically I cannot unload a previously edited record onto the userform to look at and perhaps re-edit.

Nigel

 

[dmt32]

The part is where the error is. "Call Pipeline.retrieveRec(recNo)"

Thanks for your help so far. I feel like I am getting there now.

Nigel

I have no way of testing your code or values you are passing in the arguments - so sorry, but you will need to work through each part & test values to ensure code is doing what you require. Just one observation, as printed the procedure has an Endif that would throw an error:

Call Pipeline.retrieveRec(recNo)End If

Move it to a new line.

Dave

 

[nigelreilly]

OK , thank you for your help. I have looked into my coding and found no real reason for the error

The Call Pipeline.retrieveRec(recNo) refers to this coding. Perhaps the porblem lays here.. although I am struggling to find it.

Sub retrieveRec(recNo)
    With Sheets("raw data")
        Scheme.Value = .Cells(recNo + 1, "b")
        ListBox5.Value = .Cells(recNo + 1, "c")
        Field.Value = .Cells(recNo + 1, "d")
        District.Value = .Cells(recNo + 1, "e")
        Contact.Value = .Cells(recNo + 1, "f")
        Jobtitle.Value = .Cells(recNo + 1, "g")
        Address1.Value = .Cells(recNo + 1, "h")
        Address2.Value = .Cells(recNo + 1, "i")
        Address3.Value = .Cells(recNo + 1, "j")
        Postcode.Value = .Cells(recNo + 1, "k")
        Email.Value = .Cells(recNo + 1, "l")
        Directline.Value = .Cells(recNo + 1, "m")
        Mobilenumber.Value = .Cells(recNo + 1, "n")
        ListBox2.Value = .Cells(recNo + 1, "o")
        Potentialfirstorder.Value = .Cells(recNo + 1, "p")
        Annualorderforcasr.Value = .Cells(recNo + 1, "q")
        Notes.Value = .Cells(recNo + 1, "r")
        ListBox1.Value = .Cells(recNo + 1, "s")
        Originnotes.Value = .Cells(recNo + 1, "t")
        ListBox3.Value = .Cells(recNo + 1, "u")
        Qualifyreasons.Value = .Cells(recNo + 1, "v")
        meetingdate1.Value = .Cells(recNo + 1, "w")
        meetingagenda1.Value = .Cells(recNo + 1, "x")
        meetingdate2.Value = .Cells(recNo + 1, "y")
        meetingagenda2.Value = .Cells(recNo + 1, "z")
        meetingdate3.Value = .Cells(recNo + 1, "aa")
        meetingagenda3.Value = .Cells(recNo + 1, "ab")
        ListBox4.Value = .Cells(recNo + 1, "ac")
        Contractreasons.Value = .Cells(recNo + 1, "ad")
        Firstorder.Value = .Cells(recNo + 1, "ae")
        Firstorder£.Value = .Cells(recNo + 1, "af")
        Dateoforder.Value = .Cells(recNo + 1, "ag")
        Ordernumber.Value = .Cells(recNo + 1, "ah")
        ListBox6.Value = .Cells(recNo + 1, "ai")
        Contractlength.Value = .Cells(recNo + 1, "aj")
        Criteria.Value = .Cells(recNo + 1, "ak")
        Reporting.Value = .Cells(recNo + 1, "al")
        Reportingdates.Value = .Cells(recNo + 1, "am")
        Accountcoordinator.Value = .Cells(recNo + 1, "an")
        Contractnotes.Value = .Cells(recNo + 1, "ao")
        
        Scheme.SetFocus
        
    End With
    
    Me.txt_RecNo.Text = recNo
    Me.txt_RecNo.Tag = recNo
End Sub