Hi, I have a Workbook here that users need to assign job numbers to serial numbers. Once the data is entered and saved, I want it to be locked once the Workbook is opened again. I have found many examples, but none of them really match what I need. The only difference here is that column should stay locked permanently as they are fixed numbers.
Test.xlsm
Try that
Elegant Simplicity............. Not Always
Andy, thanks for the reply. This almost works just how I need it. I need column A to be protected permanently as they will not change. Then B, C, D should be locked out once data has been entered. I hope I am explaining correctly.
Test.xlsm
Okie dokie...... Modified it so now all data entries in columns A:D are a one time only.
Remarks in columns E onwards are still allowed.
Andy, this is what I'm dealing with right now. I think I need to change my real file. Attached is the actual mess I've come up with. I have 3 sheets and other code to perform other things. I want only B, C, D to allow edits until the data is finished. A macro totals in column E as you will notice. Thanks for your help and time.
Andy? Or can someone please help?
Sorry, only just back online - having a look now..
I've disabled your macro's temporarily and put in a worksheet module code to automate the process as much as possible.
To assign a new number, all you need do is double click on the green cell..... All values are entered automatically and then those cells are locked down to prevent editing.
Please Login or Register to view this content.
SERIAL NUMBERS_TEST.xlsm
Last edited by AndyLitch; 06-14-2013 at 10:43 AM.
Thanks Andy, this is some great code. I like how it works. Is there a way to make it similar to how I had it for entering a total of how many lines to fill? The users can have up to 35 entries on one sheet and i know counting that many each time would be a pain. Thanks very much for your help
Andy, I neglected to mention that in column B, those numbers will vary. The initial block of numbers will be much different the next time the file is used.
Yes we can accommodate those requirements I reckon... In fact I'm pretty sure you can do it with a few pointers.
My approach would be to:-
a) intercept the double click once the cell is verified as correct (just after unprotect) and get a repeat number from the user via an inputbox
b) setup a loop to fill in all the basic details for each repeat item
c) detect the final manually entered item and then re-protect the sheet.
Have a bash at that and shout if you need any help
Thanks Andy, I am messing with it now. The trouble I'm having is how to increment the function serialGen multiple times?
Don't increment the function.. Your loop needs to be within the main sub.. E.g.
The basic format modifications are shown in red... So the main sub calls the function the repeat number of times as required... Don't forget that the row addressing now also needs to incorporate the X value ..Please Login or Register to view this content.
You'll also need to give some thought to how and when to reapply protection ... This may need to be associated with another event triggered macro associated with data entry to column B - It will need to test for a last entry and then apply the sheet protection.
Last edited by AndyLitch; 06-14-2013 at 12:55 PM.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks