Oct 05 2021 12:25 AM
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 :see_no_evil_monkey: 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
Oct 05 2021 01:27 AM
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?
Oct 05 2021 04:22 AM
Oct 05 2021 04:52 AM - edited Oct 05 2021 09:07 AM
SolutionHere 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
Oct 05 2021 06:37 AM
Oct 05 2021 04:52 AM - edited Oct 05 2021 09:07 AM
SolutionHere 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