Forum Discussion

Copper Contributor

Oct 05, 2021

Solved

How to get a pop-up message to show only for the intended column.

Hi. I am working on a sheet where i have 3 different pop-up messages to show for certain values in cells in a certain column. E.g. a value between 1-5 generates one message, a value between 6-10 ge...

Oct 05, 2021

Here you go:

Private Sub Worksheet_Change(ByVal Target As Range)
    Dim total As Double
    If Target.CountLarge > 1 Then Exit Sub
    If Not Intersect(Range("C:D"), Target) Is Nothing Then
        Application.Calculate
        Application.EnableEvents = False
        total = Sheet1.Range("E" & Target.Row).Value
        If total >= 1 And total <= 5 Then
            Call MsgBox("Message 1-5.", vbOKOnly, "Evaluation")
        ElseIf total <= 10 Then
            Call MsgBox("Message 6-10.", vbOKOnly, "Evaluation")
        ElseIf total <= 15 Then
            Call MsgBox("Message 11-15.", vbOKOnly, "Evaluation")
        End If
        Application.EnableEvents = True
    End If
End Sub

HansVogelaar

MVP

Oct 05, 2021

AnnaS550

Is column E the column you want to look at? If not, which one?

And does this column contain a formula or is it filled in by the user?

AnnaS550

Copper Contributor

Oct 05, 2021

Thank you Hans for quick response,
Yes the column I want to look at is E and it contains the formula (=Cx*Dx) with x being the row...
So column C and D is filled by the user and column E is meant to calculate the product of the factors in C/D.
/ Anna

HansVogelaar

MVP

Oct 05, 2021

AnnaS550

Here you go:

Private Sub Worksheet_Change(ByVal Target As Range)
    Dim total As Double
    If Target.CountLarge > 1 Then Exit Sub
    If Not Intersect(Range("C:D"), Target) Is Nothing Then
        Application.Calculate
        Application.EnableEvents = False
        total = Sheet1.Range("E" & Target.Row).Value
        If total >= 1 And total <= 5 Then
            Call MsgBox("Message 1-5.", vbOKOnly, "Evaluation")
        ElseIf total <= 10 Then
            Call MsgBox("Message 6-10.", vbOKOnly, "Evaluation")
        ElseIf total <= 15 Then
            Call MsgBox("Message 11-15.", vbOKOnly, "Evaluation")
        End If
        Application.EnableEvents = True
    End If
End Sub

AnnaS550
Copper Contributor
Oct 05, 2021
Yes! Thank you so much for your help. Much appreciated!
/ Anna