Page 1 of 2 12 LastLast
Results 1 to 15 of 23
  1. #1
    New Lounger
    Join Date
    Jul 2015
    Posts
    12
    Thanks
    2
    Thanked 0 Times in 0 Posts

    How to add a Pop-up message

    Hi

    I'm trying to create an holiday tracker spreadsheet. But I'm stuck as I need to create a pop-up box warning the Manager that he has got two people on a rest day. When he tries and allow a another person to have the same day off - in a specific area.

    I'm using Excel 2010

    I have attached an example of the spreadsheet.

    Thanks

    Kris
    Attached Files Attached Files

  2. #2
    Gold Lounger Maudibe's Avatar
    Join Date
    Aug 2010
    Location
    Pa, USA
    Posts
    2,633
    Thanks
    115
    Thanked 647 Times in 590 Posts
    Kris,

    Here is your workbook revised. If there are more that 1 RD entered for an area (col B thru E), a warning message box will appear.

    HTH,
    Maud

    Code:
    Private Sub Worksheet_Change(ByVal Target As Range)
    Row = Target.Row
    If WorksheetFunction.CountIf(Range(Cells(Row, 2), Cells(Row, 5)), "RD") > 1 Then
        MsgBox "There are multiple RDs for " & Cells(Row, 1)
    End If
    End Sub
    Attached Files Attached Files

  3. #3
    New Lounger
    Join Date
    Jul 2015
    Posts
    12
    Thanks
    2
    Thanked 0 Times in 0 Posts
    Maudibe

    Thanks for that.

    Is it possible to expand the selection to the entire sheet, as I have about 300 employees to add this sheet and the dates will include an entire year?

    Also how I would change the pop-up message to display when I grant a holiday for a person, but I have got two people on a RD's already - in a specific area.

    Sorry for the questions, but I'm new to VB.

    Thanks

    Kris

  4. #4
    Gold Lounger Maudibe's Avatar
    Join Date
    Aug 2010
    Location
    Pa, USA
    Posts
    2,633
    Thanks
    115
    Thanked 647 Times in 590 Posts
    Kris,

    I have expanded the code to encompass 500 columns. It has unlimited rows. It will continue to warn if 2 RD's on the same area but it will also give a different warning if there are already 2 RDs and now a holiday (HD) is added. There is a lot of room here to do some fancy stuff. I am sure there will be some bells and whistles offered by other members

    HTH,
    Maud

    Code:
    Private Sub Worksheet_Change(ByVal Target As Range)
    Row = Target.Row
    If WorksheetFunction.CountIf(Range(Cells(Row, 2), Cells(Row, 500)), "RD") > 1 And _
        WorksheetFunction.CountIf(Range(Cells(Row, 2), Cells(Row, 500)), "HD") > 0 Then
        MsgBox "There are multiple RDs and a scheduled holiday for " & Cells(Row, 1)
        Exit Sub
    End If
    If WorksheetFunction.CountIf(Range(Cells(Row, 2), Cells(Row, 500)), "RD") > 1 Then
        MsgBox "There are multiple RDs for " & Cells(Row, 1)
    End If
    End Sub
    Attached Files Attached Files

  5. #5
    New Lounger
    Join Date
    Jul 2015
    Posts
    12
    Thanks
    2
    Thanked 0 Times in 0 Posts
    Thank you

  6. #6
    New Lounger
    Join Date
    Jul 2015
    Posts
    12
    Thanks
    2
    Thanked 0 Times in 0 Posts
    Hi Maudibe

    Thanks for that coding. Another question, How do you add the code to show the below information, like what you do for the rest day.
    WorksheetFunction.CountIf(Range(Cells(Row, 2), Cells(Row, 500)), "HD") > 0 Then)

    SC - Shift Change
    1E - Emergency Holiday (1 day)
    0.5E - Emergency Holiday (0.5 day)
    OSP - Off site Paid
    S - Sick/Lateness

    Also is there a way of linking to the Core Areas, so that when you try and book a days holiday for somebody there is a message saying that you have two people on a rest day in that specific core area.

  7. #7
    Gold Lounger Maudibe's Avatar
    Join Date
    Aug 2010
    Location
    Pa, USA
    Posts
    2,633
    Thanks
    115
    Thanked 647 Times in 590 Posts
    Kris,

    I am not sure how you are grouping Rest days but as an example, I put together a spreadsheet that will allow entry of specific types of days off and holidays. I added data validation to make sure that the correct symbols for the days are used and conditional formatting to highlight the different types of rest days used.

    If two or more types of rest days are scheduled, a message box will popup to alert you. If multiple RDs are used and then a Holiday is scheduled, a warning with a different message will popup.

    I grouped all the types above as a rest day and the holiday as a separate type. Totals per core area are indicated in columns B and C. The grouping, data validation, and conditional formatting are all customizable.

    HTH,
    Maud

    Kris1.png

    Kris2.png
    Attached Files Attached Files

  8. #8
    Gold Lounger Maudibe's Avatar
    Join Date
    Aug 2010
    Location
    Pa, USA
    Posts
    2,633
    Thanks
    115
    Thanked 647 Times in 590 Posts
    I realized that the worksheet change event was occurring prior to the recalculation of columns B and C therefore producing the wrong message as noted above. This has been corrected by moving the code to the worksheet calculation event and adding a couple of needed tweaks.

    Maud

    Code:
    Private Sub Worksheet_Calculate()
    If Not Intersect(ActiveCell, Range("D3:SF50")) Is Nothing Then
        Row = ActiveCell.Row
        col = ActiveCell.Column
        If Cells(Row, 2) > 1 And Cells(Row, 2) <> "" Then
            If Cells(Row, 3) > 0 And Cells(Row, 3) <> "" Then
                MsgBox "There are multiple RDs and a scheduled holiday for " & Cells(Row, 1)
            Exit Sub
            Else:
                MsgBox "There are multiple RDs for " & Cells(Row, 1)
            End If
        End If
    End If
    End Sub
    Attached Files Attached Files

  9. #9
    New Lounger
    Join Date
    Jul 2015
    Posts
    12
    Thanks
    2
    Thanked 0 Times in 0 Posts
    Hi, thanks for helping me.

    Sorry I have got a few more questions.

    Is there anyway of the pop-up message not appearing when you delete the contents of a cell?

    Is there a way of linking the rest days and the holidays to the core area, for example?

    Fred, John and Mark are in the core area of FLT

    Fred and John are on a rest day, but Mark wants the same day off. So I need a pop-up to say that there are two people on a rest day. This pop-up if possible, needs to be relating to the core area. In this example it will be FLT.

    Mark and John are on holiday on the same day, but Fred wants the same day off. So I need a pop-up to say that there are two people on holiday for that day. This pop-up if possible, needs to be relating to the core area. In this example it will be FLT.

    Is there a way of deleting the cell, if you have entered an holiday or a rest day and the pop-up has appeared?

    Once again, thanks you for all your help.

    Kris

  10. #10
    Gold Lounger Maudibe's Avatar
    Join Date
    Aug 2010
    Location
    Pa, USA
    Posts
    2,633
    Thanks
    115
    Thanked 647 Times in 590 Posts
    Dummy me. I was thinking that the core areas were the 20th, 21st, 22nd, etc.... Now it becomes clear. To do what you want, you will need provide a list of all the possible core areas."

    Also, what other values could be placed in the employee's column other than
    SC - Shift Change
    1E - Emergency Holiday (1 day)
    0.5E - Emergency Holiday (0.5 day)
    OSP - Off site Paid
    S - Sick/Lateness
    Hol- Holiday

    Example: Shifts, vacation, etc.

    Also please indicate if all of the above and any others that will be considered a rest day. Would you be scheduling sick or lateness?? Would it be acceptable to have the employees names down the left column instead?

    Maud

  11. #11
    New Lounger
    Join Date
    Jul 2015
    Posts
    12
    Thanks
    2
    Thanked 0 Times in 0 Posts
    Hi Maud

    The core areas are

    Admin
    FLT
    CB
    Receiver
    Operative
    Despatch

    There will be no other values, going into the rows apart the ones listed below.

    SC - Shift Change
    1E - Emergency Holiday (1 day)
    0.5E - Emergency Holiday (0.5 day)
    OSP - Off site Paid
    S - Sick/Lateness
    1 - Holiday (day)
    0.5 - Holiday (0.5 day)
    RD - Rest Days

    No, i just need the following to happen when we chose the following 1 - Holiday (day), 0.5 - Holiday (0.5 day), RD - Rest Days

    Fred and John are on a rest day, but Mark wants the same day off. So I need a pop-up to say that there are two people on a rest day. This pop-up if possible, needs to be relating to the core area. In this example it will be FLT.

    Mark and John are on holiday on the same day, but Fred wants the same day off. So I need a pop-up to say that there are two people on holiday for that day. This pop-up if possible, needs to be relating to the core area. In this example it will be FLT.

    Is there a way of deleting the cell, if you have entered an holiday or a rest day and the pop-up has appeared?


    If possible, I would like the names go across the top.

    Thanks Kris
    Last edited by Kris McMillan; 2015-07-30 at 14:50.

  12. #12
    Gold Lounger Maudibe's Avatar
    Join Date
    Aug 2010
    Location
    Pa, USA
    Posts
    2,633
    Thanks
    115
    Thanked 647 Times in 590 Posts
    Hi Kris,

    Now that I fully understand what you want to do, here is the adjusted worksheet.

    HTH,
    Maud
    Attached Files Attached Files

  13. #13
    New Lounger
    Join Date
    Jul 2015
    Posts
    12
    Thanks
    2
    Thanked 0 Times in 0 Posts
    Hi Maudibe

    Thank you, for your time on this time and effort on this.

    Kris

  14. #14
    New Lounger
    Join Date
    Jul 2015
    Posts
    12
    Thanks
    2
    Thanked 0 Times in 0 Posts
    Maudibe

    Just one more question.

    It is possible for another pop-up box to appear, prompting the person to enter in a password to clear the box which relates to how people are on holiday, etc?

    Thanks

    Kris

  15. #15
    Gold Lounger Maudibe's Avatar
    Join Date
    Aug 2010
    Location
    Pa, USA
    Posts
    2,633
    Thanks
    115
    Thanked 647 Times in 590 Posts
    prompting the person to enter in a password to clear the box
    Yes, that is possible but which cells are you talking about Kris? Are you asking that once they are populated with a holiday, a password is needed to remove the holiday?

Page 1 of 2 12 LastLast

Posting Permissions

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