Warning Message Pop-Up Box

[Chris Waller]

I have an Excel 2010 Spreadsheet. In Column A I have a list of names and in column B I have a list of dates. I would like to know if it is possible to create a pop-up box that will inform me if any of the names in Column A have worked on a Saturday, Sunday or any Bank Holidays. Column A is titled Name of Individual and Column B is titled Date of Activity.

I would like a pop-up box that will say something like:-

Fred Bloggs worked on Monday the 1st of January 2016 which is a Bank Holiday.
Fred Bloggs worked on Saturday the 30th December 2015. etc

I would ideally like this to be in VBA as I receive a large number of files containing similar data on a monthly basis. TIA

 

[Alan_P]

Hi Chris,

How would you like to trigger the pop up box?

This is a manually run message box;

Sub WorkedOn()
LastRw = Range("B" & Rows.Count).End(xlUp).Row
Bhols = Array("[COLOR=#ff0000]01/01/2016", "25/03/2016", "28/03/2016", "02/05/2016", "30/05/2016", "29/08/2016", "26/12/2016", "27/12/2016[/COLOR]")
For i = 2 To LastRw
    DayNum = Application.WorksheetFunction.Weekday(Range("B" & i), 2)
    If DayNum = 6 Then
        MsgTxt = MsgTxt & vbNewLine & Range("A" & i).Value & " worked on Saturday " & Format(Range("B" & i), "dd mmmm yyyy")
        GoTo Nrw
    ElseIf DayNum = 7 Then
        MsgTxt = MsgTxt & vbNewLine & Range("A" & i).Value & " worked on Sunday " & Format(Range("B" & i), "dd mmmm yyyy")
        GoTo Nrw
    End If
    For Each dy In Bhols
        If Range("B" & i).Value = DateValue(dy) Then
            MsgTxt = MsgTxt & vbNewLine & Range("A" & i).Value & " worked on " & Format(Range("B" & i), "dddd dd mmmm yyyy") & " which is a bank holiday"
        End If
    Next
Nrw:
Next
    
MsgBox (MsgTxt)
End Sub

You will have to change the bank holiday dates to your local ones (included are England & Wales Bank Holidays)

Hope this helps,
Cheers,
Alan.

 

[Chris Waller]

Alan,

Ideally I would like to be able to run the code by going into the Macro on the Developer Tab and run from there. TIA

 

[Chris Waller]

Alan,

I have come into work this morning and tried your VBA, however, it appears to be generating a run-time error '1004' Unable to get the weekday property of the WorksheetFunction class.

 

[Alan_P]

Hi Chris,

How are your dates formatted? Try just using the weekday formula in a cell and see what the result is?
I'll have a look when I get to work see if I can reproduce that error...

Cheers,
Alan

 

[Chris Waller]

Alan,

The field is formatted as Date *14/03/2001 and the Locale (Location) English (UK). I did note that when I used the =Weekday() formula Friday was shown as day 1 through to Thursday which is day 7. Perhaps if I cahnge your VBA to day 2 (Saturday) and day 3 (Sunday) that may work for me. Let me know what you think.

Thanks again.

 

[Chris Waller]

Alan,

It appears that there is a problem with the following line as the macro stops and highlights the following line.

daynum = Application.WorksheetFunction.Weekday(Range("B" & i), 2)

Thanks

 

[Chris Waller]

Alan,

I have jsut had another thought. I know the VBA looks for the last row in column B, however, there is data lower down column B that should not be included in the range. Column G is the best one to refer to as this is the correct length so the VBA needs to use the range in column G, but it needs to refer to column B that contains the dates.

 

[Alan_P]

Hi Chris,

The "2" at the end of the line changes it to 1 = Monday thru 7 = Sunday [so formula would be =Weekday(B1,2)]

Is cell B2 a date? and if not on what row do the dates start?

I assumed you would have a column heading in B1 so started the check at row 2, but if the date values don't start until for example row 5 you will need to change this line;

From;
For i = 2 To LastRw
To;
For i = 5 To LastRw

If it is a case where you will not be sure which row the dates start in I can do some code to deal with that.. I'll post it in a while..

Cheers,
Alan.

 

[Alan_P]

Alan,

I have jsut had another thought. I know the VBA looks for the last row in column B, however, there is data lower down column B that should not be included in the range. Column G is the best one to refer to as this is the correct length so the VBA needs to use the range in column G, but it needs to refer to column B that contains the dates.

Ah ok, that is definitely the problem!

So this amended code now uses column G for last row and also checks if the value is a date (just in case )

As it is now checking to see if it is a date first, if determining the last row for column G becomes a problem you should just be able to change it back to column B without it causing a problem...

Cheers,
Alan.

Sub WorkedOn()

LastRw = Range("[COLOR=#0000cd]G[/COLOR]" & Rows.Count).End(xlUp).Row
Bhols = Array("01/01/2016", "25/03/2016", "28/03/2016", "02/05/2016", "30/05/2016", "29/08/2016", "26/12/2016", "27/12/2016")

For i = 2 To LastRw
    [COLOR=#ff0000]If IsDate(Range("B" & i).Value) = False Then GoTo Nrw[/COLOR]
    
    DayNum = Application.WorksheetFunction.Weekday(Range("B" & i), 2)
    
    If DayNum = 6 Or DayNum = 7 Then
        MsgTxt = MsgTxt & vbNewLine & Range("A" & i).Value & " worked on " & Format(Range("B" & i), "dddd dd mmmm yyyy")
        GoTo Nrw
    End If
    For Each dy In Bhols
        If Range("B" & i).Value = DateValue(dy) Then
            MsgTxt = MsgTxt & vbNewLine & Range("A" & i).Value & " worked on " & Format(Range("B" & i), "dddd dd mmmm yyyy") & " which is a bank holiday"
        End If
    Next
Nrw:
Next
MsgBox (MsgTxt)
End Sub