Trying to create and update button to edit a record in a spreadsheet - the following code is being used - but when I select the row to update the area highlighted in red shows Runtime error #1004 - Application or Object defined error.
Can anyone advise ?
Best Regards,
Please Login or Register to view this content.
John
you havent set the value of currentrow.
If you're looking for the row thats active then you'll need Activecell.row
In the code you posted currentrow has no value.
If posting code please use code tags, see here.
still getting error - is there an example? -Please Login or Register to view this content.
There are hundreds of records - I have a Listbox that shows the records in the userform - I want to update the record in the fields of the userform and then update the record with the button when complete
Last edited by JJFletcher; 10-25-2014 at 04:41 PM. Reason: corrections
alternatively, declare currentrow variable as the activecell.row which makes the changes to your code much smaller.Please Login or Register to view this content.
Please Login or Register to view this content.
Please Login or Register to view this content.
Regards, TMS
Trevor Shuttleworth - Retired Excel/VBA Consultant
I dream of a better world where chickens can cross the road without having their motives questioned
'Being unapologetic means never having to say you're sorry' John Cooper Clarke
I have made the modifications - yet still get the error???
error.JPG
I don't have a form, textbox or command button so I can't test YOUR code.
But that line of code works. Not sure if it's because the Textbox is selected.
Like I say, I can't test it. Suggest you post a sample workbook.
Regards, TMS
Ok - I will Post
Hello JJ Fletcher,
I've revised your code. Please use the below code on a module and rename the worksheet as appropriate.
Please Login or Register to view this content.
HR Dashboard Master V3.xlsm
This is the Workbook - I really appreciate your looking at it:
When selecting the Add Data Button - the password is : " Password " without the quotes.
I received this error with the modifications you provided jayesh25
Error3.JPG
Sorry my bad. Didn't get a chance to test it. Typed it out via mobile.
Could you change that to
Please Login or Register to view this content.
Jayesh25,
Trying your code and the data in the fields in the Userform when they are changed do not update the record in the Listbox.. The record is updated - it is appended to the bottom of the spreadsheet - the actual record selected from the Listbox on the Userform is not updated - only added to the bottom of the sheet
Probably you can look at the Workbook I sent up?
John
Please Login or Register to view this content.
Regards, TMS
Maybe:
Please Login or Register to view this content.
Regards, TMS
I tried this to the code and it deleted row 32 of the spreadsheet
It did not make any modifications to the spreadsheet or the linkbox data
Any Thoughts
TMS,
I tried your code also - did not modify the data in the spreadsheet or the data in the textbox - once the update button was selected the data that was originally there simply reposted back
Have you looked at the workbook sent uploaded?
Not sure why that would be. There's no code to delete anything.
Rather than put the text box values into variables, and then setting the cell to the variable, it just does it directly ... no middle man.
If you are making changes to the entries, I would expect you to have to re-initialise/reload the link box.
Whatever, I think you need to fully qualify the cell, that is, use the worksheet name ... as shown in post #15
TMS,
This is your code that was entered into the Userform.
I selected the top record in the textbox on the userform and it also is the first record in the spreadsheet that I am trying to modify and update. The information in the useform did not change - in fact it made no modification of the data in the userform textbox at all - and no change to the first record on the spreadsheet that was selectedPlease Login or Register to view this content.
However,
What did happed though once the information was modified on the userform and the Update button was selected - I went and looked at the spreadsheet because there were no changes visible on the userform textbox - the record being modified was duplicated at the bottom of the spreadsheet - 7 rows below the last record -INTERESTING!
Any Thoughts
For the Update, Delete and some other functions, you must find the row of the employee record in the database. Activecell could be anywhere! If you are going to ADD a new record, then you must find the row of the last existing record, etc.
Please Login or Register to view this content.
Last edited by protonLeah; 10-25-2014 at 08:39 PM.
Ben Van Johnson
ProtonLeah,
It worked perfect!!! Where have you been for the last three days - WOW!
You did it! Thank you sooooo Much!
One last question - you mention in your comment that Update, Delete and some other functions, you must find the row of the employee record in the database - can this code also be used to delete a record using the userform / textbox and also from the spreadsheet?
Fantastic Job to all who helped!
Great Contribution!
John
The code:
Set lACRow = .Range("A:A").Find(txtfullname.Text)
tells you the row of the record highlighted in the listbox on the user form.
For the DELETE function, you need code to select the entire row and delete it:
Something like:
.Range("A" & foundrow).entirerow.delete
---------------
Your current code for the DELETE works but, if you use the .find function you can eliminate the For..Next/test loop.
Last edited by protonLeah; 10-25-2014 at 10:46 PM.
I will try and create the code - If it fails I will post for help "LOL"
Thanks!
Thanks to all! - [SOLVED]
You're welcome.
If you are satisfied with the solution(s) provided, please mark your thread as Solved.
New quick method:
Select Thread Tools-> Mark thread as Solved. To undo, select Thread Tools-> Mark thread as Unsolved.
Or you can use this way:
How to mark a thread Solved
Go to the first post
Click edit
Click Go Advanced
Just below the word Title you will see a dropdown with the word No prefix.
Change to Solved
Click Save
You may also want to consider thanking those people who helped you by clicking on the little star at the bottom left of their reply to your question.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks