run macro when a cell is changed by the timeof day

You could just call the email-sending code from the time-changing code.

As I am new to this VBA stuff I am not sure how to do what you have suggested. However, although I need to call the macro each hour, I only need the macro to run if certain criterea is met. Cells S7 etc will not always change to 1 as there is an IF statement in these cells. Hope this helps

You say that you have already written a macro to change the value of some cells. At the point in the code where the cell value is changed, you could add the line 'Call Send_EMail' immediately after.

Alternatively, you can use the change event:

Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)
Dim strAddress() As String
If Sh.Name = "Sheet1" Then
    strAddress = Split(Target.Address, "$")
    If strAddress(1) = "S" Then
        Select Case strAddress(2)
            Case 7 To 28
                If Target.Value = 1 Then
                    Call Send_EMail
                End If
        End Select
    End If
End If
End Sub

Select allOpen in new window

(GrahamSkan, at least some of the cells have formulas - and a change in a formula's result doesn't trigger the change event.)

Cells S7 etc will not always change to 1 as there is an IF statement in these cells.

Jagwarman,
Is that so? If it is, you will have to use my first suggestion.

Jagwarman,
Otherwise, can you post all the relevant information, which might include pointers to related questions. Thanks.

GrahamSkan and Redmondb Thanks for your input and I will look at both. In the meantime I have put together a dummy of what I am trying to do. [Maybe I am going about it the wrong way !!] My Sheet1 is a checklist and in Cells B-C will be tasks etc. One checklist is used each week. So, column E/I/M etc are the days of the week. Tasks have to be competed at certain times as represented in 'D' I use a Static Data Sheet which has the times and days. Sheet 1 looks at the static data to see what the time is. If the time is reached cells on Sheet1 'BY/CC change. When they change to 1 cells in 'E' would change to URGENT if 'H' is blank. [F/G and H should already be completed if the task has been completed and 'E' would not change to URGENT in which case NO E-mail to be sent so no Macro to be called]

so formula is IF E=Urgent and BY1 =1 call macro and so on............

The more I think about it I prob don't need BZ to CC because all of the criterea could work off BY.

Hope all this makes sense. I have attached a dummy file.
Time.xls

Jagwarman,

Thanks for the update. As you do have access to the code, GrahamSkan's initial post is definitely the way to go, so I'll step aside at this point.

Regards,
Brian.

GrahamSkan I have plugged you code into my file but still cannot get it to run the macro when the cell[s] change to 1

Thanks & Regards

The first thing is that the requirement seems to fit Sheet2 better. My example macro was targeted at Sheet2, so that would have to be changed if it had no other problems.

However since Brian's foreknowledge that the cells' values were calculated and not set directly the Worksheet Change event is inappropriate.

The macro that initiates the update (TimeUp) is not the specific cell update that I imagined from your description, so cannot be used to directly call the email macro.

Brian's code is perfect for the job, so use that and accept his answer.

My apologies for the confusion.

GrahamSkan,

I hope you'll stay with this as there's a way to go with it yet and I'm still convinced that your initial idea is the right one.

Jagwarman,
A few questions, please...
(1) Am I correct that the user enters data in, say, F/G/H as they carry out certain tasks? Are they simply entering a character to say they've done that step or is it something more complicated?
(2) As there are three columns for recording completion, but only two columns (B & C) for the tasks, I assume that there isn't a direct relationship between them?
(3) Does the user need access to the entire week rather than simply the current day?
(4) As things stand, switching to another spreadsheet pauses the clock. Is that what you want to happen?
(5) I'm not sure what your existing Change and SelectionChange Event macros are doing. Are the simply earlier attempts at what we're doing here or are they other functionality?

Thanks,
Brian

A few questions, please...
(1) Am I correct that the user enters data in, say, F/G/H as they carry out certain tasks? Are they simply entering a character to say they've done that step or is it something more complicated?
It is a little more complicated. There is a check box in F which when ticked puts the time in G and their name in G

(2) As there are three columns for recording completion, but only two columns (B & C) for the tasks, I assume that there isn't a direct relationship between them?
Correct.

(3) Does the user need access to the entire week rather than simply the current day?
Yes

(4) As things stand, switching to another spreadsheet pauses the clock. Is that what you want to happen?
No

(5) I'm not sure what your existing Change and SelectionChange Event macros are doing. Are the simply earlier attempts at what we're doing here or are they other functionality?
Earlier attemps.
In conclusion as mentioned previously…When cells BY/CC change to 1 and cells in 'E' would change to URGENT if 'H' is blank. [F/G and H should already be completed if the task has been completed and 'E' would not change to URGENT in which case NO E-mail to be sent so no Macro to be called] So, If E= URGENT and BY= 1 Run Macro which will send e-mail.

Jagwarman,

Thanks for all that.

As I think you you appreciate, given that it's in mid-development the spreadsheet is currently much more complicated than it will be. Because of this, I can't rely on the existing formulas and code to tell me what it should be doing so please give me a description in English, please!

As a starter, here's my current understanding...
(1) This file is left open by the user all day.
(2) As the user completes a task, they tick the box for that time/day.
(3) If, within 15 minutes of an hour elapsing, its box is still not ticked then the task becomes URGENT.
(4) If, after the hour has elapsed, its box is still not ticked then the task is OVERDUE.
(5) An email is sent immediately if a task becomes URGENT.
(6) Once the "End of Hour" processing is complete anything which happens to an earlier hour can be ignored.
(7) At the start of a week, a new, empty file is created for the user.

BTW, what are the rules for the colour of the Deadline cells?

Thanks,
Brian.

Brian,

That is spot on. The colours are RAG ratings, Red on Urgent and Amber when the cell is 15 mins to the hour which is when cell changes from a 3 to a zero. I don't use Green.
Regards
John

Thanks, Jagwarman!

when cell changes from a 3 to a zero
And what's the English for that?!

Thanks,
Brian.

cells in BY/cc start off as 3 then 15 mins prior to the hour they change to 0 then on the hour they change to 1

Regards

Jagwarman,

So, that's simply the current mechanism for identifying that we're within 15 minutes before the hour.

Red on Urgent and Amber when the cell is 15 mins to the hour
The two colours are swapped around, yes?

Thanks,
Brian.

yes correct

Thanks, Jagwarman.

I'm now into testing. However, it's late here so I may not get back to you before the morning.

Regards,
Brian.

Jagwarman,

Testing's going OK so far (complete pain messing with times and days, of course). Except for the trivial ones in Conditional Formatting, there are no longer any formulas. Besides your minute-timer, the code updates the status whenever there's a change to the relevant cells in columns F, J, N, R or V.

I need to understand what happens during the working day for the user, so please review the following...
(1) User opens the file before 9:00. (As things stand, the file doesn't mind being opened before 8:00 but will do nothing until 8:00.)
(2) You confirmed that the file is left open all day, but we have to allow for system crashes etc. which may result in the file being re-opened during the day. To avoid a spurious email, the file should probably assume when it opens anytime after 9:00 that the previous time-slot(s) were completed successfully.
(3) What time is the last task? I assume that the file is not closed until after that hour - otherwise picking up a "final" Overdue would be an issue.
(4) Presumably it's not trivial that an email is being sent to the user's manager. What's to stop the user from, eh, adjusting things?
(5) It would probably be a good idea to log significant activities (e.g. finishing a time-slot, going "Urgent", going "Overdue"). It would be pretty trivial to continuously output this information to a text file. (A long time since I did this kind of thing, but it might even be possible for the user's account to be able to add to the log file but not change or delete it.)
(6) Is the user doing other work with Excel? The timer is robust, but by no means bullet-proof. Did you give any thought to the user running two copies of Excel - one for the tracker and one for other activity? (Hmm,  it might even be possible for the tracker version to be invisible and yet receive updates from the user's "ordinary" excel.)

Enough to be going on with! Talk to you tomorrow.

Regards,
Brian.

(1) User opens the file before 9:00. (As things stand, the file doesn't mind being opened before 8:00 but will do nothing until 8:00.)
Correct.

(2) You confirmed that the file is left open all day, but we have to allow for system crashes etc. which may result in the file being re-opened during the day. To avoid a spurious email, the file should probably assume when it opens anytime after 9:00 that the previous time-slot(s) were completed successfully.
Correct

(3) What time is the last task? I assume that the file is not closed until after that hour - otherwise picking up a "final" Overdue would be an issue.
On this particular file it will be 18:00 but on others it could be later.

(4) Presumably it's not trivial that an email is being sent to the user's manager. What's to stop the user from, eh, adjusting things?
Because the e-mail is created from a Macro and the Code is password protected

(5) It would probably be a good idea to log significant activities (e.g. finishing a time-slot, going "Urgent", going "Overdue"). It would be pretty trivial to continuously output this information to a text file. (A long time since I did this kind of thing, but it might even be possible for the user's account to be able to add to the log file but not change or delete it.)
Sounds Good

(6) Is the user doing other work with Excel? The timer is robust, but by no means bullet-proof. Did you give any thought to the user running two copies of Excel - one for the tracker and one for other activity? (Hmm,  it might even be possible for the tracker version to be invisible and yet receive updates from the user's "ordinary" excel.)
I have not given thisany though. Walk before I can run ¿:-)

Thanks

Thanks, Jagwarman.

Because the e-mail is created from a Macro and the Code is password protected
Probably a good idea to lock the cells as well - only allow columns F, J, N, R and V to be updated.

I've attached my current version. Purely for debugging purposes, I have made some changes...
(1) I have two new completion statuses -
    "OK" - Task was completed before the end of the time slot.
    "Pending"  - Task is O/S but there are more than 15 minutes left.
(2) Most of the completion statuses show the number of minutes to the end of the time slot.

A log file entry is written whenever the user changes Sheet2, or a Completion Status changes to "Overdue" or the current slot is "URGENT". (The latter should only happen once but, until the countdown is removed, it'll happen every minute.)

(BTW, is there a reason why the timer fires every minute? Couldn't is just fire twice an hour - at a quarter to the hour and on the hour?)

Regards,
Brian.Time-V2.xls

Thanks very much for this I will test over the next couple of days and get back to you.

BTW, is there a reason why the timer fires every minute? Couldn't is just fire twice an hour - at a quarter to the hour and on the hour?) No I see no reason why not.

Having just opened it I got an error Method ‘On Time’ of object’ _Application’ failed in Module 1 on this row Application.OnTime setDate, "TimeUp"

Jagwarman,

Please try the attached. If the error occurs, go into Debug and check the value of the setDate variable.

Thanks,
Brian.
Time-V3.xls

Hi Redmondb

I have had the file open since 7:30 this morning and the only thing that has occured is that in cell M3 it says Pending - 56

Jagwarman,

It's working perfectly for me - I'm now at the stage of making it generic so that you can easily change start and stop times. I've seen nothing like your issues, so I'm guessing it's some difference between our set-ups - or perhaps you have the live file open and it's interfering? Is your file open?

In the meantime, I'll create a version with lots of debugs so we can narrow this down.

(Apologies for any delays - my ISP is having problems, so I can only connect intermittently.)

Regards,
Brian.

when you say perhaps you have the live file open, I need the file open to be able to test it, yes?

Jagwarman,

No, you don't need the live file - just my V3 above. (If you needed the live file to test my code then I wouldn't have been able to test it... and I've done a lot of testing!)

Regards,
Brian.

I only have V3 open, the file you sent me, time on Static Data sheet says 15 and no other cells have been updated. I hate Macros :-)

Just got a very odd error, C:Settings\Temporary Internet Files\Content\G7S9N577\Time-V3.xls cannot be found.
When I opened your orig file you had a path as D:\ so I changed to C: but why Temporary Internet Files\Content. Sorry to cause you problems.

This was your original path Open "d:\Time_Log.Txt" For Append As xFileNumber

Jagwarman,

I suspect that you're running the file directly from the web page as opposed to saving it to your PC/Server and running it from there. If so, please don't do that - whenever you close the file the downloaded version is likely to be deleted by your operating system!

Apologies, I should have mentioned that the log file path might need to be changed.

Finally, rather than frustrate yourself, please hold off until the debug version's ready!

Thanks,
Brian.

ok thanks for your help

Jagwarman,

Please see attached. A few points...
(1) Lots of debug information is written to the log file, so any time you see results you don't like, please post the log file here.
(2) "Static Data" is no longer used.
(3) Lots of data is now stored in Constants so you'll be able to easily change them in the future, if necessary. The file are the sheet's name ("Sheet2"), the first hour (8), the last hour (22) and the log file path and name ("D:\Time_Log.Txt"). While we're testing please leave them all alone except the log file.
(4) The OnAction now only fires for a quarter to and on the hour. The next time is shown in A1 (which turns red if the fire time is less than now - this should only happen when the OnAction is cancelled) - nothing reads this cell, it's for debugging only.
(Oh, sometime the time is a minute later than usual - this is to avoid potential problems when the file is opened or a Completion Status cell is changed within the second before an OnAction time.)
(5) The current time-slot's Status is checked not only at the OnAction times but also whenever the user changes a Completion Status cell (F3:F17, J3:J17, etc.). (Very handy for testing!)
(6) Please ignore the List_OnTime() macro. We can discuss it when everything's OK!
(7) To avoid issues with your live file, I've renamed the two main macros.

Edit: I don't know if you're aware of it. but if a file is closed without deleting its OnAction, when the time comes due, Excel will try to open the file and run the macro. Combined with the "live" issue, this may have contributed to the errors you saw earlier.

Regards,
Brian.Time-V5.xls

Jagwarman,

<blush>

Apologies, I dropped the Static sheet, but forgot to use an alternative mechanism for storing the time-slot.

Edit: OK, please see attached. Please note that I've not had time to fully test it (and we saw what happened to V5 when I made a last minute change).

Regards,
Brian.Time-V6.xls

Jagwarman,

V6 seems to be behaving itself. However I have noticed problems when I'm working with code in another file - if I have a Break in another macro (either manual or Debug) when an OnTime fires then the OnTime appears to quietly die. (I'm not sure, but this may also  happens if there's an error in the (?current line of the?) other macro.)

How's your own testing going?

Regards,
Brian.

Redmondb

My testing is going well [and thanks for what you have done so far] I did notice you had dropped the Static sheet, I will need it for stuff I do but that's ok. One thing though, I need to get it to run my macro which send out the e-mail but not sure where to put the 'Call send_email' it needs to be prior to the overdue?

Jagwarman,

prior to the overdue
Isn't it supposed to be at the OVERDUE?  That's where it is anyway - just uncomment it.

I will need it for stuff I do
Yes, I suspected that might be the case. I just wanted to show that I'd nothing up my sleeves!

Regards,
Brian.

hi Redmondb,

it needs to be in before 'Overdue' so that it gives the manager and his/her team to action before it becomes too late.

I have been running/testing V5 and prior to our communication today it was fine. I put the mockers on it when I said 'My testing is going well' as it now appears to have stalled. Seems to have stuck on Urgent :-) I guess that could be same reason you mentioned this morning. i.e. OnTime appears to quietly die

Jagwarman,

it needs to be in before 'Overdue' so that it gives the manager and his/her team to action before it becomes too late.
Isn't that the point of "URGENT" - the user has 15 minutes to sort things out before they go OVERDUE.

Seems to have stuck on Urgent
Are you working on macros on that PC - if so, that's almost certainly the explanation.  Also, there's a macro included called List_OnTime which will tell you whether or not there's an "Update_Data" OnTime - it outputs its results in the Immediate Pane. (BTW, it's slow and it's probably best not to run it five minutes either side of the hour or a quarter to.)

Regards,
Brian.

Redmondb,

the procedure I use to populate F,G,H is through a macro. I started to test it today. I changed your code to read : Set xRange = Intersect(Target, ThisWorkbook.Sheets(xDATA).Range("$H$3:$H$17,$L$3:$L$17,$P$3:$P$17,$T$3:$T$17,$X$3:$X$17")) from ..("$F$3:$F$17,$J$3:$J$17,$N$3:$N$17,$R$3:$R$17,$V$3:$V$17"))
The reason I did this is because I use a macro to populate G and H by the use of a tick box. I presumed it would work and when I ticked the box and W was populated with the time and X was populated with the name, U would change to ok [etc] but it seems like it only work if I manually type something into the cells. I need it to work from the tick box as this shows the persons name and the time they carried out the task. thier info is taken from their PC. I hope this is not going to cause you a major problem and that it can be resolved as you have done so much [good] work on this.
Regards
John

John,

The change you mention is perfect. It doesn't matter whether a change is made manually or via a macro, it'll still cause the Change Event to fire - as long as Application.EnableEvents is True.

Any possibility that H3:H17 and the other ranges are formulas? The Change Event doesn't fire when a formula's value changes.

Could you post your macro here, please?

Thanks,
Brian.

File attached.I hope you will be able to make this work.

Regards
Time-V5.xls

Jagwarman,

It's a small change,from...
xDone = xCell.Offset(0, 1)
...to...
xDone = xCell.Offset(0, 3)

(BTW, using the C drive's root for the log file is dangerous for Windows' versions after XP.)

Regards,
Brian.

excellent that works fine thanks.
I'm only using the C drive during my testing but thanks for the advice.

Regards
John

Hi Guys
Just to say that I have been following this, but there hasn't much I could contribute after the question expanded.

Thanks, John!

Redmondb

back to my original request, how does macro call SendMail once a cellchanges to Urgent?

Thanks in advance

John,

As I replied above, it's already there in the middle of the OVERDUE processing, just remove the comment (and the *'s) from the folowing line...
'************       Call SendMail

Regards,
Brian.

Ah yes sorry.
Thanks

No problem, John! How goes the testing?

It's going very well thanks. Trouble is I keep coming up with other ideas like maybe I can have an input screen to add or remove people from the e-mail list:-)

Feel free to post any ideas here. (I'll let you know if I think they warrant a new question.)

Edit: The recipients - it depends on your email system, but most of them have a facility for creating a group. So SendMail would have that group as its single recipient.

the code in my macro uses external e-mail addresses to create the e-mail. Internally the company uses internal e-mail address [don't know why] and group e-mail addresses do not have external e-mail addresses. we use Lotus notes for e-mails and Excel for spreadsheets.

John,

When you have a spreadsheet's functionality, it feels like overkill to put a front-end on that for a basic admin. task. However...
 - How many addresses?
 - What information for each address?
 - How many users?
 - If multiple users, do they share the data?

Edit: I dug out an old VM with Lotus Notes. It was happy to let me set up a group with what it terms "Internet addresses" (i.e. non-Notes email addresses). (I couldn't actually test emailing the group as that VM has no network access.)

Regards,
Brian.

Hi Brian

I don't know what's happening today I have had V6 open since 07:00 this morning. I have just left it to see when the cells would chenge to amber and not one cell has gone amber.

Brian, it just kicked in at 09:00 and the 08:00 went Amber.
Question. If I have more than one task to be undertaken at the same time should I be able to insert a row and put the time in cell D? I ask because I am testing for that scenario and only the original cell changed to amber.

Thanks
Regards

John,

it just kicked in at 09:00 and the 08:00 went Amber.
Please post the log file.

If I have more than one task to be undertaken at the same time should I be able to insert a row and put the time in cell D?
No, the macro uses the row no. to work out the time - it doesn't even look at column D.
(A) What about two checkboxes per time-slot. Only output the user+time when both boxes are ticked?

I am testing for that scenario
This morning?

Thanks,
Brian.

Hi Brian

the proble as I see it the is that there could be several/numerous tasks that need to be completed by the same completion time would the macro be able to take that into account?

File attached
Time-Log.txt

John,

Thanks, but please answer all the questions in my previous post!

Regards,
Brian.

If I have more than one task to be undertaken at the same time should I be able to insert a row and put the time in cell D?
No, the macro uses the row no. to work out the time - it doesn't even look at column D.
(A) What about two checkboxes per time-slot. Only output the user+time when both boxes are ticked?

(A) Two checkboxes may not be enough as there could be several/numerous tasks that need to be completed by the same completion time.  Would the macro be able to take that into account?


I am testing for that scenario This morning?

(A) I was adding in additional tasks so that I could see if 2, 3, 4 etc tasks with the same time went to amber.

(Q) I have also discovered today that when I call my 'Send Mail' it does not work because the Sub is in Sheet3 (Sheet2) and so I get Sub or Function not defined'  I believe the 'SendMail' Macro needs to be in Sheet3 (Sheet2) or it will not run but I can't call it from Module 1. I appear to be in a catch 22.


As always very much appreciate your work.


Regards

John,

We now have four different threads going on simultaneously - further complicated by using the same numbering system for different threads! We both need to focus on keeping things clear...
 - Please divide your replies into the different threads (in numeric sequence, please!).
 - Please check that your replies answer all of my outstanding questions.  
 - You don't need to quote my entire question in your replies - if there's a single question in a thread then as long as you make it clear to which thread you're referring then that's enough.
 - If there are multiple questions then I'll give each one a letter, so you need only quote that. If you want to ask a new question then continue on from the last letter I used (or from A if I had no letter).

Thread 1 - Problems with this morning's run.
As is clear from both your description and the log file, processing went berserk this morning. It's almost certain that this was caused by your experiments this morning, so as far as I'm concerned this issue is explained and closed. Do you agree?

Thread 2 - Group maintenance.
In my testing, Lotus Notes is happy to use non-Notes email addresses in Groups. So why not use the Notes Group facility?

Thread 3 - Record multiple tasks per time-slot.
This is a new feature so I need to get your requirements. Fair warning, this is sounding like a huge amount of work. A few initial questions...
(A) -  What's the maximum no. of tasks?
(B) - What information do you need to record for each task for each time slot?
(C) - Could different time-slots have different tasks?

Thread 4 - SendMail is not working.
Best practice would suggest that SendMail should be in a Module not a Sheet. Your problem may be that you have prefixed the Sub statement with "Private " - if you move the macro to Module1 and drop the "Private " does the problem go away?

Regards,
Brian.

Thread 1 Problems with this morning's run.
I'm not sure it is working I would like to keep open until tomoorow if that's ok

Thread 2 Group maintenance.
I have tested this and and it does work so will be recommending each team sets up group e-mails. Thanks For that.

Thread 3  Record multiple tasks per time-slot.
(A) - I currently have one checklist with 5 separate tasks which comlete at the same time.
(B) - High level description of task is all
(C) - I don't understand this question

Thread 4 SendMail is not working
I believe that because I am using tick boxes the code needs to go into the Sheet rather than the module. I have tried with the code in the module but it then fails saying 'Unable to get the CheckBoxes property of the Worksheet class' and the code is
Set cb = ActiveSheet.CheckBoxes(Application.Caller)

Regards

John,

Thread 1
I prefer that questions are not prematurely closed - it's much easier for me to track new posts in an open question than a closed one.
The code had been working on two PC's for roughly a week - it suddenly failed just when you did awful things to it. I'm confident that it's a coincidence. But it's easy to check. Put the last  file I gave you into the XLStart folder on someone else's PC, someone who would have Excel afor most of the day and who isn't working on macros. A good test, no?

Thread 2
Thanks! How many teams are there?

Thread 3
(A) This worries me....
 - (A1) You have multiple checklists?
 - (A2) You could have more than 5 tasks?
(B) I'm not sure we're talking about the same thing. What do you want to know about the 11 o'clock time-slot of last Tuesday?
(C) Apologies. Could you have 3 tasks for the 8 o'clock time-slot, 4 for the 11 o'clock, 2 for the 12 o'clock, etc.?

Thread 4
Why is SendMail accessing the checkboxes?

Thanks,
Brian.

Thread 2
At the moment we have in the region on 30 teams all using checklists.

Thread 3
A1) Each team has their own list of tasks. However, the way the way I built the checklists allows me to just change the task name or add to/delete from the number of tasks. I am hoping when this one is finished the same will be true.  
A2) not all tasks have a deadline associated with it. So as long as it is completed by the end of day, that's fine.
B) I don't understand the question.
C) quite possibly

Thread 4
Would it be easier if I uploaded the checklist?

Thanks, John.

Thread 3
(A1) I don't have a clear view, but your Thread 4 suggestion should help that.
(A2) So it actually has a deadline of the last time-slot. One of the fundamental principles was that we'd never change earlier entries.
(B) Thanks anyway, but I think it's academic now.

Thread 4
Yes, please.

Thanks,
Brian.

A2) We won't change earlier deadlines. I will try to explain. Each of the teams will have their own checklists with their own tasks and deadlines. Each checklist will work on the principles that you have been working to. So as they go through the day and a deadline is reached their respective checklists will change status and [hopefully] send out an e-mail to their respective distribution list. Each team will have a 'Master' checklist which will be used at the start of each week. this will be 'blank' when they initially open it. Once the build is complete I will need to have 30 checklists, one for each team, but the underlying macro process will be identical for all of them.

I am so sorry to confuse you and hope this makes it a little clearer.

Regards
Time-V6.xls

Thanks, John.

Thread 3
(A2)
Each checklist will work on the principles that you have been working to
One of the fundamental principles was that we'd never change earlier entries - we would now be doing that in spades. BTW, the only bit that doesn't bother me is the multiple groups.
(A3) How much would you wager that you will never have tasks completing on times other than the hour? That you will never have different tasks on different days?
(B) I have to revisit this. Suppose I told you that everything you wanted could be done (this is just hypothetical, I'm afraid) but that at midnight all of that day's information would be lost forever. Which of the lost data would be drop-dead essential to have?

Thanks,
Brian.

Thread 3
Every day each of the teams complete their current checklists throughout the day and at the end of day they are saved in their respective folders. Every so often a selection of all of the checklists are taken and reviewed for completion. Annually they are also Audited, in a similar way by an external party.  In a very small number of cases some of the tasks have not been completed and the relevant manager has to explain the reason. The current checklists procedures work perfectly well apart from the fact that they do not send out an e-mail when a task is past its time. If we [you] can get that to work and enable me to insert extra tasks they will be perfect.

Thanks as always

Thanks, John.

Any thoughts on (A3)?

Thanks,
Brian.

A3 on the first part, my experience is some tasks are completed well prior to the deadline but many are after, which is one reason why we want the e-mail to be able to be sent to managers. on the second question "That you will never have different tasks on different days... I am not sure I understand this. Every day each team have the same tasks. if a new task is taken on board it will be added to the checklist.

Regards

John,

I don't know what the tasks are, but from what I know of people, processes and organisations, I would bet that you will live to see a task that must be completed on a half-hour and/or tasks that will only happen on less than five days.

Regards,
Brian.

John,

I hope to work on these fundamental changes over the week-end. If there are any more surprises then now is the time to mention them!

Thanks,
Brian.

Hi Brian,

where about in the world are you?

John,

Ireland. And you?

Regards,
Brian.

Brian

Kent

Regards
John

What triggered the question? Stuck in the snow with nothing to do?  :)

John,

Apologies, some things took longer than expected, so I'm afraid I won't have an update for you today.

Regards,
Brian.

Hi Brian,

not a problem appreciate all of what you are doing. You obviously enjoy this stuff.

Thanks, John.

You obviously enjoy this stuff.
Mostly!

John,

A quick update - I've updated the last file you posted to handle multiple rows within a time-slot, but testing takes a while, I'm afraid.

Regards,
Brian.

Ok thanks for the update Brian, its very good of you to take on this one.
Regards
John

John,

Please see attached. A couple of points...
(1) It won't handle a "missing" time-slot, i.e. there must be at least one task for each hour from 08:00 to 22:00 inclusive. I'm working on this.
(2) Because the times in column D are absolutely crucial, they are edited when the file is opened. They must be valid, on the hour times which are greater than or equal to the previous time and between 08:00 and 22:00, inclusive.

Regards,
Brian.Time-V8.xls

Thanks Brian. I will give this a good test.

Regards
John

Hi Brian,

I have tried reading through the macro but it is too complicated for me as I am a relative beginner. I have noticed that the cells don't change to Pending until after the deadline and for the time slot 14:00 the cells have just changed from pending to Urgent and the time is 14:45

Which bit of code do I need to look at changing so that it goes pending say 15 mins before deadline and Urgen on deadline.

Thanks in advance.
John

John,

Please see attached. This handles missing time-slots (and also the situation where the file is closed before the hour is complete but is then opened before the end of the following time-slot.

I have noticed that the cells don't change to Pending until after the deadline and for the time slot 14:00 the cells have just changed from pending to Urgent and the time is 14:45
Pending   ==> Before 45 - Not completed
OK           ==> Before 45 - Completed
Done       ==> After 45    - Completed
Urgent     ==> 45-60        - Not completed
Overdue  ==> After 60    - Not completed
Three points...
(A) As always, if you're not seeing what you expected, please post the log file.
(B) The updates for the hour usually happen at one minute past.
(C) I invented the "Pending" and "OK" statuses so that I could see that changes before the 45th minute were being processed . Of course they'll be removed when you're happy with everything else.

Regards,
Brian.Time-V9.xls

thanks Brian

Good morning Brian.

Unless I am doing somethibng wrong, [which would not surprise me] the cells do not change status prior to the hour.

15 mins prior to the hour I need the cell to highlight that a deadline is near [so Pending at this point would be great] then on the hour it should go Urgent. From what I can tell nothing is happening to well after the hour unless I tick one of the boxes [anywhere on the spreadsheet] which then appears to make the macro go live.

Do you have any update on Thread 4?

I also noticed that when I was working on another Macro the following line of code appeared "Application.Run "Time-V9.xls'!Update_Data". Bizzare.

Kind regards
John
Time-Log.txt

Jagwarman,

(1) It's working fine for me, so please recycle Excel and download again and run the last version I sent you. Let it run from 20 minutes to the hour to 5 minutes past - without loading any other spreadsheet. You should see the following...
??:40 - All O/S items show as "Pending", all other items as "OK". (Please make sure you've at least one item ticked and one unticked.)
??.45 - All O/S items show as "Urgent", all other items as "Done".
??.01 - All O/S items show as "Overdue", all other items as "Done".

If any of the above doesn't happen then please describe what you saw and send the log file.

(2) Please see my understanding of your time-line in this post. From your last post, it seems that I've misinterpreted your times. So, please post your real time-line.

Regards,
Brian.

Time Log attached
Time-Log.txt

Thanks, John.

That's deeply confusing. At 13:42:39 it shows a "Close". The only way that such an entry is written is if the Workbook_BeforeClose macro is fired - it's either been called explicitly (unlikely) or someone/thing has tries to close the file.

It goes on. A second later, you updated one of the tasks. The file hadn't been closed (as there would have been an "Open" when you re-opened it), so the file close must have been cancelled!

Can you help me with this?

Regards,
Brian.

Hi Brian

I will do the test again first thing in the morning as I am now in a different building. I will clear the file and start from a blank sheet.

Regards

John,

"I will clear the file and start from a blank sheet."
But please make sure that you have at least one completed task and one O/S one for the initial time-slot. Also, once you've done that, don't make any further changes until 5 minutes past the hour.

Please don't forget a copy of your time-line!

Regards,
Brian.

John,

Oops - please ignore my last post! I described what I got out of the log file - either you were doing things during the run or there's another file interfering. Which?!

Thanks,
Brian.

Morning Brian

I opened the file at approx 7:40 I had nothing else open. It is now 8:34 and I am attaching the TimeLog

Regards
John
Time-Log.txt

I am also attaching the log from yesterday. I left the fil open on my desk all day and night so this may also help in finding out what is happening on my PC with the file. I saved it down as Time_Log2

Regards
John
Time-Log2.txt

Brian I am uploading the Time_Log again as It the cells have just changed to Urgent at 8:45

Time changes should be:

15 mins prior to hour change to 'Pending'
on the hour change to 'Urgent'
15 Mins past hour change to Overdue

Kind Regards
John
Time-Log.txt

Thanks, John,

(1) I still need a response to this post, please.

(2) I'm parking your revised time-line for the moment - there's no point in making changes while you're possibly having problems with the underlying timer.
(Just a reminder - if you're amending your own macros then you'll almost certainly interfere with the timer. Any possibility that that is happening?)

(3) The third file is exactly what I'd expect to see - other than your change to Task 3, nothing really happened until 8:45 when a number of tasks were marked as Urgent.and the routine went to sleep until 9:00.

Brian.

Hi Brian

(1) I still need a response to this post, please.
           Oops - please ignore my last post! I described what I got out of the log file - eitheryou       were doing things during the run or there's another file interfering. Which?!

Answer) I opened another version of Excel and had nothing else open in the version the checklist was open in.

(2) It is possible I guess but if I can't have the file open and work on another file at the same tiime it's going to make it difficult for me to test. All the people I work with will have lots of Excel apllications open so I can't even ask them to test it.

Regards
John

John

Thanks.

(1) I opened another version of Excel and had nothing else open in the version the checklist was open in.
I've tested that and it doesn't seem to cause a problem (unless you open the file in the other instance as well). However, I think that the likely answer is that "you were doing things during the run" - you started to close the file. It shut-down the timer (as otherwise Excel would have re-opened the file when the timer expired) and wrote the log-file entry. At that point, Excel stepped in with the "Save/Don't Save" prompt. However, you selected "Cancel" which left the file with the timer cancelled (and a big red warning box in A1:B1).
Two solutions to this...
(A) Don't cancel a Save. However, people being people, this is not a reliable solution, so...
(B) Change the shut-down macro so that it automatically saves and closes the file.

(2)  if I can't have the file open and work on another file at the same tiime it's going to make it difficult for me to test.
Yes, I've had to go through that pain.
All the people I work with will have lots of Excel applications open so I can't even ask them to test it.
Are all these people editing macros? As I explained earlier, it's not a problem to have other Excel files open or even to run macros (although they may cause delays) - you must actually be editing a macro.
(The reason that editing code is an issue is that Excel may try to run a timer macro while you've entered half a line of code, say myVar =, which causes an error, which brings the timer macro to a juddering halt.)

(3) I hadn't include a question is this item, but are you happy with what I said?

(4) As you can imagine, I do a lot of code editing. Despite that, I've only had a few cases where that interfered with my regular running of the timer. When that happened, I saved, closed and re-opened the timer file to get things moving again.
If your users edit code or you can't adequately test the routine then, my apologies, but the timer is just not suitable for your requirements. If that's the case then I would suggest that you hit the Request Attention button and ask for the question to be deleted and immediately open a new one.
Alternatively, if your users don't routinely edit code and you're happy that you can come up with an acceptable test routine then I'll make the "Save" change and implement the revised schedule.

Please let me know how you wish to continue.

Thanks, Brian.

Hi Brian

1 + 2 OK

3 Yes Fine

4) none of the users edit code so we are fine to continue.

Regards
John

John,

And you're happy that you or a user will be able to test it?

Brian.

Hello Brian.

Yes no problem.

Thanks and sorry for all the hassel

Regards
John

Thanks, John.

I should have it for you in the morning.

Regards,
Brian.

John,

I should have it for you in the morning.
Much too optimistic. Your change to the time-line is a fundamental blow to the existing logic, so it's a big job.

Brian.

Ok thanks Brian. I bet you wish you had not taken this one on. How can I award you more points :-)

John,

I bet you wish you had not taken this one on.
Swings and roundabouts - some questions are closed almost before they're started. And then there's this one...            :)

Regards,
Brian.

Brian,

Looking good. Opened in separate Excel and it all worked perfectly to the times in the Macro. So, if the timelines are changed to

15 mins prior to hour change to 'Pending'
on the hour change to 'Urgent'
15 Mins past hour change to Overdue

I think we [you] are almost there,

Thanks
Regards
john

Thanks, John. Is Monday OK?

No problem I have plenty to keep me busy.

Have a great week[end]

And you, John!

Hi Brian,

Should have also said if we can sort out the e-mail as well.

Kind regards
John

John,

Should have also said if we can sort out the e-mail as well.
There have been lots of email questions, so you'll have to be more specific.

The last o/s item I can see is my question to you as to why you though the sendmail had to access the checkboxes.

Thanks,
Brian.

Hi Brian,

The last o/s item I can see is my question to you as to why you though the sendmail had to access the checkboxes.

Thread 4
The guy who was here before me wrote the macro that sends out the e-mail and I have no idea how that works. I figured that by sending you the file you would be able to sus it out (17/1/2012)

If there is a better way of creating the e-mail I am more than happy to use it. (It seems to me, as I am getting into this stuff that there are always several different ways of writing the code)

Kind regards
John

John,

Oh dear.

I figured that by sending you the file you would be able to suss it out (
Unfortunately you didn't mention that in that post - which specifically referred to the file being for Thread 3.

All of the time we've been talking it's been about sending an email when a task goes Urgent/Overdue (whatever). Looking at the code now I see that the, to the contrary, it talks about the check-list being "complete and ready for sign-off".

(1) What is the name of the recipient group? (Horrible thought, is it separate for each of the thirty teams? If so, how does the file know which group to email?)
(2) What is the Subject, the Body and the Sender?
(3) What errors are you getting?
(4) Have you got a working macro which emails via Notes? If so, please post it here.

Brian.

Hi Brian,

All of the time we've been talking it's been about sending an email when a task goes Urgent/Overdue

That is exactly what I want to do. What happens at the moment is that the user will 'Press' a button on their Checklist which is assigned to the Macro that sends the e-mail.

What I need the Macro to do is 'Call' the macro each time a tasks goes 'Urgent' rather than the user press the button.

I hope this helps.

Regards
John

John,

Thank you for confirming my understanding, but please respond to the questions in my post.

Brian.

(1) What is the name of the recipient group? (Horrible thought, is it separate for each of the thirty teams? If so, how does the file know which group to email?)

Each team has their own checklist and each checklist will have its own e-mail recipient.
i.e. The code in the 'SendMail' is like ......Call SendNotesMail("This e-mail confirms the checklist for xxxxxxxxx is complete and signed-off", "",

(2) What is the Subject, the Body and the Sender?

The e-mail goes out with the subject "This e-mail confirms the checklist for xxxxxxxxx is complete and signed-off"

There is no narrative in the body.

The sender is currently [I believe], taken from the person who has the checklist open. But thinking that through as the checklist can be shared I am not sure how that would work. That is probably why the e-mail is not working because it does not know who to send it from.  That would explain the error [below]

(3) What errors are you getting?
'Unable to get the CheckBoxes property of the Worksheet class'

(4) Have you got a working macro which emails via Notes? If so, please post it here.

The code for this is the same code as in all other checklists [Module 2]

Regards
John

John,

"This e-mail confirms the checklist for xxxxxxxxx is complete and signed-off"
You requested that we only send an email when a task goes "URGENT" - why are we sending a "complete" message for something that hasn't been done?

the checklist can be shared
Do you mean that a single file will be simultaneously open for multiple users?

Brian.

Morning Brian

"This e-mail confirms the checklist for xxxxxxxxx is complete and signed-off"
You requested that we only send an email when a task goes "URGENT" - why are we sending a "complete" message for something that hasn't been done?

Sorry I just took that from an existing one but the wording would be changed to something like "This e-mail is to inform yuo that a deadline has been missed" It would then be up to the line manager to investigate with his/her staff.

the checklist can be shared
Do you mean that a single file will be simultaneously open for multiple users?

Yes, currently a checklist is opened as 'Shared' so that all team members can complete it.

Regards
John

John,

Yes, currently a checklist is opened as 'Shared' so that all team members can complete it.
Your latest surprise and, I think, the show-stopper. "Normal" shared spreadsheets are bad news, but adding timers to them only compounds the issues.

I strongly recommend that you abandon this idea immediately - hit the Request Attention button and ask for this question to be deleted. Meanwhile, open a new one in the appropriate topic area - normally I'd suggest Access but as you're a Lotus Notes site that may be the better option.

Brian.

Hi Brian,

OMG :-(

Can I suggest that we hold off for a couple of days and maybe do a rethink. I really don't want to throw away all your good [hard] work. Maybe I can come up with an alternative way around this.

Kind Regards
John

John,

As you will. You might also think about you'd avoid multiple emails being sent.

(Does your office do Lotus Notes development?)

Brian.

Hi Brian,

I have the e-mail working now but then another problem arose. If I minimise the spreadsheet and work on some other spreadsheet, the timer put 'Urgent' etc in the current spreadsheet I am working on not in the one where the macro is.

Any idea on that.

Last hope :-)

Regards
John

John,

If you've changed it to handle workbook sharing then...Respect!

Please post your file here.

Thanks,
Brian.

I've requested that this question be deleted for the following reason:

The question became too complex over time and needs to be revaluated by myself

Brian chased the moving goalposts over a five week period. Why not give him some points and close the question in the usual way?

Thanks, GrahamSkan. I'm raised an Objection.

more than happy to give points

Thanks, John.

This is my fault as I had suggested above that you delete the question and open a new Access or Lotus Notes one. However, I had forgotten (!) that GrahamSkan was involved. So, assuming that you're agreeable, my suggestion is to split the points evenly between us - pick a favourite post from each of us!

Regards,
Brian.

John,

For future reference...

Unlike the rest of the English-speaking world, in Experts Exchange, "Good" means..."Not Good". To quote from one of the Help pages...
An A grade should be given if you receive the solution from the Experts; you should consider the A grade the default unless it is deficient.
...and...
It is customary to explain any grade that is not an A.

In addition, a valid "It can't be done" is not grounds for lowering a Grade.

B's and C's are considered to be blots on an expert's record so it's important that they're only there for valid reasons.