bbqsmokeman
New Member
Hello
I have a workbook with multiple tabs (all named) that I need to know if there is a simple way via macro to launch a pop up if specific cells hit a threshold (pop up only the one time) and then pop up again at certain intervals (increased value by)
Right now G6, H6 and J6 hold the main value that is cumulative from column D entries
Example: when G6 hits total 7- would like a pop up to show message. Then not show a message till next value increase of 5 so the next pop up for G6 would be 12 and so on.
Same for H6 at value 4 then display pop up message and every increase of 2 then display again but no other times.
I have the following code that I thought would work but it does nothing.
I tried the simple version code:
but this code would display pop up every time which I didn't want to happen.
Ideally it would be great if all worksheets (person named sheets) would reference the code I am trying to put together opposed to setting each worksheet with the same code
I am supplying a template of the workbook
I have a workbook with multiple tabs (all named) that I need to know if there is a simple way via macro to launch a pop up if specific cells hit a threshold (pop up only the one time) and then pop up again at certain intervals (increased value by)
Right now G6, H6 and J6 hold the main value that is cumulative from column D entries
Example: when G6 hits total 7- would like a pop up to show message. Then not show a message till next value increase of 5 so the next pop up for G6 would be 12 and so on.
Same for H6 at value 4 then display pop up message and every increase of 2 then display again but no other times.
I have the following code that I thought would work but it does nothing.
Code:
Option Explicit
Private Sub Worksheet_Calculate()
Dim Rng1 As Range
Dim Rng2 As Range
Dim Rng3 As Range
Dim Value As Double
Dim Prompt As String
Dim Title As String
'Put the range you want to look at here
Set Rng1 = Range("G6")
'Put the target value here
Value = 7
'Put the message (prompt) of the message box (pop up) here
Prompt = "Threshold reaching concern level, please engage HR"
'Put the title of the message box (pop up) here
Title = "Sick Days"
If Rng1.Value = Value Then
MsgBox Prompt, vbInformation, Title
End If
'Put the range you want to look at here
Set Rng2 = Range("G6")
'Put the target value here
Value = 4
'Put the message (prompt) of the message box (pop up) here
Prompt = "Tardiness reaching concern level, please engage HR"
'Put the title of the message box (pop up) here
Title = "Lates"
If Rng2.Value = Value Then
MsgBox Prompt, vbInformation, Title
End If
'Put the range you want to look at here
Set Rng3 = Range("J6")
'Put the target value here
Value = 1
'Put the message (prompt) of the message box (pop up) here
Prompt = " Please engage HR"
'Put the title of the message box (pop up) here
Title = "Unauthorized Absences"
If Rng2.Value = Value Then
MsgBox Prompt, vbInformation, Title
End If
End Sub
I tried the simple version code:
Code:
Private Sub Worksheet_Calculate()
If Range("G6").Value > 0 Then MsgBox "required message here"
End Sub
Ideally it would be great if all worksheets (person named sheets) would reference the code I am trying to put together opposed to setting each worksheet with the same code
I am supplying a template of the workbook