alert notifications at set points in time

0

Hi team I am creating a spreadsheet that will send a notification via either email or just by a colour change in the cell.

so using forms i have a database which will be fed by the form which records the hours run by a set of compressors these comprssors require a maintenance shedule which is due in set periods of time thus when the database reaches these set points or slightly before it sends a notification thats the theory and i am sure excel can perfom this just my skills in excel dont allow it.

the setpoints would be shown in hours the periods being:

500hr service due

1000 hrs service is due or six months (whichever is sooner)

2000 hrs service is due or 12 months (whichever is sooner)

5000 hrs Major overhaul is due.

these service periods are concurrent meaning that after the hours top 5000 hours they will continue to rise or already have done so.

In this case i would be using the running hours column to trigger the notification.

This may be a little untidy atm and i plan to redo it with all the date now stored in the spreadsheet but i would rather get this right and then transpose them into the final work book.

Hi John the logger sheet was really my initial attempt at a means of creating a table that would be easy for the guys to modify so yes manually input the data there.

The other option i was exploring was to create a form which they would use that would feed in to the worksheet. 

Notifications wise i was thinking maybe a email to certain team members and maybe a colour change in the cell to signify that mainenance was due.

The setpoits are hours run from installation all the current hours record are the actual hours run at this time.

Answer
Discuss

Discussion

Hi Mark and welcome to the Forum

Excel can do what you describe but uploading a sample Excel file would really help contributors to see which cells/fields you're using to trigger the notification (is that to be a message box in file or an email; triggered automatically or on a prompt from the user, say a "Check upcoming maintenance tasks" button?)

To upload a workbook, please edit your original question (adding detail is necessary) and use the "Add files..." button below the text to attach the file(s).
John_Ru (rep: 1002) Mar 22, '21 at 8:01 am
Mark
Thanks for providing a file and more detail (sounds like you're dealing with HV subs).
Is the "logger sheet" updated manually (for hours run)? Are the "setpoints" (hours run or time) from the installation date (and if so where is  that recorded)? What type of "notification" do you mean?
Again, please revise your original question so others know.
John_Ru (rep: 1002) Mar 22, '21 at 12:14 pm
Mark

Thanks for clarifying your question (next time, please don't name a contributor when doing so but say something like "Revision 1: <<blah,blah blah>>" so we can see what's changed. Okay to address a contributor or user in the Discussion bits though!

There's a lot needed to achieve what you need. I'll try to find some time later to look at it.
John_Ru (rep: 1002) Mar 23, '21 at 5:47 am
Understood didnt think to write here

I had created a form to feed in which created a database using MS forms what i might do during the day here is redo that and upload that database here rather than use the logger sheet, As i said there is quite a bit of tidying up to be done as this is really my first effort.
Mjonah Mar 23, '21 at 5:55 am
Mark

Your clarification is key to the question (so you did it in thei right place IMHO).

I'm in the UK too (?) but a bit busy today so if you submit a "tidied" file later today, I might get chance to look either this evening or tomorrow.

Not sure about using MS Forms for input (though if you want the resultant Excel file manipulating that's okay). Excel VBA has a UserForms that can be used (but that makes the project larger).
John_Ru (rep: 1002) Mar 23, '21 at 6:03 am
I have updated the spreadsheet and tidied it up leaving the logger sheet as the data entry location for now
Mjonah Mar 23, '21 at 7:53 am
Mark

I should have some time this afternoon to look at this again. Two questions please- should the maintenance  check/emails be triggered by changes made to the logger sheet (manually)and on workbook open (to capture 6/12 month time set points where hours data isn't available)? What is the role of WSE Frequency in the maintenance work?
John_Ru (rep: 1002) Mar 26, '21 at 4:48 am
John 

I think the best time for the emails/notification to be triggered would on the updating of the logger sheet, The time periods 6 and 12 months is a set period for the relevant maintenances to happen regardless of that actual running hours so if 6 months is up but the compressor has only run 780 hours say we should be doing the maintenance. 

I have updated the  workbook to reorder the columns to avoid the confusion re the WSE.

WSE's are Written scheme of examination for insurance purposes and have no bearing on the required notification.
Mjonah Mar 26, '21 at 5:39 am
Thanks Mark

I'll take the liberty of adding two extra columns and send an example of my thinking (for one substation)
John_Ru (rep: 1002) Mar 26, '21 at 6:42 am
sounds good
Mjonah Mar 26, '21 at 8:26 am
Add to Discussion

Answers

0

Mark

Please see the attached trial sheet (you'll need to permit macros). It's not complete- it works on a modified version of your Ninfield sheet only)  but if you change one of the values (in yellow cells) in "logger sheet", that value will by written to the matching row in "Ninfield NEW" and a MessageBox will return some data to you.  

It uses this worksheet event macro which detects if you changed one of the yellow cells (you can just press enter in one of those or type a new value):

Private Sub Worksheet_Change(ByVal Target As Range)

If Intersect(Target, Sheet1.Range("B3:B8")) Is Nothing Then Exit Sub
If IsNumeric(Target.Value) Then
    MsgBox ServiceDueByHrs(Target.Value) & vbLf & ExtractPlantData(Target.Offset(0, -1).Value, Target.Value), vbOKOnly, "Updated plant data"
End If

End Sub
That calls two functions (in Module1) but they can be changed to modify what happens.

Note that, for now, it only checks whether the updated hours fall within 100 hours of a 1000, 2000 or 5000 hours setpoint and says which service is due according to that. You can play with the numbers to check it works. It  doesn't account for installatio date (and date limits from that) or service performed dates. Likewise there's an issue if the hours are updated in the "dead bands" between those tolerances but this is a play version. If the logic is right (/improved) then it could be appiled to other sheets and emails could be triggered to suit (perhaps using data like the MessageBox returns).

Also I've moved your Running Hours column to B in Ninfield New , leaving your conditional formatting in place but correcting the VLOOKUP formula you used. I've used a structured formula but the non-structured formula for B4 would be:CODE]=IFERROR(VLOOKUP(B4],'logger sheet'!A:B,2,FALSE),"No data")[/CODE]which also adds "No data" is there isn't a match in the "logger sheet". Note however that the macro will overwrite that formula when new hours values are written in the "logger sheet

Note that this is a project really (so not strictly within the rules of the Forum).

Hope this is useful. Let me have your comments please.

Discuss

Discussion

Forgot to say, the setpoint thresholds (plus/minus 100) are currently "hard-coded" in one of the functions. The Setpoints sheet isn't used yet.
John_Ru (rep: 1002) Mar 26, '21 at 1:47 pm
Thankyou John that certainly works as wished in that it alerts the staff memeber to the fact it has hits a threshhold point and that a service in now due and which service is now due.

Now we have this i took the liberty of adding another tab for address to send an alert email to my personal one for testing purposes, although this could be any email  address contained in this tab. 

This also doubles down as a quick check for management to identify whether the weekly routines are being done. Though i would daresay my team leader would not enjoy the 19 emails received to say the routine has been done for the machines that week.
Mjonah Mar 29, '21 at 4:17 am
Mark
What I posted was "a toe in the water" so to speak, not intended as the answer to your problem.

Not sure how much time I'll have to work on it this week but please see email sent to your test address.
John_Ru (rep: 1002) Mar 29, '21 at 5:08 am
Mark- that email address failed. Daemon reported "The email account that you tried to reach does not exist. Please try double-checking the recipient's email address for typos or unnecessary spaces."
John_Ru (rep: 1002) Mar 29, '21 at 5:16 am
Mark, let me know about the email address (and my comments above) when you get chance please.
John_Ru (rep: 1002) Mar 29, '21 at 11:02 am
Private Sub Worksheet_Change(ByVal Target As Range)
'Update by Extendoffice 2018/3/7
    On Error Resume Next
    If Target.Cells.Count > 1 Then Exit Sub
  Set xRg = Intersect(Range("D2"), Target)
    If xRg Is Nothing Then Exit Sub
    If IsNumeric(Target.Value) And Target.Value > 500 And Target.Value < 1000 Then
        Call Mail_small_Text_Outlook
    End If
End Sub
Sub Mail_small_Text_Outlook()
    Dim xOutApp As Object
    Dim xOutMail As Object
    Dim xMailBody As String
    Set xOutApp = CreateObject("Outlook.Application")
    Set xOutMail = xOutApp.CreateItem(0)
    xMailBody = "ALERT: Compressor running hours" & vbNewLine & vbNewLine & _
              "DUNG2AC1 has a running time in excess of 5000hrs" & vbNewLine & _
              ""
    On Error Resume Next
    With xOutMail
        .To = "mjonastesting@gmail.com"
        .CC = ""
        .BCC = ""
        .Subject = "Alert: Compressor Running Hours"
        .Body = xMailBody
        .Display   'or use .Send
    End With
    On Error GoTo 0
    Set xOutMail = Nothing
    Set xOutApp = Nothing
End Sub
 
Mjonah Mar 30, '21 at 4:54 am
My colleague at work created this vba code which generates a email for whomever runs the macro to send but he said he can get it to auto check and send.
Mjonah Mar 30, '21 at 4:57 am
Mark

I see you posted a macro above (but not sure why- I know how to send emails from Excel!). I was saying that the email ADDRESS you sent did not work for me (the email was bounced). I was sending you a PM to suggest we chat about what might be done (when I get some time) but it looks like you've started work on it.
John_Ru (rep: 1002) Mar 30, '21 at 4:59 am
Oops! Missed your comment after the code (guess I was typing at the time).

If you're following a different route (using your colleague's help), please bear in mind my code has some shortcomings (in that it relies on the hous being changed inside a +/- 100 hours of a set point and doesn't take account of/ manipulate service due/done dates).

If you're done with my help, do you feel I answered anwered your question? If so, kindly mark my answer as Selected. Otherwise, please let me know (and consider a chat)
John_Ru (rep: 1002) Mar 30, '21 at 5:44 am
Add to Discussion


Answer the Question

You must create an account to use the forum. Create an Account or Login