+ Reply to Thread
Results 1 to 3 of 3

Alerts/Reminders

  1. #1
    Registered User
    Join Date
    10-24-2012
    Location
    Scotland
    MS-Off Ver
    Excel 2007
    Posts
    2

    Alerts/Reminders

    Hello

    I am looking for a way to create an alert or reminder of some sort for two different problems.

    I am creating a training sheet which will outline when a person is booked, approved and attending training and also when that training expires. At the moment, we do not have a sufficient system in place in keeping track of training which has not been approved or declined (sitting idle) and I was looking to have some sort of reminder for when that cell is blank.

    I have looked through threads and found some which alert you when closing the spreadsheet but, as authorisation comes from a different department, we would not likely know the outcome on that day.

    The relevant cells would be: Status = F2 and Expiry Date = I2

    As I am not very experienced in excel, I'm not sure if that would be possible but would be interested in knowing how to do such things.

    I would also like to use something like this to alert us when training is due to expire.

    Any help would be much appreciated.

    Thanks

  2. #2
    Forum Guru Pete_UK's Avatar
    Join Date
    12-31-2011
    Location
    Warrington, England
    MS-Off Ver
    Office 2019 (still learning)
    Posts
    22,607

    Re: Alerts/Reminders

    You would often use conditional formatting for this kind of thing. With this you can get Excel to change the colour of a cell (or group of cells) if some condition is met, so you could have, for example, the name of the employee turn to an orange background if the expiry date is within the coming 4 weeks, and to a red background if the expiry date has passed.

    An alternative is to use an IF statement which can give some appropriate message in a cell if the condition is met (eg "Expiry within 4 weeks" or "Qualification lapsed").

    I think you need to decide which of these approaches you want as your warning mechanism, and perhaps post a sample workbook so that the layout of your data can be seen (de-sensitize the data before posting).

    Hope this helps.

    Pete

  3. #3
    Registered User
    Join Date
    10-24-2012
    Location
    Scotland
    MS-Off Ver
    Excel 2007
    Posts
    2

    Re: Alerts/Reminders

    Hi

    Thanks for replying. I was really hoping for an alert to be sent to my outlook calendar 1 week after a date has been entered into the request sent cell so that it will prompt others to check the status of the request. I have some of the macro I have been working on below, but I need it to check the date in the E column and if the date is one week old, alert us.

    Dim OLApp As Outlook.Application
    Dim OLAI As Outlook.AppointmentItem
    Set OLApp = New Outlook.Application
    Set OLAI = OLApp.CreateItem(olAppointmentItem)
    With OLAI
    .Subject = "Training Spreadsheet"
    .Body = "Check status of training"
    .Start = DateSerial(2012, 10, 25) + TimeSerial(11, 30, 0)
    .End = .Start + "00:30"
    .Location = ""
    .MeetingStatus = olMeeting
    .RequiredAttendees = "donna.brown@shetland.gov.uk;eilidh.coull@shetland.gov.uk"
    .Send
    End With
    Set OLAI = Nothing
    Set OLApp = Nothing
    End If
    End Sub

    Donna
    Last edited by donnarose92; 10-25-2012 at 08:44 AM.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Tags for this Thread

Bookmarks

Posting Permissions

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1