Updating a spreadsheet using a userform

[quintin]

Good day I am trying to update a spreadsheet via a userform and am not having much luck. I have had error codes but am not sure what or how to fix them, this is the code i'm getting now (run time error 1004 vba application defined or object defined)
If somebody could just point me in the right direction, I would be happy.
I have managed to get it to bring all the info up, when you scroll through a combobox, as I said trying to get it to update is not working. Please find that part of the code bellow.

Private Sub cmb_update_Click()

Dim myname As String, myrange As Range
Dim currentrow As Long
myname = tb_nsurname.Text
Worksheets("PATIENT DATA").Cells(currentrow, "a").Value = tb_nsurname
Worksheets("PATIENT DATA").Cells(currentrow, "b").Value = tb_name

    
End Sub

 

[quintin]

Hi there,
Okay let me start (try to explain what I'm doing) I have a userform that the user will enter data in (name,surname,id,address,town and etc) when the click submit, it put all the data onto a spreadsheet. I have another userform that a person is about to view a clients data and to see what ever they need to see - NOW the userform that I am currently busy with is able to show all that data, however I am trying to find a way to overwrite/update a clients details, so lets say name jim surname jones address 34 grey st now has moved and I need to call that client up and be able to change his/hers address or whatever needs changing.
The spreadsheet has columns with into going across (as i said, name, surname and etc) it starts at row 3 and always updates when a new client is added...at the moment there are over 1300 rows.
I am going to post that whole code in that is used on the userform called update.
I hope i've made sense adn thank you once again.
Quintin

Private Sub cb_surname_Change()
 Dim myname As String, myrange As Range
    
    myname = Me.cb_surname.Text
    Set myrange = ThisWorkbook.Sheets("PATIENT DATA").Range("a:a")
    Set found = myrange.Find(myname, LookIn:=xlValues)
    If Not found Is Nothing Then
        Me.tb_nsurname = found.Offset(, 0)
        Me.tb_name = found.Offset(, 1)
        Me.tb_id = found.Offset(, 2)
        Me.tb_street = found.Offset(, 3)
        Me.tb_suburb = found.Offset(, 4)
        Me.tb_town = found.Offset(, 5)
        Me.tb_country = found.Offset(, 6)
        Me.tb_email = found.Offset(, 7)
        Me.tb_home = found.Offset(, 8)
        Me.tb_work = found.Offset(, 9)
        Me.tb_cell = found.Offset(, 10)
        'Me.tb_refby = found.Offset(, 11)
        'Me.tb_refbypat = found.Offset(, 12)
        Me.tb_mainsurname = found.Offset(, 13)
        Me.tb_mainname = found.Offset(, 14)
        Me.tb_mainid = found.Offset(, 15)
        Me.tb_scheme = found.Offset(, 16)
        Me.tb_option = found.Offset(, 17)
        Me.tb_medno = found.Offset(, 18)
        Me.tb_maincode = found.Offset(, 19)
        Me.tb_patcode = found.Offset(, 20)
        'Me.tb_lastvisit = found.Offset(, 23).Text
        'Me.tb_firstvisit = found.Offset(, 22).Text
        'Me.tb_sincelastvisit = found.Offset(, 47)
        'Me.tb_dob = found.Offset(, 38).Text
        'Me.tb_age = found.Offset(, 39).Text
        'Me.tb_lastrec = found.Offset(, 44)
        'Me.tb_noofvisits = found.Offset(, 46)
        'Me.tb_listing = found.Offset(, 24)
        'Me.tb_amount = found.Offset(, 45)
    Else
        Me.tb_nsurname = ""
        Me.tb_name = ""
        Me.tb_id = ""
        Me.tb_street = ""
        Me.tb_suburb = ""
        Me.tb_town = ""
        Me.tb_country = ""
        Me.tb_email = ""
        Me.tb_home = ""
        Me.tb_work = ""
        Me.tb_cell = ""
        'Me.tb_refby = ""
        Me.tb_mainsurname = ""
        Me.tb_mainname = ""
        Me.tb_mainid = ""
        Me.tb_scheme = ""
        Me.tb_option = ""
        Me.tb_medno = ""
        'Me.tb_lastvisit = ""
        Me.tb_maincode = ""
        Me.tb_patcode = ""
        Me.tb_maincode = ""
        'Me.tb_firstvisit = ""
        'Me.tb_sincelastvisit = ""
        'Me.tb_dob = ""
        'Me.tb_age = ""
        'Me.tb_lastrec = ""
        'Me.tb_listing = ""
        'Me.tb_amount = ""
        'Me.tb_noofvisits = ""
        
        
    End If
End Sub

Private Sub cmb_return_Click()
Unload Me
End Sub


Private Sub cmb_update_Click()
Dim myname As String, myrange As Range
Dim currentrow As Long
Dim finalrow As Long

currentrow = 3
myname = tb_nsurname.Text
Set myrange = ThisWorkbook.Sheets("PATIENT DATA").Range("a:a")
    Set found = myrange.Find(myname, LookIn:=xlValues)

Worksheets("PATIENT DATA").Cells(currentrow, "a").Value = tb_nsurname
Worksheets("PATIENT DATA").Cells(currentrow, "b").Value = tb_name
Worksheets("PATIENT DATA").Cells(currentrow, "c").Value = tb_id


End Sub

Private Sub UserForm_Initialize()
'fill the combox
    For Each cell In [SURNAME]
        Me.cb_surname.AddItem cell
    Next cell
End Sub