Solved

run macro when a cell is changed by the timeof day

Posted on 2013-01-07
136
233 Views
Last Modified: 2013-03-18
I have a macro that runs a clock with the time changing every minute. When the time reaches certain times throughout the day, cells change from a zero to a 1. i.e. at 08:00 cell S7 changes to a 1. At 09:00 Cell S14 changes to a 1 and so on.

When any cell in the range S7 through S28 changes to a 1, I want Excel to call my macro 'Call Send_EMail'

Thanks
0
Comment
Question by:Jagwarman
  • 65
  • 62
  • 9
136 Comments
 
LVL 76

Expert Comment

by:GrahamSkan
ID: 38750557
You could just call the email-sending code from the time-changing code.
0
 

Author Comment

by:Jagwarman
ID: 38750598
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
0
 
LVL 76

Expert Comment

by:GrahamSkan
ID: 38750648
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.
0
 
LVL 76

Expert Comment

by:GrahamSkan
ID: 38750708
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

Open in new window

0
 
LVL 26

Expert Comment

by:redmondb
ID: 38750926
(GrahamSkan, at least some of the cells have formulas - and a change in a formula's result doesn't trigger the change event.)
0
 
LVL 76

Assisted Solution

by:GrahamSkan
GrahamSkan earned 250 total points
ID: 38750999
Thanks Redmond,
If that is the case, the questioner hasn't reported it. We'll wait and see.

However there is a bug in the code. Line 6 should return a numeric value

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 Cint(strAddress(2))
            Case 7 To 28
                If Target.Value = 1 Then
                    Call Send_EMail
                End If
        End Select
    End If
End If
End Sub

Open in new window

0
 
LVL 26

Expert Comment

by:redmondb
ID: 38751010
Cells S7 etc will not always change to 1 as there is an IF statement in these cells.
0
 
LVL 76

Expert Comment

by:GrahamSkan
ID: 38751025
Jagwarman,
Is that so? If it is, you will have to use my first suggestion.
0
 
LVL 76

Expert Comment

by:GrahamSkan
ID: 38751034
Jagwarman,
Otherwise, can you post all the relevant information, which might include pointers to related questions. Thanks.
0
 
LVL 26

Accepted Solution

by:
redmondb earned 250 total points
ID: 38751111
Jagwarman,

GrahamSkan's first suggestion is simple and effective and, assuming you have access to the clock code, it's definitely the way to go.

However, if you do not have access then please consider the Event code below which runs the Send_EMail macro whenever one of the formula results in S7:S28 changes to 1. There are a few things to be aware of...
(1) As an Event macro, the code needs to be in the sheet not a standard module. (Let me know if you need more on this.)
(2) I am assuming that the Send_EMail macro will cause the value to change back to 0. (Or else the macro may simply run everytime there's a calculation on the sheet, which I assume is not what you want!)
(3) Because of the Event macro, Undo is disabled on Sheet1.

I've included a test file using the macro. Whenever the value in S1 is greater than 50 then S28 changes to 1. The Event macro is triggered by the recalculation, checks S8:S28 and so finds the 1 in S28. This causes it to run the Send_Macro - which just displays a message and sets S1 back to 1.

The Event macro is...
Option Explicit

Private Sub Worksheet_Calculate()
Dim xCell As Range

For Each xCell In Range("S7:S28")
    If xCell = 1 Then
        Application.EnableEvents = False
            Call Send_EMail
        Application.EnableEvents = True
        Exit Sub
    End If
Next
    
End Sub

Open in new window


Regards,
Brian.Send-Email-Test.xls
0
 

Author Comment

by:Jagwarman
ID: 38751257
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
0
 
LVL 26

Expert Comment

by:redmondb
ID: 38751408
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.
0
 

Author Comment

by:Jagwarman
ID: 38751508
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
0
 
LVL 76

Expert Comment

by:GrahamSkan
ID: 38752046
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.
0
 
LVL 26

Expert Comment

by:redmondb
ID: 38752269
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
0
 

Author Comment

by:Jagwarman
ID: 38752518
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.
0
 
LVL 26

Expert Comment

by:redmondb
ID: 38752541
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.
0
 

Author Comment

by:Jagwarman
ID: 38752626
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
0
 
LVL 26

Expert Comment

by:redmondb
ID: 38752656
Thanks, Jagwarman!

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

Thanks,
Brian.
0
 

Author Comment

by:Jagwarman
ID: 38752748
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
0
 
LVL 26

Expert Comment

by:redmondb
ID: 38752771
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.
0
 

Author Comment

by:Jagwarman
ID: 38752828
yes correct
0
 
LVL 26

Expert Comment

by:redmondb
ID: 38752990
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.
0
 
LVL 26

Expert Comment

by:redmondb
ID: 38753232
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.
0
 

Author Comment

by:Jagwarman
ID: 38753686
(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
0
 
LVL 26

Expert Comment

by:redmondb
ID: 38754439
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
0
 

Author Comment

by:Jagwarman
ID: 38756012
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.
0
 

Author Comment

by:Jagwarman
ID: 38756041
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"
0
 
LVL 26

Expert Comment

by:redmondb
ID: 38756149
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
0
 

Author Comment

by:Jagwarman
ID: 38758195
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
0
 
LVL 26

Expert Comment

by:redmondb
ID: 38758840
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.
0
 

Author Comment

by:Jagwarman
ID: 38759297
when you say perhaps you have the live file open, I need the file open to be able to test it, yes?
0
 
LVL 26

Expert Comment

by:redmondb
ID: 38759338
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.
0
 

Author Comment

by:Jagwarman
ID: 38759367
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 :-)
0
 

Author Comment

by:Jagwarman
ID: 38759396
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.
0
 

Author Comment

by:Jagwarman
ID: 38759398
This was your original path Open "d:\Time_Log.Txt" For Append As xFileNumber
0
 
LVL 26

Expert Comment

by:redmondb
ID: 38759437
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.
0
 

Author Comment

by:Jagwarman
ID: 38759573
ok thanks for your help
0
 
LVL 26

Expert Comment

by:redmondb
ID: 38759619
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
0
 
LVL 26

Expert Comment

by:redmondb
ID: 38760719
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
0
 
LVL 26

Expert Comment

by:redmondb
ID: 38765849
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.
0
 

Author Comment

by:Jagwarman
ID: 38766237
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?
0
 
LVL 26

Expert Comment

by:redmondb
ID: 38766548
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.
0
 

Author Comment

by:Jagwarman
ID: 38766579
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
0
 
LVL 26

Expert Comment

by:redmondb
ID: 38766868
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.
0
 

Author Comment

by:Jagwarman
ID: 38767461
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
0
 
LVL 26

Expert Comment

by:redmondb
ID: 38767807
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.
0
 

Author Comment

by:Jagwarman
ID: 38773551
File attached.I hope you will be able to make this work.

Regards
Time-V5.xls
0
 
LVL 26

Expert Comment

by:redmondb
ID: 38773649
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.
0
 

Author Comment

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

Regards
John
0
 
LVL 76

Expert Comment

by:GrahamSkan
ID: 38773690
Hi Guys
Just to say that I have been following this, but there hasn't much I could contribute after the question expanded.
0
 
LVL 26

Expert Comment

by:redmondb
ID: 38773746
Thanks, John!
0
 

Author Comment

by:Jagwarman
ID: 38774773
Redmondb

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

Thanks in advance
0
 
LVL 26

Expert Comment

by:redmondb
ID: 38775321
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.
0
 

Author Comment

by:Jagwarman
ID: 38777201
Ah yes sorry.
Thanks
0
 
LVL 26

Expert Comment

by:redmondb
ID: 38777226
No problem, John! How goes the testing?
0
 

Author Comment

by:Jagwarman
ID: 38777285
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:-)
0
 
LVL 26

Expert Comment

by:redmondb
ID: 38777301
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.
0
 

Author Comment

by:Jagwarman
ID: 38777812
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.
0
 
LVL 26

Expert Comment

by:redmondb
ID: 38778461
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.
0
 

Author Comment

by:Jagwarman
ID: 38786203
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.
0
 

Author Comment

by:Jagwarman
ID: 38786233
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
0
 
LVL 26

Expert Comment

by:redmondb
ID: 38786279
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.
0
 

Author Comment

by:Jagwarman
ID: 38786312
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
0
 
LVL 26

Expert Comment

by:redmondb
ID: 38786337
John,

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

Regards,
Brian.
0
 

Author Comment

by:Jagwarman
ID: 38786385
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
0
 
LVL 26

Expert Comment

by:redmondb
ID: 38786607
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.
0
 

Author Comment

by:Jagwarman
ID: 38786906
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
0
Top 6 Sources for Identifying Threat Actor TTPs

Understanding your enemy is essential. These six sources will help you identify the most popular threat actor tactics, techniques, and procedures (TTPs).

 
LVL 26

Expert Comment

by:redmondb
ID: 38786979
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.
0
 

Author Comment

by:Jagwarman
ID: 38787814
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?
0
 
LVL 26

Expert Comment

by:redmondb
ID: 38788112
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.
0
 

Author Comment

by:Jagwarman
ID: 38788199
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
0
 
LVL 26

Expert Comment

by:redmondb
ID: 38788351
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.
0
 

Author Comment

by:Jagwarman
ID: 38789072
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
0
 
LVL 26

Expert Comment

by:redmondb
ID: 38789190
Thanks, John.

Any thoughts on (A3)?

Thanks,
Brian.
0
 

Author Comment

by:Jagwarman
ID: 38789352
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
0
 
LVL 26

Expert Comment

by:redmondb
ID: 38789412
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.
0
 
LVL 26

Expert Comment

by:redmondb
ID: 38792755
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.
0
 

Author Comment

by:Jagwarman
ID: 38793074
Hi Brian,

where about in the world are you?
0
 
LVL 26

Expert Comment

by:redmondb
ID: 38793864
John,

Ireland. And you?

Regards,
Brian.
0
 

Author Comment

by:Jagwarman
ID: 38794381
Brian

Kent

Regards
John
0
 
LVL 26

Expert Comment

by:redmondb
ID: 38794465
What triggered the question? Stuck in the snow with nothing to do?  :)
0
 
LVL 26

Expert Comment

by:redmondb
ID: 38800657
John,

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

Regards,
Brian.
0
 

Author Comment

by:Jagwarman
ID: 38800707
Hi Brian,

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

Expert Comment

by:redmondb
ID: 38800967
Thanks, John.

You obviously enjoy this stuff.
Mostly!
0
 
LVL 26

Expert Comment

by:redmondb
ID: 38806644
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.
0
 

Author Comment

by:Jagwarman
ID: 38806662
Ok thanks for the update Brian, its very good of you to take on this one.
Regards
John
0
 
LVL 26

Expert Comment

by:redmondb
ID: 38807440
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
0
 

Author Comment

by:Jagwarman
ID: 38808842
Thanks Brian. I will give this a good test.

Regards
John
0
 

Author Comment

by:Jagwarman
ID: 38810184
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
0
 
LVL 26

Expert Comment

by:redmondb
ID: 38810617
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
0
 

Author Comment

by:Jagwarman
ID: 38810797
thanks Brian
0
 

Author Comment

by:Jagwarman
ID: 38826341
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
0
 
LVL 26

Expert Comment

by:redmondb
ID: 38826453
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.
0
 

Author Comment

by:Jagwarman
ID: 38826806
Time Log attached
Time-Log.txt
0
 
LVL 26

Expert Comment

by:redmondb
ID: 38826875
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.
0
 

Author Comment

by:Jagwarman
ID: 38827045
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
0
 
LVL 26

Expert Comment

by:redmondb
ID: 38827091
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.
0
 
LVL 26

Expert Comment

by:redmondb
ID: 38827154
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.
0
 

Author Comment

by:Jagwarman
ID: 38830107
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
0
 

Author Comment

by:Jagwarman
ID: 38830112
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
0
 

Author Comment

by:Jagwarman
ID: 38830151
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
0
 
LVL 26

Expert Comment

by:redmondb
ID: 38830912
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.
0
 

Author Comment

by:Jagwarman
ID: 38830996
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
0
 
LVL 26

Expert Comment

by:redmondb
ID: 38831414
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.
0
 

Author Comment

by:Jagwarman
ID: 38831550
Hi Brian

1 + 2 OK

3 Yes Fine

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

Regards
John
0
 
LVL 26

Expert Comment

by:redmondb
ID: 38831758
John,

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

Brian.
0
 

Author Comment

by:Jagwarman
ID: 38832134
Hello Brian.

Yes no problem.

Thanks and sorry for all the hassel

Regards
John
0
 
LVL 26

Expert Comment

by:redmondb
ID: 38832258
Thanks, John.

I should have it for you in the morning.

Regards,
Brian.
0
 
LVL 26

Expert Comment

by:redmondb
ID: 38833668
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.
0
 

Author Comment

by:Jagwarman
ID: 38834267
Ok thanks Brian. I bet you wish you had not taken this one on. How can I award you more points :-)
0
 
LVL 26

Expert Comment

by:redmondb
ID: 38834692
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.
0
 

Author Comment

by:Jagwarman
ID: 38840039
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
0
 
LVL 26

Expert Comment

by:redmondb
ID: 38840106
Thanks, John. Is Monday OK?
0
 

Author Comment

by:Jagwarman
ID: 38840202
No problem I have plenty to keep me busy.

Have a great week[end]
0
 
LVL 26

Expert Comment

by:redmondb
ID: 38840215
And you, John!
0
 

Author Comment

by:Jagwarman
ID: 38841584
Hi Brian,

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

Kind regards
John
0
 
LVL 26

Expert Comment

by:redmondb
ID: 38841716
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.
0
 

Author Comment

by:Jagwarman
ID: 38842734
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
0
 
LVL 26

Expert Comment

by:redmondb
ID: 38843501
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.
0
 

Author Comment

by:Jagwarman
ID: 38843582
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
0
 
LVL 26

Expert Comment

by:redmondb
ID: 38844952
John,

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

Brian.
0
 

Author Comment

by:Jagwarman
ID: 38848354
(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
0
 
LVL 26

Expert Comment

by:redmondb
ID: 38849929
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.
0
 

Author Comment

by:Jagwarman
ID: 38850119
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
0
 
LVL 26

Expert Comment

by:redmondb
ID: 38850750
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.
0
 

Author Comment

by:Jagwarman
ID: 38855803
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
0
 
LVL 26

Expert Comment

by:redmondb
ID: 38855860
John,

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

(Does your office do Lotus Notes development?)

Brian.
0
 

Author Comment

by:Jagwarman
ID: 38888457
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
0
 
LVL 26

Expert Comment

by:redmondb
ID: 38889155
John,

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

Please post your file here.

Thanks,
Brian.
0
 

Author Comment

by:Jagwarman
ID: 38981567
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
0
 
LVL 76

Expert Comment

by:GrahamSkan
ID: 38980854
Brian chased the moving goalposts over a five week period. Why not give him some points and close the question in the usual way?
0
 
LVL 26

Expert Comment

by:redmondb
ID: 38981568
Thanks, GrahamSkan. I'm raised an Objection.
0
 

Author Comment

by:Jagwarman
ID: 38981596
more than happy to give points
0
 
LVL 26

Expert Comment

by:redmondb
ID: 38981634
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.
0
 
LVL 26

Expert Comment

by:redmondb
ID: 38995201
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.
0

Featured Post

How to improve team productivity

Quip adds documents, spreadsheets, and tasklists to your Slack experience
- Elevate ideas to Quip docs
- Share Quip docs in Slack
- Get notified of changes to your docs
- Available on iOS/Android/Desktop/Web
- Online/Offline

Join & Write a Comment

Outlook Free & Paid Tools
This code takes an Excel list of URL’s and adds a header titled “URL List”. It then searches through all URL’s in column “A”, looking for duplicates. When a duplicate is found, it is moved to the top of the list. The duplicate URL’s are then highlig…
The viewer will learn how to create two correlated normally distributed random variables in Excel, use a normal distribution to simulate the return on different levels of investment in each of the two funds over a period of ten years, and, create a …
This Micro Tutorial will demonstrate how to use a scrolling table in Microsoft Excel using the INDEX function.

708 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

19 Experts available now in Live!

Get 1:1 Help Now