Page 2 of 2 FirstFirst 12
Results 16 to 23 of 23
  1. #16
    Lounger
    Join Date
    Mar 2014
    Location
    Calgary, Alberta, Canada
    Posts
    25
    Thanks
    9
    Thanked 0 Times in 0 Posts
    I really apprecate all of your hard work and effort put into this (and special kudos to JP too). This will really help me out when we ramp up our workflow again in a few weeks!

  2. #17
    5 Star Lounger Maudibe's Avatar
    Join Date
    Aug 2010
    Location
    Pa, USA
    Posts
    1,194
    Thanks
    44
    Thanked 226 Times in 210 Posts
    Thanks for the thanks PM

  3. The Following User Says Thank You to Maudibe For This Useful Post:

    emre.cetin (2014-10-14)

  4. #18
    New Lounger
    Join Date
    Mar 2014
    Posts
    1
    Thanks
    0
    Thanked 1 Time in 1 Post
    ProjectMgr,

    I notice a comment in your first post that has not been addressed. You mentioned you do not have the Developer tab in Excel. That is very easy to fix! Simply go to File/Options/Customize Ribbon. In the column on the right-hand side (Customize the Ribbon), place a check mark next to Developer; then select the OK button and there you have it!

  5. The Following User Says Thank You to TootstheGeek For This Useful Post:

    ProjectMgr (2014-03-27)

  6. #19
    Lounger
    Join Date
    Mar 2014
    Location
    Calgary, Alberta, Canada
    Posts
    25
    Thanks
    9
    Thanked 0 Times in 0 Posts
    I have been testing and tweaking the code slightly and everything works fantastic! One question though, as I was testing I found that if these dates are in the calendar and I click the button again it duplicates the event; is there any line of code to put in so that this does not happen? i.e. so it does not put 2x the events in the calendar and maybe a pop-up generates indicating duplicate project?
    Thanks!

  7. #20
    5 Star Lounger Maudibe's Avatar
    Join Date
    Aug 2010
    Location
    Pa, USA
    Posts
    1,194
    Thanks
    44
    Thanked 226 Times in 210 Posts
    PM, Will have an answer for you this weekend.

    Maud

  8. #21
    Lounger
    Join Date
    Mar 2014
    Location
    Calgary, Alberta, Canada
    Posts
    25
    Thanks
    9
    Thanked 0 Times in 0 Posts
    Thanks Maud.

    Greg (PM)

  9. #22
    5 Star Lounger Maudibe's Avatar
    Join Date
    Aug 2010
    Location
    Pa, USA
    Posts
    1,194
    Thanks
    44
    Thanked 226 Times in 210 Posts
    Greg,

    Due to technical difficulties, I was unable to remote into my work computer where Outlook is installed. However, I have compiled some additional untested code that should work to test if the appointments and/or tasks (reminders) have been created.

    There are two ways to approach this:

    1. The hard way: I have located some code that will actually test to see if there are the tasks/appointments already present in Outlook using the parameters in your workbook. It would need much modification to suit your workbook and since I do not have access to Outlook at present, I have focused on approach 2.

    2. The easy way: A history of tasks/appointments can be created by placing the number 1 in a remote column on the spreadsheet for each project IF the code for that project has successfully added a task or appointment. Each time the code is run, it will for check that column (Column 52) for the history status for that project. If a blank is found (no history) then the code will run and produce the tasks/appointments and then update the history by placing a 1 in the column. If a 1 is found (column 52) then there is a history of the tasks/appointments being made. You will be presented with a message box indicating such whereby you can make a choice to:
    a. Continue to create the task or appointment in which the code will run (Yes)
    b. Decline to continue and the code will be halted (No)
    To remove the history for that project, just delete the value in column 52.

    I hope this addresses your question,
    Maud

    project overview2.png

    Inserted are the following lines (blue):

    Tasks (reminders):
    Code:
    Public Sub CreateTasks()
    num = Selection.row
    'TEST HISTORY STATUS OF TASKS
    If Cells(num, 52) = 1 Then
        Msg = "Task reminders for this project have aready been created." & Chr(13) & _
        "Do you wish to create them any way?"
        Style = vbYesNo
        Response = MsgBox(Msg, Style)
        If Response = vbNo Then Exit Sub
    End If
    AddToTasks Cells(num, 9), "Project End Date", 7
    AddToTasks Cells(num, 8), "1st Files to Send", -2
    If IsDate(Cells(num, 28)) Then AddToTasks Cells(num, 9), "Arrange Monitors", 7
    If IsDate(Cells(num, 29)) Then AddToTasks Cells(num, 8), "Confirm Monitors", -2
    If IsDate(Cells(num, 29)) Then AddToTasks Cells(num, 9), "DDS", 3
    If Cells(num, 20) > 0 Then AddToTasks Cells(num, 21), "3rd Party letters to Send", -7
    Cells(num, 52) = 1
    End Sub
    Appointments:
    Code:
    Public Sub CreateTasks2()
    row = Selection.row
    'TEST HISTORY STATUS OF APPOINTMENTS
    If Cells(row, 52) = 1 Then
        Msg = "Appointments for this project have aready been created." & Chr(13) & _
        "Do you wish to create them any way?"
        Style = vbYesNo
        Response = MsgBox(Msg, Style)
        If Response = vbNo Then Exit Sub
    End If
    appointment = AddToCalendar(Cells(row, 9), "Project End Date", "Office1", #8:00:00 AM#, #9:00:00 AM#)
    appointment = AddToCalendar(Cells(row, 8), "1st Files to Send", "Office2", #8:00:00 AM#, #9:00:00 AM#)
    If IsDate(Cells(row, 28)) Then appointment = AddToCalendar(Cells(row, 9), "Arrange Monitors", "Office3", #9:00:00 AM#, #10:00:00 AM#)
    If IsDate(Cells(row, 29)) Then appointment = AddToCalendar(Cells(row, 8), "Confirm Monitors", "Office4", #9:00:00 AM#, #10:00:00 AM#)
    If IsDate(Cells(row, 29)) Then appointment = AddToCalendar(Cells(row, 9), "DDS", "Office5", #10:00:00 AM#, #11:00:00 AM#)
    If Cells(row, 20) > 0 Then appointment = AddToCalendar(Cells(row, 21), "3rd Party letters to Send", "Office6", #10:00:00 AM#, #11:00:00 AM#)
    Cells(row, 52) = 1
    End Sub
    Attached Files Attached Files
    Last edited by Maudibe; 2014-03-31 at 10:24.

  10. #23
    Lounger
    Join Date
    Mar 2014
    Location
    Calgary, Alberta, Canada
    Posts
    25
    Thanks
    9
    Thanked 0 Times in 0 Posts
    Thanks Maud,
    I will try out Option #2 which should suit its purpose.
    Much appreciate again!

Page 2 of 2 FirstFirst 12

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •