VBA Pop up message

[jbsin89]

Hi all,

Hope someone will be able to help. I am below beginner with VBA and what I'm trying to do is starting to frustrate.

I have a form for a customer to complete. When a number < 5 is entered into a specific cell I need a pop up to come up saying "Please provide key improvement"

This would then would need to be applied down the table (AF20:AF30)

Is someone able to point me in the right direction?

Thanks

J

 

[louisH]

Hi.

Right click on your sheet tab and click view code.
In the vba editor that appears, paste this code :

Private Sub Worksheet_Change(ByVal Target As Range)

    On Error Resume Next
    Dim rng As Range
    Set rng = Intersect(Target, Range("AF20:AF30"))
    
    If rng Is Nothing Then Exit Sub
    On Error GoTo 0
    
    Dim c As Range
    
    For Each c In rng
        
        If c.Value < 5 Then
            MsgBox "Cell " & c.Address & " : Please provide key improvement."
        End If
        
    Next c

End Sub

 

[My Aswer Is This]

Are you saying if a value less then 5 is entered in Range("

AF20:AF30") then you want the message box to pop up?

If this is not what you want then please explain more please.

You used the term Form and Table so I may be confused.


​

 

[jbsin89]

Life save thank you!

 

[My Aswer Is This]

Try this:

Private Sub Worksheet_Change(ByVal Target As Range)
If Not Intersect(Target, Range("AF20:AF30")) Is Nothing Then
If Target.Cells.Count > 1 Or IsEmpty(Target) Then Exit Sub
If Target.Value < 5 Then MsgBox "Cell " & Target.Address & " : Please provide key improvement."
End If
End Sub