Lock cells based on value of other cells - code simplification query

Firstly what are you trying to achieve here?

It seems you are locking several ranges of cells but the way that they are locked means that once they are locked, they can't be unlocked?

As an overall guide, are all the lines you want to work on grouped together, (ie. lines 11 to 50), or are they random lines all over the worksheet?

If the former you can use a For loops to work through the lines directly, if the latter I would create an array of the line numbers and then use a For loop to step through that.

As for the speed, you must remember that the 'Change' event is called each time the spreadsheet changes, which is likely to be what is slowing it down. Each of the cases doesn't need to run everytime, only if what it depends on is affected.

For example:

Please Login or Register  to view this content.

The outcome of that code will only be different if either the value of I11, or J11 has just been changed.
The 'Target' variable of the onchange event tells you what has just changed, so you can check to see if either I11 or J11 were involved. If they were then execute your IF statement, if not then dont bother.

Do you have a sheet with some example data so that we can see how you are intending this macro to be used? (It will also allow us to see the flickering you describe, and just how slow it is).

Thanks Phil_V.

As it's a bit difficult to send a functional worksheet copy because of links to other worksheets and workbooks I'll try to explain the situation. If that doesn't help then I'll spend the time to strip back the workbook to create a workable example.

The worksheet is part of an internal system used for disection/allocation of particular receipts. The locking routine is to assist in preventing the GST (tax) portion of the payment being incorrectly allocated. Three basic types of receipts are dealt with - receipts inclusive of GST, exclusive of GST, and a combination of both.

In all cases Col E is the total amount received. Cols I & J are used for entry of the non-GST components of GST-inclusive receipts, and Col M is the GST amount. Col O is for entry of GST-exclusive amounts. Cols K L N are used for descriptive info for Cols I & J amounts, and Col P Q R are used for descriptive info for Col O amounts.

The first IF deals with receipts that do not include a GST component. If the amounts entered to Cols I & J are both 0, then there is no GST so Col M is locked along with descriptive Cols K L N. Cols O P Q R remain unlocked to allow entry of the GST-exclusive amount and descriptive detail. The ELSE simply provides a get-out-of-trouble routine by unlocking COLS K L N if the entries in Cols I & J are deleted.

The second IF deals with receipts that include GST. If the total of Cols I J & M (non-GST amounts plus GST) equal Col E (total payment) then there is no GST-excluive amount so Cols O P Q R are locked. Alternatively, if the total of Cols I J & M doesn't equal Col E then O P Q R remain unlocked to allow entry of the GST-exclusive part of the receipt and relevant descriptive info. Again the ELSE is just a get-out-of-trouble routine.

Data validation ensures the disected amounts always total the Col E amount.

All the rows using the code are consecutive.

I'm pretty raw when it comes to vba so if I haven't sent you to sleep with the long-winded explanation above some extra help with your FOR suggestion (or any other way of tackling my problem) will sure be appreciated.

Cheers & thanks again.

Ok, longwinded is sometimes the best way to be

The issue that you / I have is that by default, all cells are locked. This means that the when you protect the worksheet only the cells that you have EXPLICITY 'unlocked' will be editable.

So for instance the first time a change is made to the worksheet this code will run:

Please Login or Register  to view this content.

Lets presume that either I11 or J11 is still blank, (as you can only have typed into one of them before causing the 'change' event to trigger).
When the above code runs, it will therefore drop into the 'Else' section unlocking K11:N11 and then protecting the sheet. At that point your are stuck.
You are unable to unlock those cells again by typing into J11, (if that was the one that was still blank), because that cell is now locked, and protected.

I think the first thing you will need to do is have a 'reset' macro that will unlock all of the cells on the worksheet, and then from that point, only lock / unlock the ones you are interested in.

Now that I 'think' I know what you are trying to achieve I shall take a look at it

Glenin,

In the code below I have taken an entirely different approach at the problem, so please let me know if it suits your needs.
Rather than placing this code in the 'Change' event, it is in the 'SelectionChange' event, which is triggered when a new cell is selected.
Rather than getting bogged down in locking & unlocking cells, and protecting / unprotecting the worksheet, the code instead checks when a cell is selected.
When the cell is selected the code will check the other values on that row to see if the cell is valid to have an entry put into it. If it is not it will warn the user, and move the cursor back out of the cell.

There are potentially ways around this protection, but it should stop your casual user from typing in a cell by mistake.
Let me know what you think please, and of course feel free to question anything you are unsure of.

Please Login or Register  to view this content.

Hi Phil_V,

Early am here and just did a quick test using your excellant suggestion.

Seems to work exactly as required, but as I'm tied up with meetings till about midday I won't have a chance to play with it further till I get back to the office. I'll contact you again later today.

Many thanks for your time effort and help.

Cheers.

Thanks for the feedback, will wait to hear more from you.
Note that you are in Aus and I am UK tho, so nearly midnight here, so will be a good few hours before I get back on here

Hi Phil_V,

Apologies for the belated further response - I've been to Borneo since my last post and under pressure since getting back a few days ago.

Your code is just perfect - works like a charm!

Again my thanks.

All the best.