Pop up message when numbers 1, 2, 3 or 4 are entered into entire columns AI through AV

[discoveringexcel]

I have a people list for my company that I work for. We want to keep track of training for each individual associate.

We have columns AI through AV starting on row 3 set up with our different lines. We rate their level of training 1-4. So we want a brief message to pop up when one of the cells containing 1-4 is selected. We want the formula to be absolute for those columns AI through AV and all rows 3 and below.

So if I were to enter the number 1 into cell AS100 a message would pop up saying "Fully trained in that area and capable of working independently if needed."
and the same thing for 2, 3 and 4 but with different messages.

Any suggestions?

Thanks in advance!

 

[Trebor76]

Hi discoveringexcel,

Welcome to MrExcel!!

Try this event macro* on the sheet in question:

Option Explicit
Private Sub Worksheet_Change(ByVal Target As Range)

    'If the change is within columns AI to AV (inclusive) then...
    If Target.Column >= 35 And Target.Column <= 48 Then
        '...if the change was from row 3 of those columns then:
        If Target.Row >= 3 Then
            With Application
                .ScreenUpdating = False
                .EnableEvents = False
                'Display a message in-line with the number entered
                Select Case Val(Target.Value)
                    Case Is = 1
                        MsgBox "Fully trained in that area and capable of working independently if needed."
                    Case Is = 2
                        MsgBox "Message for 2"
                    Case Is = 3
                        MsgBox "Message for 3"
                    Case Is = 4
                        MsgBox "Message for 4"
                End Select
                .EnableEvents = True
                .ScreenUpdating = True
            End With
        End If
    End If
    
End Sub

Regards,

Robert

* To install this macro copy it (Ctrl + C) and then right-click on the tab you want it run from and from the shortcut menu select View Code and the paste it (Crtl + V) to the module

 

[discoveringexcel]

Is there a way to have the message pop up every time the cell is selected? So the information is available after the initial data was entered?

 

[discoveringexcel]

Is there a way to have the message pop up every time the cell is selected? So the information is available after the initial data was entered?

I know with the data validation option when the cell is selected, it will show the message. but there isn't enough room for all 4 levels.

 

[Trebor76]

You will need to use another event. To save duplication put this macro into a standard workbook module...

Option Explicit
Sub MessageDisplay(lngMyNum As Long)

    Select Case Val(lngMyNum)
        Case Is = 1
            MsgBox "Fully trained in that area and capable of working independently if needed."
        Case Is = 2
            MsgBox "Message for 2"
        Case Is = 3
            MsgBox "Message for 3"
        Case Is = 4
            MsgBox "Message for 4"
    End Select

End Sub

...and then these event macros on the sheet in question:

Option Explicit
Private Sub Worksheet_Change(ByVal Target As Range)
    
    If Target.Column >= 35 And Target.Column <= 48 And Target.Row >= 3 Then
        If Val(Target.Value) = 1 Or Val(Target.Value) = 2 Or Val(Target.Value) = 3 Or Val(Target.Value) = 4 Then
            Application.EnableEvents = False
                Call MessageDisplay(Val(Target.Value))
            Application.EnableEvents = True
        End If
    End If
    
End Sub
Private Sub Worksheet_SelectionChange(ByVal Target As Range)

    If Target.Column >= 35 And Target.Column <= 48 And Target.Row >= 3 Then
        If Val(Target.Value) = 1 Or Val(Target.Value) = 2 Or Val(Target.Value) = 3 Or Val(Target.Value) = 4 Then
            Application.EnableEvents = False
                Call MessageDisplay(Val(Target.Value))
            Application.EnableEvents = True
        End If
    End If

End Sub

Robert