+ Reply to Thread
Results 1 to 2 of 2

2016 Excel VBA Code for Locking a Cell after Data Entery

  1. #1
    Registered User
    Join Date
    09-05-2017
    Location
    Hudson WI
    MS-Off Ver
    2016
    Posts
    2

    2016 Excel VBA Code for Locking a Cell after Data Entery

    I have made a macro for my spreadsheet that is supposed to conditional lock cells aft date is entered. I've tried running the macro but I get a debug notice at this lie cl.Locked = "True"

    This is the full macro: I am hoping someone can help me resolve the issue

    Private Sub Worksheet_Change(ByVal Target As Range)

    Dim cl As Range

    ActiveSheet.Protect Password:="123"

    For Each cl In Target

    If cl.Value <> "" Then

    check = MsgBox("Is this entry correct? This cell cannot be edited after entering a value.", vbYesNo, "Cell Lock Notification")

    If check = vbYes Then

    cl.Locked = "True"

    Else

    cl.Value = ""

    End If

    End If

    Next cl

    Activeshet.Protect Password:="123"

  2. #2
    Forum Expert Arkadi's Avatar
    Join Date
    02-13-2014
    Location
    Smiths Falls, Ontario, Canada
    MS-Off Ver
    Office 365
    Posts
    5,057

    Re: 2016 Excel VBA Code for Locking a Cell after Data Entery

    Hi Loboi,

    I believe it is because you start by Protecting the sheet, and it should start with unprotect. While the sheet is protected you can't set the lock. Also, syntax error in the last line missing an "e" in ActiveSheet
    And remember, cells are locked by default, so start by unlocking cells that the user should be able to change, and THEN protect.

    Additional note, please used code tags in the future. Highlight your code, and hit the # in the menu just above the area you type in:

    Please Login or Register  to view this content.
    Last edited by Arkadi; 05-13-2022 at 10:30 AM.
    Please help by:

    Marking threads as closed once your issue is resolved. How? The Thread Tools at the top
    Any reputation (*) points appreciated. Not just by me, but by all those helping, so if you found someone's input useful, please take a second to click the * at the bottom left to let them know

    There are 10 kinds of people in this world... those who understand binary, and those who don't.

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. [SOLVED] Create outlook 2016 message using excel 2016 data
    By Cephei in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 10-29-2021, 08:53 AM
  2. Replies: 5
    Last Post: 04-16-2018, 08:56 AM
  3. Replies: 1
    Last Post: 09-18-2017, 08:27 AM
  4. Color code data bar in excel 2016
    By infinitely_average in forum Excel Charting & Pivots
    Replies: 2
    Last Post: 11-27-2016, 12:58 AM
  5. Replies: 2
    Last Post: 05-17-2016, 12:23 PM
  6. Excel VBA Code for locking/unlocking a cell based on the value of another cell
    By latourjim in forum Excel Programming / VBA / Macros
    Replies: 5
    Last Post: 05-22-2013, 10:03 PM
  7. Automatic data entery
    By resdin in forum Excel Formulas & Functions
    Replies: 6
    Last Post: 09-14-2012, 12:01 PM

Tags for this Thread

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