Results 1 to 5 of 5

Thread: Pop up message when numbers 1, 2, 3 or 4 are entered into entire columns AI through AV
Thanks Thanks: 0 Likes Likes: 0

  1. #1
    New Member
    Join Date
    Dec 2018
    Posts
    3
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

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

    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!

  2. #2
    Board Regular
    Join Date
    Jul 2007
    Location
    Sydney
    Posts
    4,392
    Post Thanks / Like
    Mentioned
    7 Post(s)
    Tagged
    1 Thread(s)

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

    Hi discoveringexcel,

    Welcome to MrExcel!!

    Try this event macro* on the sheet in question:

    Code:
    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

  3. #3
    New Member
    Join Date
    Dec 2018
    Posts
    3
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

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

    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?

  4. #4
    New Member
    Join Date
    Dec 2018
    Posts
    3
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

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

    Quote Originally Posted by discoveringexcel View Post
    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.

  5. #5
    Board Regular
    Join Date
    Jul 2007
    Location
    Sydney
    Posts
    4,392
    Post Thanks / Like
    Mentioned
    7 Post(s)
    Tagged
    1 Thread(s)

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

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

    Code:
    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:

    Code:
    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

Some videos you may like

User Tag List

Tags for this Thread

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •