Input Message Varies According to Cell Contents?

[BSWJON]

Hi all, I am trying to achieve something straightforward but am unsure of whether Excel has the functionality.

I have a training database which simply has dates in cells corresponding to when people are trained. What I need Excel to do is show an input message when the date cell is selected which shows a retraining date that is three years on from the date in the cell. It's obvious data but something our ISO9001 auditors wanted the database to show.

So selecting 1.2.2019 would generate a message saying words to the effect of "training due 1.2.2022"

As you can see, the cells are sandwiched together so having additional columns or rows to display this info would be problematic- plus adding new staff or courses would require more database upkeep than the administrator using excel would be capable of! Plus there is conditional formatting applied to the table range which would get messy with additional rows/columns being inserted which dont want the same formatting.

As an aside- whenever I insert new rows or columns into the existing range specified within the conditional formatting, the existing conditional formatting isn't being applied to the new data- why would this be?

TIA; any help would be appreciated!

Person Name	Course 1	Course 2
Mathman McGee	1.2.2019	3.6.2018
Freddy Formulas	26.6.2020	13.12.2015

 

[BSWJON]

Hi Sykes,

Thanks for this!

So, some progress. The code worked for a new date I put in, which is a promising step- however the date in the note was the year 1902 and the note was not added for any of the existing dates. Out of interest, what format should the dates be entered? dd/mm/yyyy I presume?

When I entered another test date, it opened the debug prompt.

When I added a 2020 date, I got this:

Hitting debug showed this:

At some point I also got this but can't remember what caused it.

 

[BSWJON]

I just took another look at this- the note added specifies training due on 30.12.1902 at a time which corresponds to the cell contents- so I put a dummy date in of 2.3.15 and the note says training due 30.12.1902 at 02:03:15.

Also, the undo function ceases to work and the conditional formatting is lost... I'm beginning to wonder whether this is too complicated?

 

[sykes]

I think there are 2 things to be sure of:
1. Cell formatting
2. Method of date entry.

You need to be sure the cells are formatted for "Date" before data entry; doesn't matter what format you choose (you can still use 12.2.20 style is you wish), as long as they're formatted for "Date."
You need to use an entry method which Excel recognises. If you type in "12.2.20" Excel will display this, but read the cell value as something totally different.
Typing in "12/2/20" will allow Excel to recognise this as a date; it will still display the date in the format you've specified, but it'll treat the cell contents as a proper date, and all will be well.
It's working for me, so if you try the two steps I've mentioned - in an empty cell - it should work correctly for you.
Personally, I don't use the 12.2.20 style format, because internationally, it's very ambiguous, and has caused all manner of problems over the years.

Also, the undo function ceases to work and the conditional formatting is lost... I'm beginning to wonder whether this is too complicated?

The undo function never works on items changed with the use of VBA. That's why I said in post#2:

NB - test on COPY of your workbook first!

...and you never mentioned that you had conditional formatting.

I suggest looking at the great "XL2BB" addin which is available on this site (see the link in my signature).
It's easy to install, and allows you to easily post sections of your work into your posts, along with formatting, formulae, and other clever stuff.
As well as being easy for you to use, helpers can copy & paste it straight into their own platforms, making it quicker & easier to work on your project - and without the need for guesswork.

If I'd had sight of the sheet with XL2BB, I would have seen that you already have a whole host of dates which need altering; we therefore need to run a one-off piece of code to take all the existing dates, and get them into the correct format.
What date style are you using - is it dd.mm.yy, or mm.dd.yy?
I'm currently writing some code to re-format the existing dates, into ones which will allow the comments to be inserted, and also insert the comments.

 

[BSWJON]

Thanks for that- entering the dates in dd/mm/yy rather than dd.mm.yy makes all the difference and retains the conditional formatting- so overall, a good result- thank you!

Not to worry about the undo function, it was just an observation and in any case the contents and note can be deleted. I've been developing all of this in a copy of the workbook so no harm done in any case.

That addin sounds like a good shout as I have another macro that I am recieving help from too but that one is being more of a pickle.

If it is a hassle, don't worry about writing the new code- I can re-enter the dates manually, it'll take time but less than you have on making the codes work for me so far.

Thanks again. I will continue to test it and see if it throws up any niggles.

 

[sykes]

Now that we know the correct entry method, and bearing in mind that I already wrote the code earlier today, can you give it a try?
It's just to test on ONE cell at a time - on a few of your cells which already have the dates in them from before (not the new ones with comments).
You'll need to select the cell in question first (to make it the active cell), then run the code, by clicking into the body of the code and pressing F5.
It SHOULD convert the cell contents to an actual date, re-format the cell for m.d.yy (as I think you want it), and then the sheet_change event should kick-in, and add the note.
Try it on a few of the cells with different dates, and if it does what I think, we can finish off by writing a script to go through all the cells on that sheet, and convert the "Old" ones to new dates, with 3 yr expiry notes.

Sub frmt_dates()
Dim mnth As Integer
Dim dy As Integer
Dim cntr As Integer
Dim txtlen As Integer
Dim yr As Integer

    With ActiveCell
    
        txtlen = Len(.Text) - 3
        cntr = InStr(1, .Text, ".")
        
        mnth = Left(.Value, cntr - 1)
        dy = Mid(.Text, cntr + 1, txtlen - cntr)
        yr = Right(.Value, 2)
        
        .Value = DateSerial(yr, mnth, dy)
        .NumberFormat = "m.d.yy"
    End With
End Sub