macro to insert row on last cell in a table of a protected worksheet
Hi everyone,
My first post, so hopefully I get some help.
I have a protected worksheet with formulas in cells A to AB, and approx 30 rows.
I would like to allow the user insert a new row after entering data into the last cell in in the worksheet by either using tab or return.
I would like all the formulas to copy to the new row.
I would like the user to be able to put in as many rows as needed.
Copy and paste this macro into the worksheet code module. Do the following: right click the tab for your Weld and NDT Log sheet and click 'View Code'. Paste the macro into the empty code window that opens up. Close the code window to return to your sheet. Make a selection in cell B28. A new row with the drop down lists will be inserted below. This will happen each time the last cell in column B is populated.
If your sheet is protected with a password, the code will have to include that password.
Please Login or Register to view this content.
You can say "THANK YOU" for help received by clicking the Star symbol at the bottom left of the helper's post.
Practice makes perfect. I'm very far from perfect so I'm still practising.
That worked great! However, when I apply the code to a similar worksheet it does not work. I'm I missing something.
You have to make sure that the macro is in the worksheet code module for the other sheet and that the other worksheet has exactly the same set up. Unfortunately "similar" is not good enough. They have to be identical in setup.
I copied and pasted the code exactly. The sheet works the same way, just different columns of info.
I really appreciate your help. I have attached the new file.
I noticed that the two sheets are not identical. In the most recent version you have a "Comments" line which wasn't in the original version and to complicate matters that line is in an area of merged cells. You should avoid using merged cells as they almost always cause problems for excel macros. The macro also assumes that column A will never be empty. Try this macro:
Please Login or Register to view this content.
Thanks for your help.
I noticed that when a user adds a row the protect sheet settings default back to select unlocked cells.
How can I keep the settings I select when I protect the sheet.
I would like the to be able to use:
auto filter
sort
format rows
delete rows
pivottable reports
Try:
Please Login or Register to view this content.
That works great, however on another sheet the protect sheet settings would not let me delete the row. It was still asking for me to unprotect the sheet. I attached the workbook so you can see whats happening.
I have one last form I am trying to get to work the same way. But I get a bug. It is not identical to the others. Any help would be greatly appreciated.
Try the attached file.
Fantastic!
Ok last one.
Same problem. Tried the latest code still didn't work.
Don't understand why its not working. Again, thanks so much for your help
If it's not working when you try to delete rows, I couldn't get it to work either and to be honest I really don't have a solution. I researched the issue and we seem to be doing everything right but still can't get it to work. Could I suggest you start a new thread explaining the exact nature of the problem. Attach a copy of your file. Most likely another forum member with more experience will be able to help. I would appreciate it that if you do find a solution, if you could send me a private message with a link to that solution so I can have a look. Sorry I couldn't be of more help.
Ok thanks for trying.
You're welcome.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks