Code to change posted year to current year not working .

  • I have code to; prevent deletion/modification in certain cells, remind a user to make an entry, prevent entry of certain text, & convert text in a column to proper case, The code is located under

    Code
    1. [FONT=Calibri][size=12]Private Sub Worksheet_Change(ByVal Target As Range)[/SIZE][/FONT]


    The code to do the various tasks mentioned above does work, but the code to change the year, does not work.


    I can't see anything wrong, but I am hoping that the group will find the problem.

  • Hello,


    In order to understand your objective and test your workbook macro ... attaching a sample file would help a lot ...
    Just one sheet with the basic data ...


    If you feel like saying "Thank You" for the help received, do not hesitate to click the "Like" icon ...just below...in the bottom right corner...:)

  • Sorry ... but seems to be some incompatibility with my configuration which is preventing download ...


    Could you further streamline your sample file to the bare minimum ...

    If you feel like saying "Thank You" for the help received, do not hesitate to click the "Like" icon ...just below...in the bottom right corner...:)

  • Thanks for your latest sample file ...


    At least ... managed to download it ...


    As soon as I have a moment will take a look at ... your 124 line Event macro ...

    If you feel like saying "Thank You" for the help received, do not hesitate to click the "Like" icon ...just below...in the bottom right corner...:)

  • Absolutely ... all you other ... multiple instructions do remain identical ... :wink:


    Have you had a chance to test inputs in cell G19 ...???

    If you feel like saying "Thank You" for the help received, do not hesitate to click the "Like" icon ...just below...in the bottom right corner...:)

  • [USER="31712"]Carim[/USER] I just tested the code & it does run, & the only problem is that it also changes the month. i.e.. The cell was 10/1/2000. I changed the 1 to 1, & the code changed it to 11/11/2019.

  • The Event macro is using MONTH(G20) ... i.e. the month of the date which appears in cell G20 ...


    Should you need to forecast Next meeting for following month, you can have : MONTH(G20)+1


    Hope this will help

    If you feel like saying "Thank You" for the help received, do not hesitate to click the "Like" icon ...just below...in the bottom right corner...:)

  • The only thing that changes is the day & the year. Each week of the month has a worksheet, the month wont change; because the next month appointment is on a different sheet with that month already entered. The day is manually entered, because there is no set day.

  • Fair enough ...


    So from where ... should the macro get :


    1. The Year


    2. The Month


    since the Day comes from the manual input in cell G19 ...

    If you feel like saying "Thank You" for the help received, do not hesitate to click the "Like" icon ...just below...in the bottom right corner...:)

  • [USER="31712"]Carim[/USER] We could get the month from cell A18.it will have the month for the worksheet. The cell will protected & the font color will be the same as the background. I don't think, that the year will be a problem. Initially the year is 2000, & will change when the formula runs. That is what is supposed to do. What do you think?

  • What you are suggesting is no problem ...


    Can you handle these two modifications by yourself ... or do you need a helping hand ...

    If you feel like saying "Thank You" for the help received, do not hesitate to click the "Like" icon ...just below...in the bottom right corner...:)

  • I thought I could do the modifications. I was wrong, I really will need a hand. Would you be kind enough to assist me. The month would be static & located in A18. The only thing that would change is the year.

  • Currently the month, day & year is located in G19. I was thinking that the static date could be located in A18 & possibly the day & year could still be located in G19. The only entry would be the day, & initially when the cell was set up it had the month/day year as 10/1/2000. FYI. prior to closing the sample, save it. You will have to use task manager to close, because the code to close the app was deleted, when I made the sample.

  • As you said currently your reference date is located in cell G20 ... and your event macro looks as follows :



    Should you decide to use cell A18 and get rid of cell G20 ... the only modification required in the code would be to replace G20 by A18 ...


    Hope this clarifies

    If you feel like saying "Thank You" for the help received, do not hesitate to click the "Like" icon ...just below...in the bottom right corner...:)