+ Reply to Thread
Results 1 to 4 of 4

lock cells for edit through VBA

  1. #1
    Forum Expert icestationzbra's Avatar
    Join Date
    01-07-2004
    MS-Off Ver
    2007, 2010
    Posts
    1,421

    lock cells for edit through VBA

    hi,

    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,

    mac.

  2. #2
    Forum Expert icestationzbra's Avatar
    Join Date
    01-07-2004
    MS-Off Ver
    2007, 2010
    Posts
    1,421
    hi,

    could someone please help me with this problem?

    thanks,

    mac.

  3. #3
    Forum Contributor
    Join Date
    01-11-2004
    Location
    England
    MS-Off Ver
    Excel 2007
    Posts
    388
    Hi!

    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
    ActiveSheet.Protect password:="password"
    GoTo QuickExit
    End If

    Does anything in this rambling spark any inspirations?

    Alf

  4. #4
    Registered User
    Join Date
    05-13-2013
    Location
    Chicago, IL
    MS-Off Ver
    Excel 2007
    Posts
    10

    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.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1