Macro for pop up message

[bbqsmokeman]

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.

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:

Private Sub Worksheet_Calculate()
If Range("G6").Value > 0 Then MsgBox "required message here"
End Sub

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

 

[p45cal]

Try the attached. I can't test easily because there are links to workbooks I don't have.

 

[bbqsmokeman]

p45cal thank you for the template. I tested it and some portions work but it doesn't trigger at the original value it needs to for each one. It triggers at the value it needs to increase by so the sick needs to trigger first time at 7 and then every 5 more sick entries for infinity - so 1st trigger for sick is 7, then next is 12, then next is 17 and so on. The lates needs to trigger at 4 then 6 then 8 then 10 and so on, and unauth absences trigger at 1, then 2 then 3 and 4 and five and so on. The G6, H6, and J6 are all formula based and countif is used for when entries in column D matches the entry then it adds to G6, H6 and J6 accordingly
Not sure if I explained it correctly so I updated the spreadsheet you sent with more info that may help

 

[vletm]

... or something like this?
ps. I simplified cells[G6:J6] ... check those!

 

[p45cal]

p45cal thank you for the template. I tested it and some portions work but it doesn't trigger at the original value it needs to for each one. It triggers at the value it needs to increase by so the sick needs to trigger first time at 7 and then every 5 more sick entries for infinity - so 1st trigger for sick is 7, then next is 12, then next is 17 and so on. The lates needs to trigger at 4 then 6 then 8 then 10 and so on, and unauth absences trigger at 1, then 2 then 3 and 4 and five and so on. The G6, H6, and J6 are all formula based and countif is used for when entries in column D matches the entry then it adds to G6, H6 and J6 accordingly
Not sure if I explained it correctly so I updated the spreadsheet you sent with more info that may help

The trigger values are the ones in row 8. You said this was a template. Initially, manually set the thresholds to their originals on row 8, thereafter, when one value in row 6 goes equal or greater than that threshold, the message pops up it will increment row 8 by the value in the cell in row 9. Testing here, as far as I can, it seems to work.

 

[bbqsmokeman]

VLetm; that worked great on the template! I ran into problems when I added the code to the original file and it popped anytime I clicked in a cell in column D. So I figured I would slim down the original file and send it in case some references are interfering with the code.
Also is there anyway this great code could be implemented across all sheets with exception of like the Summary sheet (omitted)- I have the code to omit sheets but don't have the code to include all sheets

 

[p45cal]

vletm, be aware on this line:
If steps >= 0 Then If steps Mod step = 0 Then Show_Msg = True
This will only be True if the count hits an exact multiple; what if the count jumps over the multiple?
Eg. Say the count was 9, and the threshold was 10. If the count then jumps by 2 it will be 11, and will skip the exact 10.
(If I've got this right!?)

 

[bbqsmokeman]

The trigger values are the ones in row 8. You said this was a template. Initially, manually set the thresholds to their originals on row 8, thereafter, when one value in row 6 goes equal or greater than that threshold, the message pops up it will increment row 8 by the value in the cell in row 9. Testing here, as far as I can, it seems to work.

p45cal yours works but I should've explained better and my apologies but I didn't want row 8 to increment by the value in the cell in row 9. I sued those cells as reference to what G6, H6 and J6 should increase by (I should've used a text box) to differentiate them, my apologies if misinterpreted.

 

[bbqsmokeman]

vletm, be aware on this line:
If steps >= 0 Then If steps Mod step = 0 Then Show_Msg = True
This will only be True if the count hits an exact multiple; what if the count jumps over the multiple?
Eg. Say the count was 9, and the threshold was 10. If the count then jumps by 2 it will be 11, and will skip the exact 10.
(If I've got this right!?)

vletm I understand what you are saying but the entries in column D are done daily so to go from 9 to 11 that is failure on the person making the entries as its supposed to be updated each day and each row has a date entry so I don't foresee it as being a problem or getting missed.

 

[p45cal]

are done daily so to go from 9 to 11 that is failure on the person making the entries as its supposed to be updated each day

…until that person, well, goes sick, takes unauthorised absence or is tardy one day and doesn't get the time!!

 

[bbqsmokeman]

it's an attendance tracker used by managers for their staff so only managers have access to it and enter the days that fall under those categories so they have some type of tracker of the agents attendance. The D column is a drop down list that only gets filled on the day the sick, late or absence occurs. If the person doesn't get the time then it becomes a bonus for the agent as they don't end up in attendance management and then falls on the manager for not doing their job.That's why the pop up message because some managers enter the data and the G6, H6 and J6 values climb but they don't pay attention to it to act upon the attendance; so thought if they had a pop up message it would make the manager pay more attention and act on the alert message. Seen agents sick 12-15 days a month but no action taken for HR or letter given to the agent so it's a miss by managers and bonus for the agent as they don't get disciplinary action taken against them when there should be.

 

[vletm]

@bbqsmokeman:
That file was sample just for PopUps
... not fully tested with real data
... and of course I would do some part another way. It could be different story.
hmm... You copied that Macro to another file - It's Okay!
But ... as You notice (or not) it works better with sheet
which name is "Sheet1"! You can make that change.


@p45cal: If steps >= 0 Then If steps Mod step = 0 Then Show_Msg = True
#7: before that if takes away that 'start value' ex 7. no Msg if less ex 7.
if step (-7) mod step (=5) then Msg. if step is 12 then 12-7 = 5 .. 5 mod 5 = 0
it will be True every 5 steps... or how?
And those default values could be (7-4-0-1 / 5-2-0-1) could be in code or in sheet.

 

[bbqsmokeman]

ok I don't know if I am doing something wrong but when I add it to the original it constantly pops. I also noticed that the cells below G6,H6 and J6 populate with numbers (per the code) when originally I only want the pop up message to be when G6, H6, or J7 hit the start values and then pop at each threshold increase value based on entries in column D (which is a drop down list to choose from)
I thought maybe it is better I supply the original file (slimmed down) so I can upload to show what I mean.

So basically everytime an entry is made in column 'D' (sick, late, etc) those values go to G6, H6, J6 respectably as a COUNTIF function to give me totals.
When the 'totals' hit the original target value (say sick is 7) then it pops the message. After that the message in G6 doesn't pop till column 'd' increases by 5 more 'sick' entries thus calculating G6 to 12 then it should pop the message. This should be an infinite value increase.
To help with this and again my deepest apologies if my request/inquiry was misinterpreted I am uploading the slimmed down version of the original workbook with the formulas etc. in case they are impacting how the code runs.

 

[vletm]

@bbqsmokeman - ouch...
1) I tested only with 'Employee 1'
2) I just changed that code as I showed! The code could move to another place (after modification) and it would work with 'all needed sheets'.
3) There (somewhere) has to be those 'help values' now in (G7:K8).
4) ... seems to work ... but I'm not sure how Your codes accept this!
I didn't read all Your codes ... but something maybe makes it slow?
5) As I also wrote previous time ... I did two sample sheets for You.
Because, why need to make own 'employee'-sheet for everyone?
It needs one! .. and rough sample of 'master'. There are NO codes for act now!
and also NO popups! Keep or do something ... or ask more if/then You're interesting.

 

[bbqsmokeman]

vletm, the code works great with exception to G6 pop up. It pops at 7 then at 12 but if it goes to 17 no pop and then 22 no pop but the other 2 work great! You have some real good skills!
p45cal; you also have some great skills as well!
Vletm I am impressed with how you took the extra time to put together another option for tracking attendance. Even though I know it can work if that is all its used for but part of the reason for the layout etc for the sheets is there is movement where agents move to another manager and the format,layout I have in place works with the code to transfer (move) or acquire an employee. Have code that takes the spreadsheet and sends a copy to the new manager which that manager just clicks the acquire link and everything gets pulled into that manager's workbook. This is why I cannot change to your version, although I like it alot!
The problem with only one sheet having the code and the cells G7 G8, H7 H8, J7 J8 having values in them, managers may want to remove those values and break the code. Also it makes it difficult for managers to have to copy the code into the new agent or acquired agent Not disrespecting the managers but they are not familiar with code and would get frustrated trying to figure it out on how to do it and for them having the values in G7,8 and H7,8 and J7,8 will confuse them more.
I like how you simplified the formula for G6,H6 and J6 that is also impressive and makes it cleaner.
I don't know what the answer is but I do know I need to try and get this working with only G6,H6,J6 values triggering the pop ups without using the cells below them and the code needs to be across all sheets so this way its not a copy and paste in every sheet plus when new agents are added, the code would not have to be repeated each time.

 

[vletm]

@bbqsmokeman
1) The code can modify that those G7&8 can take away!
The code have to have 'initial-value' and 'step' for every case.
You can do it ... or just let me know then I'll do it.
2) One or many employee -sheets: Even try to think to use only one row where edit/fill ... per employee. Then no need to have 'many many' data validations and formulas to take care or just to remember add add ...
or ...
'an idea' could be that You filter that employees rows from that sheet and only those rows could send to next manager ... and that next manager 'just' add those rows to own use to same one sheet. - still not necessary to have many sheets - - I don't know how many 'employees' but many sheets makes heavier workbook and at least it needs to take care 'Your Master Summary'-links ...
But, those are just my ideas ...

 

[bbqsmokeman]

vletm; I am still in the learning stages of coding and everyone; including you have been patient and extremely helpful as I learn more and more and develop myself at code as I find it intriguing and exciting to what it can do. With that said though I would ask if you would mind doing it as I know its asking a lot but with your expertise it would probably be done at lot sooner than I could do it. If you don't mind I would deeply appreciate it and I understand the sheet thing and will make a copy of what you presented me and see how my Leader and HR like it and how it would be easier than this method and hopefully they take too it so we can use the layout going forward

 

[vletm]

PreView of that ...
... still missing many parts!
How do 'managers' send 'files'? Manager to Manager or how?
One employees all info is only in one Managers file or
is somewhere 'master-data'-file?
>> Ideas ... Questions ... Answers?

 

[vletm]

@bbqsmokeman
Are You interesting about 'one-sheet-version'?
... check Your 'Inbox'!

 

[bbqsmokeman]

hi vletm; sorry I didn't get back to you sooner but my laptop I had to bury it since it stopped working so I had to get a new one. To hopefully answer a few questions: the managers send files by clicking an icon on the summary page and they input the name of the agent and the code that runs behind the scenes looks for the name finds the tab, copies the sheet and sends the sheet to the new manager via email. The same applies when they acquire someone. The manager saves the attachment (xls file of the agent) and clicks the acquire icon on the summary and locates the xls and it imports to the managers spreadsheet as a tab with the name of the agent and adds that agent into the list of names on the summary page. It is a bit tedious and encumbersum but it does the job. I am always interested in simplification and 'one stop shop' sorta speak to make workload easier as I am trying to bullet proof the workbooks and make it easy enough for anyone coming into the role can do without feeling intimidated

 

[vletm]

... so did You test that version which You have in Your 'Inbox'?
There are not all features ready, of course!