Updating a spreadsheet using a userform

quintin · Nov 11, 2014

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.

Code:

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

rajkavikumar · Nov 11, 2014

Please assign row number to variable "currentrow" and check if Worksheet name specified in code ("PATIENT DATA") is correct.

Also specify at which line you are getting this error.

quintin · Nov 11, 2014

Hi, I am getting the error on line -

Worksheets("PATIENT DATA").Cells(currentrow, "a").Value = tb_nsurname
If I take that line out then it'll move the error to the next line.
I think it's something I'm not or adding into the line.
</pre>

GTO · Nov 11, 2014

As rajkavikumar stated, currentrow (Dim currentrow As Long) is declared/dimension(ed), but no value (row number) is assigned. Thus, as the default value of an Integer or Long variable is 0... and there is no row zero, the code will fail. You must at minimum, get a row number. Does that make sense?

Mark

quintin · Nov 11, 2014

not 100%, but I will give it a try - many thx

quintin · Nov 11, 2014

I tried something like that, but still no luck - I then took long out and put string in it's place, then it gave me a message error 13 - okay then I took currentrow out completely and tried found.offset and there again no luck.
All I want to do is to update the userform so that it updates the speardsheet.
many thanks

GTO · Nov 11, 2014

Hi quintin,

Off to bed for me, but for any 'answerer' and you, hopefully this will help:

You do not describe what "found.offset' is. Presuming (chancy at the moment) that "found" is a range reference, then .Offset returns a Range Object as well; but you are looking to return an Integer (whole number) value to allow .Cells() to work.

Slow down! With nothing negative intended, you seem to be aimed at getting an answer without truly understanding what we are doing with the code.

In most cases, 'code' is just doing what we could do 'manually' with the interface (that is, working directly in Excel). But when we are telling the computer to do it for us, we need to issue very explicit instructions. Thus, providing a String (text) as an argument, or, providing a 'I want you to put this value in row zero' is not likely to return wanted results.

I hope that makes sense and is taken in the friendly manner intended.

Mark

quintin · Nov 11, 2014

Mark,
I would just like to thank you for explaining it the way you did and yes I like to understand (break something apart) but sometimes a get rather frustrated with myself/the coding and then “JUST” want the answer.
I think I’ll try reading up more about it and then maybe I’ll get it.
Quintin

quintin · Nov 12, 2014

Good day,

I have manage to get it to update the sheet, however it only always replaces the first row - this is what i have done so far

Code:

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

GTO · Nov 12, 2014

quintin said:
Mark,
I would just like to thank you for explaining it the way you did...

Greetings Quintin,

Very happy to help with what I can and most happy that you took my comments in the positive manner intended.

quintin said:
...but sometimes a get rather frustrated with myself/the coding and then “JUST” want the answer...

Me too brother! Whilst VBA seems intuitive AFTER doing it a bit, it's still code. Work, family, LIFE..., all those pesky things to interfere with trying to get the application/workbook to do some fantastical things - Jiminy!

quintin said:

Good day,

I have manage to get it to update the sheet, however it only always replaces the first row - this is what i have done so far

Code:

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

I am not sure what you are wanting to do when you say it replaces only the first row. 'found' is not declared (As a Range), leastwise not in the procedure. 'currentrow' is, although defined as a variable, used as a constant and thus, only row 3 is going to get updates.

Might I suggest posting some data (what is on the sheet now) and what we want to do in plain English?

Something like: "I have a userform named [something] with a textbox named [something else]. After typing in "[some value]" in the textbox, when I click the commandbutton, I'd like the code to run through column [n] and [do something] in [the last row the value is found/the first row the value is found/or something else?]."

I hope that all makes sense; I am beat harder than a bad hound-dog. Off to bed, but I hope to check back tonight.

Mark

Updating a spreadsheet using a userform

quintin

Board Regular

Excel Facts

rajkavikumar

New Member

quintin

Board Regular

GTO

MrExcel MVP

quintin

Board Regular

quintin

Board Regular

GTO

MrExcel MVP

quintin

Board Regular

quintin

Board Regular

GTO

MrExcel MVP

Similar threads

Forum statistics

Share this page

Updating a spreadsheet using a userform

Board Regular

Excel Facts

New Member

Board Regular

MrExcel MVP

Board Regular

Board Regular

MrExcel MVP

Board Regular

Board Regular

MrExcel MVP

Similar threads

Forum statistics

Share this page

We've detected that you are using an adblocker.

Which adblocker are you using?

Disable AdBlock

Disable AdBlock Plus

Disable uBlock Origin

Disable uBlock