Conditionally unprotect cells to allow data entry

[wpryan]

Hi All,

I have a workbook that is used for service report data entry, and want to allow entry in a cell range only if the value in the adjacent cell contains certain text. The code I'm using is this (where Column A is a data range selectable by a drop down box and Column B is the data range to lock or unlock):

Please Login or Register  to view this content.

...and so on until row 23. However, where the code falls short is that, if someone selects the text required to unlock the cells, and then changes their mind and changes the value back to something else, the cell in column B will now be locked with (now invalid) data entered into it. I need some code to both unlock the cell and to allow data entry, and if the data in Column A changes to something that should cause the cell to be locked, to first clear the cell's contents before locking it.

...anyone? Thanks in advance!

[royUK]

Your post does not comply with Rule 3 of our Forum RULES. Use code tags around code. Posting code without them makes your code hard to read and difficult to be copied for testing. Highlight your code and click the # at the top of your post window. For more information about these and other tags, found here

[wpryan]

My apologies, code is here:

Please Login or Register  to view this content.

[shg]

...and so on until row 23

This is a loop?

Please Login or Register  to view this content.

There are better ways to do this if the code is to be triggered by a change in col A.

[royUK]

You should edit the original post

[FunkyFox]

It might be worth using the Workbook_SheetChange event along with application.intersect to trap changes in the cells you are watching.

eg.

Please Login or Register  to view this content.

end sub

[FunkyFox]

Please Login or Register  to view this content.

[wpryan]

Hi shg,

Thanks for the response. The original code wasn't intended to be a loop because unfortunately, I don't know how to do them (relatively new at VBA - but taking a course in 2 weeks!) but I think the workflow would be better if it were. I tried the code on a blank worksheet and it seems to work well. I'll try to incorporate it in the 'real' form and if there's a problem I'll let you (all) know. Thanks again.

[shg]

As I said, if the code is supposed to be responsive to a change in col A, there are better ways to do this. If you explain, I'll show you.

[wpryan]

Thanks again. The objective is to protect data integrity, so of the list of 10 or so items in the drop down boxes of column A, if the items are as described before then the adjacent cells in column B should be unlocked for data entry. What happens in practice, however, is that a person will select a value which unlocks the cell in column B and then change their mind, go back to column A and change the value to one which then locks column B and unfortunately, the data in it is also locked. The workaround is to select some value to unlock column B again, then delete the value, and then go back to column A to enter the value they wanted. As I said, this code seems to work all right. If you have a better idea, I'm happy to learn from you!

[FunkyFox]

If you wanted to remove invalid entries from column A you could use the following..

Please Login or Register  to view this content.

If the user enters a value that isn't in your 'allowed list then it is deleted automatically and the cell re-locked. The 'enableevents' is there to prevent excel endlessley looping as the event will be called again when the cell gets cleared.

HTH.

[shg]

The 'enableevents' is there to prevent excel endlessly looping

The ClearContents will retrigger the event.

Maybe like this:

Please Login or Register  to view this content.

Code goes in the Sheet module:

Adding Code to a Sheet module
1. Copy the code from the post
2. Right-click on the tab for the relevant sheet and select View Code. This opens the Visual Basic Editor (VBE) and shows the object module for the selected worksheet.
3. Paste the code in the window
4. Press Alt+Q to close the VBE and return to Excel

[FunkyFox]

Shg..

The ClearContents refers to cells outside the watched range so would not trigger a fresh call.

I do agree that re-enabling events on error is a good idea though.. missed that one.

Don't suppose you've any experience with FScommand.. Im getting no replies to my post today.

[shg]

The ClearContents refers to cells outside the watched range so would not trigger a fresh call.

It will retrigger the change event, irrespective of whether it's in or out of the tested range. Try it.

[FunkyFox]

Not sure what you mean, all I get is that if you enter a value from wpryan's list in column A it unlocks the adjacent cell.. if the value is invalid it locks the cell and deletes the value entered..

The worksheet change fires but exits if there is no everlap with column A.. I thought that was what was required..

[shg]

The worksheet change fires but exits if there is no everlap with column A.

That's true, and your method is the only way in certain circumstances (like for userforms, for which you can't disable events, and need instead to set a flag to distinguish between manual entries and code).

The better practice, when possible though, is to avoid needlessly triggering an event, and here that's easy to do.

[wpryan]

Thanks all, for your excellent help. I notice with the last bit of code that if I select the values expected to allow data entry, the code works well, but if I change it to something that should clear the cells, the cells are only cleared when I go back to the cell in column A - in other words, I select the value in colunm A, hit Enter or the arrow key, but the value in column B stays the same. Only when I go back to the cell in column A does the cell get cleared.

shg, would you be so kind as to explain the"me.protect" and "UserInterfaceOnly=True" line? In other worksheets I am locking and unlocking the form for various things, by using the protect and unprotect commands - of which there are a lot. Your solution seems to be the more elegant one.

Thanks again!

[shg]

Only when I go back to the cell in column A does the cell get cleared.

If it's not doing what you want, please explain the logic your looking for.

explain the"me.protect" ...

In a sheet module, Me refers to the sheet; in the ThisWorkbook module, it refers to the workbook, and in a userform module, it refers to the form.

... and "UserInterfaceOnly=True"

It allows VBA to modify the sheet while blocking the user interface, for reasons just like this.

[wpryan]

QUOTE]If it's not doing what you want, please explain the logic your looking for.[/QUOTE]
I was hoping that as soon as the change is registered by the selection of the item in the drop down box in column A, that the change would be done in the adjacent cell in column B. However, I need to go back to the cell in column A in order that the change in column B is registered. Thanks again for all of your help.

[shg]

However, I need to go back to the cell in column A in order that the change in column B is registered.

Not for me; col B changes as soon as you make an entry in col A.

If that's not what you're seeing, post the workbook.

[wpryan]

Not for me; col B changes as soon as you make an entry in col A.

If that's not what you're seeing, post the workbook.

Here you go! I extracted the worksheets from the original, they are behaving the same.

[shg]

The code works fine; you put it in the SelectionChange event instead of the Change event.

[wpryan]

That's fantastic, works like a charm. Thanks a million!