Macro for a Pop-Up Box help....

Hi. I hope someone can help me.....

I have a spreadsheet which is used for a daily schedule of contact centre advisor staff. I want to create a macro that will throw up a pop up box when the sheet is opened using the date as a reference....

So on e worksheet named 'realtime' i have row 2 from cells B to AZ containing a date ie B2= 01/07/2007, C2= 02/07/2007 etc.

Column A has various 'headings' A4= Earlies, A5= Mids, A6= Lates, A7= Total

So under each date is the corresponding figures for the stuff in column A.

So if I open the sheet today (03/07/2007) I would want a pop up that shows this data....

Earlies + the data in D4
Mids + the data in D5
Lates + the data in D6
Total + the data in D7

But obviously it needs to reference today's date and show the figures under that date?

Hope this makes sense.

See the attached file.

I hope it's what you need.

Regards,
Antonio

Hi, Neil. See if this will work for you. Copy this procedure into the ThisWorkbook module of your workbook:

Please Login or Register  to view this content.

HTH

Jason

Hi Jason, yours seems to work, but it is saying it cannot find the date. Does the date need to be specified anywhere? Or should it take it from the system?

Are the dates listed in B2:AZ2? If so, are they actual dates, or text strings? The variable 'Date' does not need to be defined; it is similar to the TODAY() worksheet function.

Yes, sorry that does work.... However I need it to show the wording in Column A?

So it should read

Earlies: 3
Mid: 6
Lates: 3
Total: 12

It should show the contents of cells A4:A7 before each of the amounts. Is this the correct range?

Hi Jason.... It's showing the values fine, but the Text in A4-A7 is not being shown at all?

Thanks for your help by the way....

Hi, Neil. No problem at all.

I'm puzzled, though. This code should show the contents of cells A4:A7:

Please Login or Register  to view this content.

Cells(i + 3, 1), when i = 1, would the the same as saying Range("A4"). Would it be possible to post up a zipped copy of the workbook?

Jason

Well I'm a bit clueless to be honest. I'll attach...

Password is 'schedule'

I've extended the date range btw. Thats the only thing I've touched.

it's a link as it exceeds file sazi thingy...

http://www.neilcarden.co.uk/Shift.zip

The problem is that the "front" sheet is activated, and I didn't fully qualify the range of column A. Change the above loop to the following:

Please Login or Register  to view this content.

This will also account for the fact that you don't have a "Totals" row at A7 any more.

Jason

Yes, that works amazingly!!!

Thanks for your help Jason!

No problem, Neil.