Hello, I would like to know how i could use VBA to offer the next number and fields in a register (see Pic) and lock all of the previous details so they can not be edited after each save. Is this possible?register.png
Hello, I would like to know how i could use VBA to offer the next number and fields in a register (see Pic) and lock all of the previous details so they can not be edited after each save. Is this possible?register.png
Not sure what you mean by "off the next number and fields".
Could you attach a workbook, rather than a pic of one, and show us an example of what needs to be locked?
BSB
thanks for the reply. I hope the attached workbook clarifies things.
Here's one quick and easy method.
Every time you hit save it will unprotect the sheet, update which rows are 'locked' from editing then reapply the protection.
You could do similar in other ways, for example when you enter a value in the last column of a row it would protect the rows from that point upward, but you could run into issues with that if a user accidentally puts a value in that column.
Try the attached and see if it works for you as it is. If not we can revisit.Please Login or Register to view this content.
BSB
Hello. In this model, the sheet is fully protected except for the cells in row 2:
a) Write in row 2 the information you want, and
b) Enter the data in listObject by 'clicking' on the button.
PHP Code:
Sub Macro_8()
Unprotect "myPassword"
ListObjects(1).ListRows.Add.Range = Cells(1).CurrentRegion.Rows(2).Value
Protect "myPassword", True, True, True, , , , , , , , , , True, True
End Sub
You are always very welcome if you add reputation by clicking the * (bottom left) of each message that has helped you.
works perfectly, thanks. The only change I would like, if the report number in the top left that you fill in would automatically be locked with the next sequential number. Is there a way to do this?
Do you think it's OK?...
Last edited by beyond Excel; 04-27-2023 at 08:19 AM.
this works great! I have made some tweaks to suit my purpose but i was wondering if there was a line of vba that could clear all of the fields except for the date and report no fields when closing the workbook? I have attached a copy of my progress so far...
Mmm...
I think you missed post #7: It does exactly that!
Sorry, you're right. I was struggling trying to get it to work with my newest sheet. A job for tuesday, i reckon. Thanks for your help and have a great weekend.
The same to you.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Bookmarks