lock cells for edit through VBA
is there a way to lock certain cells for editing through a piece of VBA code?
i have some data in cells D1:D100. i would like to prevent people from editing those cells, it could pop-up a message "cannot edit" or something like that.
i tried using Target.AllowEdit = False in the SelectionChange event, it does not work. i do not want to use Protect Sheet from the Tools menu for some reasons.
thanks in advance for any inputs,
could someone please help me with this problem?
I note your aversion to sheet Protection.
Would it be acceptable to set all cells to "Unlocked" and then apply sheet protection?
I ask, because you could then apply local protection to selected cells either permanently (beforehand from the menu) or on a temporary basis using VBA to switch cell locking on and off.
The sort of situation where I've used it involved locking cells to input if certain conditions applied (e.g. either put in the aggregate of two numbers or put in the 2 separately but don't allow mixing the methods).
If (IsEmpty(rngL) = False) Or (IsEmpty(rngK) = False) Then ActiveSheet.Unprotect "password"
rngK.Locked = False
rngL.Locked = False
rngM.Locked = True
Does anything in this rambling spark any inspirations?
Re: lock cells for edit through VBA
Old post, but I thought I'd chime in with my standard solution to this.
Set data validation, with a custom formula of "=FALSE". This way, any attempt to change the value will result in an error message popping up, and by the default data validation settings, disabling people from changing the value.
Of course, if they are savvy, they could turn off data validation. Also, this does not guard against people deleting the value (selecting the cell and pressing the Delete key).
Going a bit deeper than I have in the past now, but if that isn't agreeable, I suppose you could also write a Macro to fire on a change to a value, and you could see if the value beign changed is one of the ones you are trying to protect, and if it is, you give an error message (msgbox), and delete the (newly entered) value, or ortherwise reset it (though you'd have to custom define what constitutes resetting it).
I suppose you could go even further and make a 'fake sheet protection' using this, plus a dialog box (userform) that pops up asking for a password. That would be a lot of work though.
Users Browsing this Thread
There are currently 1 users browsing this thread. (0 members and 1 guests)
Search Engine Friendly URLs by vBSEO 3.6.0 RC 1