+ Reply to Thread
Results 1 to 8 of 8

Lock a cell after it is populated - Code needed

  1. #1
    Registered User
    Join Date
    08-09-2019
    Location
    Leicester, England
    MS-Off Ver
    Excel for O365
    Posts
    12

    Lock a cell after it is populated - Code needed

    I have a code which populates a cell automatically with the date following a selection from a drop-down list in another cell.

    I now need to protect that date cell from being edited manually after the date has been added.

    Failing that, could the date cell be formatted to only accept inputs from the existing code and nothing else?

    The code I have looks like this (ignore the Dim myMail as this refers to other code):

    Please Login or Register  to view this content.

  2. #2
    Valued Forum Contributor
    Join Date
    06-29-2014
    Location
    Australia
    MS-Off Ver
    MSO 365
    Posts
    1,095

    Re: Lock a cell after it is populated - Code needed

    Hello GlynB,

    Failing that, could the date cell be formatted to only accept inputs from the existing code and nothing else?
    It may be a better option so under your existing event code, place this one:-


    Please Login or Register  to view this content.
    A message box will appear as soon as a User clicks on a cell in Column M that has a date in it warning the User that no manual entries are allowed. Once OK is clicked, the cursor will return back to a cell in Column J.
    It will allow manual entries if a cell is blank.

    Just an option for you.

    I hope that this helps.

    Cheerio,
    vcoolio.
    Last edited by vcoolio; 10-17-2019 at 05:30 PM.

  3. #3
    Registered User
    Join Date
    08-09-2019
    Location
    Leicester, England
    MS-Off Ver
    Excel for O365
    Posts
    12

    Re: Lock a cell after it is populated - Code needed

    Hi vcoolio,

    I gave this a try and unfortunately it doesn't work as I hoped it would. Here are the symptoms:


    Select "Resolved" from the drop-down in column J - Date is populated in column M AND Message box appears.
    Click"ok" to clear the message box
    Click the date in column M and nothing happens.
    Double click the date in column M and flashing cursor appears. Still no message box.
    Click any other cell in the work sheet (including other cells in column M with dates) and the message box appears.
    Click "ok" to clear the message box.
    Click any cell in column M with date. No message box.

    Any clues?

  4. #4
    Valued Forum Contributor
    Join Date
    06-29-2014
    Location
    Australia
    MS-Off Ver
    MSO 365
    Posts
    1,095

    Re: Lock a cell after it is populated - Code needed

    Hello Glyn,

    Before submitting the option, I tested it in a sample of how I assumed your workbook is set out and the code worked exactly as it should have. No problems or errors.
    Not knowing exactly how your workbook is set out, please upload a sample of it so that we can test the code on your actual data set. Make sure that the sample is an exact replica of your workbook and include any codes that you may have already implemented in it. If your data is sensitive, then please use dummy data.

    Cheerio,
    vcoolio.

  5. #5
    Registered User
    Join Date
    08-09-2019
    Location
    Leicester, England
    MS-Off Ver
    Excel for O365
    Posts
    12

    Re: Lock a cell after it is populated - Code needed

    Hi vcoolio,

    Please see attached.

    I have left your code in there too.
    Attached Files Attached Files

  6. #6
    Valued Forum Contributor
    Join Date
    06-29-2014
    Location
    Australia
    MS-Off Ver
    MSO 365
    Posts
    1,095

    Re: Lock a cell after it is populated - Code needed

    Hello GlynB,

    The problem has arisen because you added the code to your existing one.

    If you look carefully, you'll see that your code is a Worksheet_Change event code whereas mine is a Worksheet_SelectionChange event code. They cannot be combined.

    This is why I asked that you place my code under your existing one.

    Attached is your sample workbook with the code correctly implemented. You'll notice that all works as it should now.

    Cheerio,
    vcoolio.
    Attached Files Attached Files

  7. #7
    Registered User
    Join Date
    08-09-2019
    Location
    Leicester, England
    MS-Off Ver
    Excel for O365
    Posts
    12

    Re: Lock a cell after it is populated - Code needed

    Thanks vcoolio.

    That was indeed my error. This works now.

    Thanks for your help.

  8. #8
    Valued Forum Contributor
    Join Date
    06-29-2014
    Location
    Australia
    MS-Off Ver
    MSO 365
    Posts
    1,095

    Re: Lock a cell after it is populated - Code needed

    You're welcome Glyn. I'm glad to have been able to assist.

    Cheerio,
    vcoolio.

+ 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. Replies: 11
    Last Post: 01-23-2017, 11:40 AM
  2. Replies: 1
    Last Post: 08-20-2015, 12:46 PM
  3. Lock cells if another cell is populated
    By garveyarmy in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 03-31-2014, 09:52 AM
  4. Code to restrict cell input until another cell is populated
    By chavanalini in forum Excel Programming / VBA / Macros
    Replies: 21
    Last Post: 11-19-2013, 04:37 AM
  5. Replies: 0
    Last Post: 02-03-2013, 06:33 PM
  6. Attempting to lock dates into a cell after they are populated
    By svtcobrar_410 in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 11-28-2012, 01:08 PM
  7. Code to restrict cell input until another cell is populated
    By Brandy in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 04-20-2010, 11:29 AM

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