Display message based on cell value

[Caitlin535]

Hello -

My worksheet has a set up in which users essentially put a value in column A, a value in column B, and then Column C = B-A. Each time a new value is calculated in column C, I want a message box to pop up - I need to it give one message if the value is < 8.0, and another message if the value is > 8.0 (don't need a message for 8.0 exactly).

I've been trying to do this with Data Validation, but it doesn't seem to want to work - I can't get a message to pop up at all, which is frustrating. I also tried the following code:

Private Sub Worksheet_Change(ByVal Target As Range)
Dim A As Range, r As Range
Set A = Range("C3:C16")
If Intersect(Target, A) Is Nothing Then Exit Sub
For Each r In Target
If r.Value < 8.0 Then
MsgBox "You have indicated working fewer than 8.0 hours on this day. Please indicate how to account for hours." & r.Address
End If
Next r
End Sub

But it doesn't work either - AND, it would be my second Worksheet_Change entry, and I know only one is allowed per sheet (and combining more than one together seems beyond me!).

Any advice?

Thank you so so much!

 

[Michael M]

Maybe this UNTESTED

Private Sub Worksheet_Change(ByVal Target As Range)
Dim A As Range, r As Range
Set A = Range("B3:B16")
If Intersect(Target, A) Is Nothing Then Exit Sub
For Each r In A
If Target.Offset(, 1).Value < 8 Then
MsgBox "You have indicated working fewer than 8.0 hours on this day. Please indicate how to account for hours. " & "See Row " & Target.Row
Exit Sub
ElseIf Target.Offset(, 1).Value > 8 Then
MsgBox "You have indicated working MORE than 8.0 hours on this day. Please indicate how to account for hours. " & "See Row " & Target.Row
Exit Sub
End If
Next r
End Sub

 

[My Aswer Is This]

Try this:

Script runs when you enter a value in column B in the range you indicated.

Private Sub Worksheet_Change(ByVal Target As Range)
'Modified  10/8/2019  12:49:56 AM  EDT
If Target.Cells.CountLarge > 1 Or IsEmpty(Target) Then Exit Sub
If Not Intersect(Target, Range("B3:B16")) Is Nothing And Target.Offset(, 1).Value < 8 Then
    MsgBox "You have indicated working fewer than 8.0 hours on this day." _
    & vbNewLine & "Please indicate how to account for hours." & vbNewLine & "Found here  " & Target.Offset(, 1).Address
End If
If Not Intersect(Target, Range("B3:B16")) Is Nothing And Target.Offset(, 1).Value > 8 Then
    MsgBox "You have indicated working more than 8.0 hours on this day." _
    & vbNewLine & "Please indicate how to account for hours." & vbNewLine & "Found here  " & Target.Offset(, 1).Address
End If
End Sub

 

[Caitlin535]

Thank you to you both for your replies - unfortunately, though, neither script seems to work! I got rid of my other Worksheet_Change script (even if I get a working MsgBox script, I'll then have to figure out how to use it in addition to the Worksheet_Change script I already have) and tried both, one after the other, but got nothing

I'm attaching my actual worksheet, just for reference. Technically the Target column is actually column I; I did change that before trying each script, just FYI.

Am I doing something wrong?

 

[My Aswer Is This]

We need to know what column you plan to enter the last value into.

In your original post that would be Column B

Not sure why you would originally say A and B but now your saying column I

So tell us what to columns you plan to enter your values into and in what column you expect to see 8 in.

 

[Caitlin535]

Whoops - didn't actually realize I couldn't attach a worksheet anymore. Still, it's as simple as:

[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD="align: center"][/TD]
[TD="align: center"]A[/TD]
[TD="align: center"]B[/TD]
[TD="align: center"]C[/TD]
[TD="align: center"]D[/TD]
[/TR]
[TR]
[TD="align: center"]1[/TD]
[TD="align: center"]Start Time[/TD]
[TD="align: center"]End Time[/TD]
[TD="align: center"]Total Hours[/TD]
[TD="align: center"]Explanation[/TD]
[/TR]
[TR]
[TD="align: center"]2[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"]=B2-A2[/TD]
[TD="align: center"][/TD]
[/TR]
[TR]
[TD="align: center"]3[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"]=B3-A3[/TD]
[TD="align: center"][/TD]
[/TR]
[TR]
[TD="align: center"]4[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"]=B4-A4[/TD]
[TD="align: center"][/TD]
[/TR]
</tbody>[/TABLE]

When the formula in Column C above equals <8 I want a particular message to pop up, and when it equals >8 I want another message to pop up. I'm not sure why the two scripts you suggested aren't working - thoughts?

Thank you so much -
Caitlin

 

[My Aswer Is This]

The script I provided requires you to manually enter a value in Column B to make the script run

So you need to enter a value in column A then a value into column B and then the script runs.

The script does not run just because the value in column 3 changes.
The script runs when you manually enter a value in column B

Remove all other change event scripts before trying this.

If you get this working we will show you how to also add other auto sheet event scripts you may want in the same sheet.

 

[Caitlin535]

I understand - in attempting to simplify my question (by referring to columns A, B, and C instead of the actual columns in my worksheet) I ended up making things impossible for myself! The explanation you gave referencing the mini-chart I entered makes perfect sense, and I thank you so much. Will give it a try in my spreadsheet this afternoon . . .

Thank you again!

 

[Caitlin535]

Of course, now that I understand my silly and very basic mistake, both scripts run perfectly. THANK YOU!

HOWEVER, I'm now hoping I can impose upon you one last time: As I mentioned, I have another Worksheet_change VBA script that I also need to be able to use. Is there a way to combine the two scripts so that they both run? My existing Worksheet_change script is here:

Private Sub Worksheet_Change(ByVal Target As Range)On Error GoTo Handler
If Target.CountLarge > 1 Then Exit Sub
If Target.Column = 5 And Target.Value = "Arrived" Then
Application.EnableEvents = False
Target.Offset(0, 1) = Format(Now(), "hh:mm")
Target.Offset(0, 12) = Format(Now(), "mm-dd-yyyy hh:mm")
Application.EnableEvents = True
End If


If Target.Column = 7 And Target.Value = "Departed" Then
Application.EnableEvents = False
Target.Offset(0, 1) = Format(Now(), "hh:mm")
Target.Offset(0, 11) = Format(Now(), "mm-dd-yyyy hh:mm")
Application.EnableEvents = True
End If


Handler:
Application.EnableEvents = True


End Sub

 

[My Aswer Is This]

Try this:

Private Sub Worksheet_Change(ByVal Target As Range)
'Modified  10/10/2019  2:19:44 AM  EDT
On Error GoTo M
Application.EnableEvents = False
If Target.CountLarge > 1 Then Exit Sub
If Target.Cells.CountLarge > 1 Or IsEmpty(Target) Then Exit Sub
If Not Intersect(Target, Range("B3:B16")) Is Nothing And Target.Offset(, 1).Value < 8 Then
    MsgBox "You have indicated working fewer than 8.0 hours on this day." _
    & vbNewLine & "Please indicate how to account for hours." & vbNewLine & "Found here  " & Target.Offset(, 1).Address
End If
If Not Intersect(Target, Range("B3:B16")) Is Nothing And Target.Offset(, 1).Value > 8 Then
    MsgBox "You have indicated working more than 8.0 hours on this day." _
    & vbNewLine & "Please indicate how to account for hours." & vbNewLine & "Found here  " & Target.Offset(, 1).Address
End If
'Part Two
If Target.Column = 5 And Target.Value = "Arrived" Then
    Target.Offset(0, 1) = Format(Now(), "hh:mm")
    Target.Offset(0, 12) = Format(Now(), "mm-dd-yyyy hh:mm")
End If

If Target.Column = 7 And Target.Value = "Departed" Then
    Target.Offset(0, 1) = Format(Now(), "hh:mm")
    Target.Offset(0, 11) = Format(Now(), "mm-dd-yyyy hh:mm")
End If
Application.EnableEvents = True
Exit Sub
M:
Application.EnableEvents = True
MsgBox "We had a Problem"
End Sub