+ Reply to Thread
Results 1 to 6 of 6

Help with alert box

  1. #1
    Registered User
    Join Date
    06-26-2014
    Location
    Scotland
    MS-Off Ver
    10
    Posts
    37

    Help with alert box

    Hi i am attempting to set up an alert box

    I want it to notify me if an employee has less than 12 hours rest between shifts
    The start times (E:E) and finishing times (F:F) and the rest period (G:G) all starting from row 5
    with the name and details in E1 ect

    Ive written the code below but it doesnt work the message either shows every time any value is entered or if I tweak it it doesnt work at all.


    Private Sub Worksheet_Change(ByVal Target As Range)
    If Range("G5") < 12 Then
    MsgBox Range("E1") & " has worked less than 12 hours "
    End If
    End Sub


    Thanks x

    I was then planning on creating a button on the alert which says send email and would send a prewritten email, so if anyone could help me with that or where i could find the info to do it would be great cant find anything online

  2. #2
    Forum Expert
    Join Date
    07-31-2010
    Location
    California
    MS-Off Ver
    Excel 2007
    Posts
    4,070

    Re: Help with alert box

    How do you want the code to be triggered?
    Do you only need to have a message box for those that have no rest period in column G?
    Do you want a message box for all employees that meet the criteria or just the first one?
    What format is your start and end times in?
    Is the value in column G the result of a formula? If so Worksheet_Change will not work like that.

    I'm sure there is more that needs to be clarified but this is what I got for now.

  3. #3
    Registered User
    Join Date
    06-26-2014
    Location
    Scotland
    MS-Off Ver
    10
    Posts
    37

    Re: Help with alert box

    Quote Originally Posted by stnkynts View Post
    How do you want the code to be triggered?
    Do you only need to have a message box for those that have no rest period in column G?
    Do you want a message box for all employees that meet the criteria or just the first one?
    What format is your start and end times in?
    Is the value in column G the result of a formula? If so Worksheet_Change will not work like that.

    I'm sure there is more that needs to be clarified but this is what I got for now.

    Hi I wasnt to sure about how it was best to have it triggered. I enter the times HH:MM manually in columns E and F and have a formula for column G ( general format) to calculate how many hours rest they have. Is it possible to trigger this buy simply entering the values in E and F ?

    I was going to do this for each employee but they are 7 columns apart for example:
    Employee1 (columns E:K) Employee2 (columns L:R) all the way up to Employee118 (column AER:AEX)
    would i need to enter each range individually

    do you have any suggestions ?
    Sorry if my info is a bit hopeless im just starting out
    Thanks in advance

  4. #4
    Forum Expert
    Join Date
    07-31-2010
    Location
    California
    MS-Off Ver
    Excel 2007
    Posts
    4,070

    Re: Help with alert box

    would i need to enter each range individually
    It really depends on what you want to do. If you want it to trigger on a worksheet_change event where when you enter in a time into F, for example, it will look at column G and do code based upon its value then yes, you will need to enter each range individually. If you wanted to run a subroutine to look at column G and every 7 columns after that to see if the value is less than 12 you would not need to enter each range individually. The flaw to the subroutine is that it will be applied to all employees not just the one that has had time entered (well thats not entirely true but the coding gets a little time consuming for a free project).

    Sorry if my info is a bit hopeless im just starting out
    No worries. But keep in mind if your info is "a bit hopeless", my ability to help you is even more hopeless because I can't read your mind, or see what you are seeing, and rely entirely on your ability to communicate effectively.


    Here is a quick proposal. Since the only reference for employee names I have is from the code in your first post this will be limited in direction but will hopefully demonstrate to you some capability.

    Please Login or Register  to view this content.

  5. #5
    Registered User
    Join Date
    06-26-2014
    Location
    Scotland
    MS-Off Ver
    10
    Posts
    37

    Re: Help with alert box

    Thank you !!
    Last edited by Justmegan93; 08-07-2014 at 06:30 AM.

  6. #6
    Registered User
    Join Date
    06-26-2014
    Location
    Scotland
    MS-Off Ver
    10
    Posts
    37

    Re: Help with alert box

    Quote Originally Posted by stnkynts View Post
    It really depends on what you want to do. If you want it to trigger on a worksheet_change event where when you enter in a time into F, for example, it will look at column G and do code based upon its value then yes, you will need to enter each range individually. If you wanted to run a subroutine to look at column G and every 7 columns after that to see if the value is less than 12 you would not need to enter each range individually. The flaw to the subroutine is that it will be applied to all employees not just the one that has had time entered (well thats not entirely true but the coding gets a little time consuming for a free project).



    No worries. But keep in mind if your info is "a bit hopeless", my ability to help you is even more hopeless because I can't read your mind, or see what you are seeing, and rely entirely on your ability to communicate effectively.


    Here is a quick proposal. Since the only reference for employee names I have is from the code in your first post this will be limited in direction but will hopefully demonstrate to you some capability.

    Please Login or Register  to view this content.
    Thank you so much but I couldnt get it to work, think i would need to enter each range individually for each employee using a worksheet_change which i know would be time consuming

    I have tried this :
    Private Sub Worksheet_Change(ByVal Target As Range)
    Dim rng As Range
    Set rng = Target.Parent.Range("G5:G1000")
    If Target.Count > 1 Then Exit Sub
    If Intersect(Target, rng) Is Nothing Then Exit Sub
    Application.EnableEvents = False
    Select Case Target.Value
    Case Is < 12
    MsgBox (Range("E1") + " :less than 12 hours rest"), vbCritical + vbOKOnly, "Value Too Low"

    Case Else
    Target.Offset(, -1).Value = Target.Offset(, -1).Value + Target.Value
    End Select
    Application.EnableEvents = True
    End Sub

    which works if i enter the value in manually in column G but as the value is a result of a formula from E and F is there anyway i could alter the code to make it work with the function ?

    Thanks again

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. Replies: 0
    Last Post: 10-10-2013, 05:14 AM
  2. New message alert... doesn't ALERT me very well
    By Xx7 in forum Outlook Formatting & Functions
    Replies: 6
    Last Post: 07-20-2011, 03:54 PM
  3. Alert Box
    By SamuelT in forum Excel General
    Replies: 3
    Last Post: 10-11-2006, 04:36 AM
  4. Pop-up Alert Help
    By Thomas in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 09-19-2005, 09:05 PM
  5. alert
    By Paula in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 07-05-2005, 05:05 PM

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