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

 

[sykes]

One way would be to use VBA (not sure whether you're au fait with that?).
My suggestion is to put a comment (now called a "Note") into the cell which has been changed. This way, the user only needs to hover the cursor over the cell in question, and the new training date will be displayed.
This will work on new, clean cells - which so far don't have any notes in them, but will also insert a new date into an existing note, where an existing training date's being updated for a new one.
The code needs to be inserted into the sheet module of the sheet in question.
Also, the workbook needs to be saved as a "Macro-enabled Workbook" with the file extension ".xlsm" or other corresponding one, if you have an early version of Excel / Office.

Private Sub Worksheet_Change(ByVal Target As Range)

    With Target
        If .Column <> 2 Or .Row = 1 Then Exit Sub
        If .Comment Is Nothing Then
            .AddComment "Training due " & DateAdd("yyyy", 3, .Value)
        Else
            .Comment.Text ("Training due " & DateAdd("yyyy", 3, .Value))
        End If
    End With

End Sub

NB - test on COPY of your workbook first!

".Column <> 2" refers to column 2 i.e. col B. If you need it to refer to a different column, change the column number in the code.
If you need more than one column to be annotated, post back.
If you don't know how to place code into your project, or where to put it, post back.

 

[sykes]

Sorry - only just noticed that you've already specified at least one other column, so this is one way to add other columns - just add more code, thus:

Private Sub Worksheet_Change(ByVal Target As Range)

With Target
        If .Column <> 2 Or .Column <> 3 Or .Row = 1 Then Exit Sub
        If .Comment Is Nothing Then
            .AddComment "Training due " & DateAdd("yyyy", 3, .Value)
        Else
            .Comment.Text ("Training due " & DateAdd("yyyy", 3, .Value))
        End If
    End With

Also, suggest we get the main issue sorted first - not sure about the conditional formatting, but someone else may step-in...

 

[BSWJON]

Thanks Sykes, this makes sense. Is there a way to apply this to a table range rather than specify each column and row? I have 100 rows of data and columns up to AQ.

Will give this a go- I have done a few macro enabled workbooks before

 

[BSWJON]

Tried this by viewing VB code and pasting, changed column numbers and row numbers to reflect the data- however it says 'invalid outside procedure'?

 

[sykes]

OK. First let's try to get it working.
My second post didn't show the 1st & last lines in the VB code (due to me editing it!).
You need to ensure that the code is pasted into the WORKSHEET module of the sheet in question - not into any other module.
In the left hand pane (normally), you should see your project, then the Excel objects within it; ensure you've selected the correct worksheet module, by double-clicking it.
Now paste this whole section into the code pane on the right, but - if there's already anything inside the start & end lines:

Private Sub Worksheet_Change(ByVal Target As Range)

End Sub

... then we might need to amend it.
Otherwise, just ensure the whole lot reads thus:

Private Sub Worksheet_Change(ByVal Target As Range)

    With Target
        If .Column <> 2 Or .Column <> 3 Or .Row = 1 Then Exit Sub
        If .Comment Is Nothing Then
            .AddComment "Training due " & DateAdd("yyyy", 3, .Value)
        Else
            .Comment.Text ("Training due " & DateAdd("yyyy", 3, .Value))
        End If
    End With

End Sub

I suggest that you just test on a couple of columns for now - just to ensure it's working correctly.
It'll work on any row - apart from row 1, as I guessed this would be your header row.
Also, make sure the cells are formatted for dates, otherwise the code's trying to change dates which aren't dates in the first place!
The code will fire every time there's a sheet change, but stop again immediately, if the change was in row 1, or NOT in any of your specified columns.

 

[BSWJON]

Thanks for the help Sykes. This is how it looks:

It's not throwing up an error message, but when I click run, it opens the macros dialogue box which is currently empty:

And alas, the notes aren't appearing yet.

What shall I do next?

Thanks again and thank goodness we can C&P screenshots...

 

[sykes]

Hi

You don't run this code by using the "Run" command!
This is called an event code. It's built-in to Excel, and designed to automatically run when a specific "event" occurs.
In this case we're using the "Worksheet Change" event. If you look at the dropdown boxes at the top of the right-hand pane in your first screenshot, you'll see that "Worksheet" and "Change" are both selected.
If you select another - like "Activate" from the RH dropdown, you'll see that automatically it starts another bit of code for you - in this case the worksheet_activate code. Any code you write in here, is automatically run each time the worksheet's activated.
If you put the following in there, then go back to your workbook, select a different sheet, then select the original sheet again, you should see the message:

Private Sub Worksheet_Activate()
    MsgBox "Hi - this is the worksheet_activate code working automatically!"
End Sub

As you'll by now be aware, event code's extremely useful, because it enables us to automate so much in Excel - instead of having to push buttons, or use the run command. etc. Have a good look at all the events available - there are loads - and not just for sheets - there are a load for the "Workbook" too - for example the Workbook_Open event - which (as the name suggests) will run each time someone opens the workbook.

Going back to my original code, the worksheet_change event is used, to fire code each time a "Change" is made to that particular worksheet - which is why it's important to only have the code residing in the worksheet in question.
It only runs if a user makes a change to the sheet, then comes out of the cell in question (normally by using the Return key, of course). For example, changing cell E5 from a 1 to a 2.
Normally we set it to test if the sheet change has occurred in a particular cell, or range of cells, and only then, to run. If the change has been made to any other cell or range outside our specified area, then the code still begins to run, but as soon as it tests for our range, and finds the change has occurred somewhere else, it exits the procedure - so the user doesn't notice anything happening in the background at all.

Hopefully, by now, if I've explained sufficiently clearly, you'll realise that our code will only run when your user makes a change to any cell in cols 2 or 3 (B or C) and also only if the row is greater than row 1; this is because we don't want any changes to be made in a row that's most likely being used for headers etc. As I don't know your specific setup, I'm just guessing here - to give you some ideas of how you might wish to adapt the code for your particular scenario.

I see that you've adapted the columns in my original code to 7 & 8, so if you go back to that worksheet, and make a change to a cell in either of those columns (row 4 onwards, as I see you've specified not row 3) then press "Return" or just select somewhere else on the sheet, you should find the code then fires.

Fingers crossed...!

Please let us know how you get on.

 

[BSWJON]

Haha sorry yeah I just hoped it might 'bump start' the command!

So I C&P'd your test code and lo and behold, it worked! Which is quite cool. Thanks for the guidance on this- it will be very useful in the future.

The main code we're looking at though, still does not appear to be working? To confirm we're looking for a note to appear when we hover over any date value in the range F3:AQ97 however at present the code is only applying to row 3, columns 7 and 8 (which are now G & H as I've removed the first two redundant columns). The code should fire when changes are made to columns 2 and 3 (B and C) although in use it should be whenever a change is made pretty much anywhere as it needs to be constantly up to date.

Sorry to be a pain but what might I have missed at this point?

 

[sykes]

Great! - I wanted to get it working properly, before we did anything else.

I've already been working on a bit of code, in the hope we'd get over the previous hump...

Try this next tranche.
Completely replace the old code with the new - just leaving the first & last lines i.e. Private Sub... and End Sub.

I strongly suggest testing on a COPY of your work, as we often don't see the whole picture of someone's project, and it's always possible that VBA can affect something in a different part of the workbook, that we don't know about.
Just to be sure of my understanding...
This will affect ANY cell in the range F3:AQ97 which is changed - not just those containing dates.
If you only want it to work on those containing dates, then we need to do some more work. As long as the whole of this range is to hold the training dates, then we're fine.

Private Sub Worksheet_Change(ByVal Target As Range)

    With Target
        Select Case .Column
            Case 5 To 43
                If .Row < 3 Or .Row > 97 Then Exit Sub
                If .Comment Is Nothing Then
                    .AddComment "Training due " & DateAdd("yyyy", 3, .Value)
                Else
                    .Comment.Text ("Training due " & DateAdd("yyyy", 3, .Value))
                End If
            Case Else
                Exit Sub
        End Select
    End With

End Sub

Please report back, Ta.