+ Reply to Thread
Results 1 to 16 of 16

Locking dates after Date

  1. #1
    Forum Contributor
    Join Date
    12-30-2021
    Location
    Mount Gambier, Australia
    MS-Off Ver
    19
    Posts
    106

    Locking dates after Date

    I have never used VBA or macros before

    Is there a way I Can lock a cell after say a date

    so I want to lock cells in Column H once the date in C1 is past the date in the corresponding cell in Column B. ie. H25 and B25
    Attached Files Attached Files
    Last edited by wildecoyote1966; 01-02-2022 at 07:54 PM. Reason: correcting details

  2. #2
    Forum Guru Kaper's Avatar
    Join Date
    12-14-2013
    Location
    Warsaw, Poland
    MS-Off Ver
    most often 365, but still time to time use also older versions
    Posts
    8,154

    Re: Locking dates after Date

    Are you sure it is the right file (or the right explanation in the post)?
    Columns you mention in text seem to be different than in a file.
    Best Regards,

    Kaper

  3. #3
    Valued Forum Contributor Eastw00d's Avatar
    Join Date
    02-29-2020
    Location
    Breda, NL
    MS-Off Ver
    2016, 2019
    Posts
    808

    Re: Locking dates after Date

    I am not sure if you want the cells in Column H blocked before mentioned date or after, so in this code I assumed "Before":

    Please paste below macro in the code module of the worksheet ( to open by RightClick on the worksheet tab "Amort", and then click on "View Code" )
    Please Login or Register  to view this content.
    Cheers
    Erwin
    Attached Files Attached Files
    I started learning VBA because I was lazy ...
    Still developing.... being more lazy...

  4. #4
    Forum Contributor
    Join Date
    12-30-2021
    Location
    Mount Gambier, Australia
    MS-Off Ver
    19
    Posts
    106

    Re: Locking dates after Date

    Quote Originally Posted by Kaper View Post
    Are you sure it is the right file (or the right explanation in the post)?
    Columns you mention in text seem to be different than in a file.
    Hi Kaper

    Yes in Tab Amort. however I did make a mistake thank you cell reference A25 should be B25

    I want the cell H25 to lock after the date in cell B25 passes the date in cell C1 and so on.

  5. #5
    Forum Contributor
    Join Date
    12-30-2021
    Location
    Mount Gambier, Australia
    MS-Off Ver
    19
    Posts
    106

    Re: Locking dates after Date

    Quote Originally Posted by Eastw00d View Post
    I am not sure if you want the cells in Column H blocked before mentioned date or after, so in this code I assumed "Before":

    Please paste below macro in the code module of the worksheet ( to open by RightClick on the worksheet tab "Amort", and then click on "View Code" )
    Please Login or Register  to view this content.
    Cheers
    Erwin
    Thanks Erwin.

    That was very close. I need cell to lock after the date so entries can only be made on or before the date in column B.
    That way entires can be made to see what happens after a date and not before

  6. #6
    Valued Forum Contributor Eastw00d's Avatar
    Join Date
    02-29-2020
    Location
    Breda, NL
    MS-Off Ver
    2016, 2019
    Posts
    808

    Re: Locking dates after Date

    Ok, so you need to change the range-statement line into these two:
    Please Login or Register  to view this content.
    Cheers
    Erwin

  7. #7
    Forum Contributor
    Join Date
    12-30-2021
    Location
    Mount Gambier, Australia
    MS-Off Ver
    19
    Posts
    106

    Re: Locking dates after Date

    Hi Erwin

    like this?

    Please Login or Register  to view this content.


    doesn't seem to have changed anything (probably me)
    Last edited by AliGW; 01-03-2022 at 06:09 AM. Reason: Code tags added.

  8. #8
    Valued Forum Contributor Eastw00d's Avatar
    Join Date
    02-29-2020
    Location
    Breda, NL
    MS-Off Ver
    2016, 2019
    Posts
    808

    Re: Locking dates after Date

    Yes,
    the code in this way unlocks all the cells before/above found date, and locks all cells below/after found date. (in the first code it was just the opposite)
    But maybe you need first to unlock all your cells in column H first before trying, and please keep the rules of this forum by embedding you code into code-tags (it's the "hashtag" mark in the editing options above your writing.)

    Cheers
    Erwin

  9. #9
    Forum Contributor
    Join Date
    12-30-2021
    Location
    Mount Gambier, Australia
    MS-Off Ver
    19
    Posts
    106

    Re: Locking dates after Date

    Hi Erwin

    I don't see the option to post code as you said. Maybe I'm to new.

    Basically once the date in C1 passes the date in column B passes I want to lock the corresponding cell in Column H and the ones above it.
    Can't change the past only the future

  10. #10
    Valued Forum Contributor Eastw00d's Avatar
    Join Date
    02-29-2020
    Location
    Breda, NL
    MS-Off Ver
    2016, 2019
    Posts
    808

    Re: Locking dates after Date

    Well, for me this works, please see attachment

    Cheers
    Erwin
    Attached Files Attached Files

  11. #11
    Forum Guru Kaper's Avatar
    Join Date
    12-14-2013
    Location
    Warsaw, Poland
    MS-Off Ver
    most often 365, but still time to time use also older versions
    Posts
    8,154

    Re: Locking dates after Date

    The merit part is handled by Eastw00d, so let me just show (screenshot attached) how to wrap CODE tags around the code to obtain such a result:
    Please Login or Register  to view this content.
    Attached Images Attached Images

  12. #12
    Forum Contributor
    Join Date
    12-30-2021
    Location
    Mount Gambier, Australia
    MS-Off Ver
    19
    Posts
    106

    Re: Locking dates after Date

    Thanks Erwin

    I however need the opposite please

    Sorry but I don't understand how this works. Itried changing true to false and false to true but nothing seems to change

  13. #13
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Ipswich, England
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel (Windows 11 64-bit)
    Posts
    62,138

    Re: Locking dates after Date

    Administrative Note:

    Welcome to the forum.

    We would very much like to help you with your query, however you need to include code tags around your code.

    Please take a moment to add the tags. Posting code between [code]Please [url=https://www.excelforum.com/login.php]Login or Register [/url] to view this content.[/code] tags makes your code much easier to read and copy for testing, and it also maintains VBA formatting.

    Please see Forum Rule #2 about code tags and adjust accordingly. Click on Edit to open your post, then highlight your code and click the # icon at the top of your post window. More information about these and other tags can be found here

    (Note: this change is not optional. As you are quite new here, I have done it for you this time.)
    Ali


    Enthusiastic self-taught user of MS Excel who's always learning!
    Don't forget to say "thank you" to anyone who has offered you help in your thread. You can reward them by clicking on * Add Reputation below theur user name on the left, if you wish.


    Forum Rules (updated September 2018): please read them here.
    How to use the Power Query code you've been given: help here. More about the Power suite here.

  14. #14
    Forum Contributor
    Join Date
    12-30-2021
    Location
    Mount Gambier, Australia
    MS-Off Ver
    19
    Posts
    106

    Re: Locking dates after Date

    yes Ali Kaper just showed me how. Thank you for doing it for me this time

  15. #15
    Valued Forum Contributor Eastw00d's Avatar
    Join Date
    02-29-2020
    Location
    Breda, NL
    MS-Off Ver
    2016, 2019
    Posts
    808

    Re: Locking dates after Date

    Quote Originally Posted by wildecoyote1966 View Post
    Sorry but I don't understand how this works. Itried changing true to false and false to true but nothing seems to change
    So please upload the file where you tried changing this, I will look into this one.
    cheers
    Erwin

  16. #16
    Forum Contributor
    Join Date
    12-30-2021
    Location
    Mount Gambier, Australia
    MS-Off Ver
    19
    Posts
    106

    Re: Locking dates after Date

    Please Login or Register  to view this content.
    Blocked everything from H38 down

    and
    Please Login or Register  to view this content.
    made no difference to the first change

    then I

    Please Login or Register  to view this content.
    removed NOT from If Not Intersect(Target, [c1]) Is Nothing Then

    and I think it's working.
    Last edited by wildecoyote1966; 01-03-2022 at 08:46 PM. Reason: adding additional information

+ 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. Locking Column that has auto-populating dates from code
    By junglej815 in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 06-02-2021, 09:19 PM
  2. Vba code required for locking columns contains dates
    By send2carthik in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 05-11-2018, 09:38 AM
  3. [SOLVED] Locking Cell After Specified Date
    By rwatson in forum Excel Programming / VBA / Macros
    Replies: 12
    Last Post: 07-01-2012, 09:47 AM
  4. [SOLVED] Excel 2007 : GENERAL: Locking the formatting of dates in a column
    By banvir1 in forum Excel General
    Replies: 3
    Last Post: 05-02-2012, 02:54 PM
  5. Locking Date
    By stevencohen in forum Excel General
    Replies: 5
    Last Post: 11-10-2011, 06:51 AM
  6. Locking Date after using =Now()
    By gsandy in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 04-04-2011, 06:46 PM
  7. Locking Date
    By Juran in forum Excel General
    Replies: 1
    Last Post: 11-09-2006, 10:14 PM

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