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]

Also I should mention, a message box is limited to 1024 characters. From test this gives approx. 24 lines (24 people worked a weekend or BH) in the message box, would you ever expect to see more than this?

 

[Chris Waller]

Alan,

I would be very surprised to see more than a couple of people working more than one or two Saturday's in the month and I have not yet come across anyone working a Bank Holiday, but that doesn't mean that it won't happen. But this is just a way of capturing anything that I may visually miss.

 

[Chris Waller]

Alan,

I tried this again, at home and I removed all the weekends and Bank Holidays an a Blank pop-up box appeared. Is it possible to input a phrase like "No weekends or Bank Holidays worked in this period"? I have tried several ways of doing this, but I haven't got it to work as yet.

Thanks

Chris

 

[Alan_P]

Hi Chris,

yes, add this just before "MsgBox (MsgTxt)"

If MsgTxt = "" then 
[COLOR=#333333]    MsgTxt = "No weekends or Bank Holidays Worked"
[/COLOR]end if

Cheers,
Alan.

 

[Chris Waller]

Hi Chris,

yes, add this just before "MsgBox (MsgTxt)"

If MsgTxt = "" then 
[COLOR=#333333]    MsgTxt = "No weekends or Bank Holidays Worked"
[/COLOR]end if

Cheers,
Alan.

Alan.

Thanks once again. I had forgotten to add If MsgTxt = "" Then before the line of text I had added i.e. MsgText = etc.

Thanks again for your help. You have made me very happy helping in this way.