Forum Discussion

AnnaS550's avatar
AnnaS550
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 generates a different message...

My problem is that this message seems to pop up for every cell in that same row afterwards. How can I change the code to get it to pop up only for that column?

 

I have spend enough time on this 🙈 and hope someone can help me to get forward.

Regards Anna

My code below:

 

Private Sub Worksheet_Change(ByVal Target As Range)

    Dim total As Double

    Application.Calculate

    total = Sheet1.Range("E" & Target.Row).Value

    If (total >= 1) And (total <= 5) Then

        Call MsgBox("Message 1-5.", vbOKOnly, "Evaluation")

    End If

   

     If (total >= 6) And (total <= 10) Then

        Call MsgBox("Message 6-10.", vbOKOnly, "Evaluation")

    End If

   

       If (total >= 11) And (total <= 15) Then

        Call MsgBox("Message 11-15.", vbOKOnly, "Evaluation")

    End If

   

End Sub

excel
Macros and VBA
  • HansVogelaar's avatar
    HansVogelaar
    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

     

4 Replies

  • HansVogelaar's avatar
    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's avatar
      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's avatar
        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

         

Resources