Display message based on cell value

Caitlin535

New Member
Joined
Jan 8, 2016
Messages
21
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!
 

Excel Facts

Highlight Duplicates
Home, Conditional Formatting, Highlight Cells, Duplicate records, OK to add pink formatting to any duplicates in selected range.
Maybe this UNTESTED

Code:
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
 
Upvote 0
Try this:

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

Code:
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
 
Upvote 0
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?
 
Upvote 0
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.
 
Upvote 0
Whoops - didn't actually realize I couldn't attach a worksheet anymore. Still, it's as simple as:

ABCD
1Start TimeEnd TimeTotal HoursExplanation
2=B2-A2
3=B3-A3
4=B4-A4

<tbody>
</tbody>

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
 
Upvote 0
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.
 
Upvote 0
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!
 
Upvote 0
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:

Code:
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
 
Upvote 0
Try this:
Code:
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
 
Upvote 0

Forum statistics

Threads
1,212,927
Messages
6,110,725
Members
448,294
Latest member
jmjmjmjmjmjm

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top