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

[neilcarden]

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

I have a spreadsheet which is used for a daily schedule of contac
centre advisor staff. I want to create a macro that will throw up a po
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 A
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 colum
A.

So if I open the sheet today (03/07/2007) I would want a pop up tha
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 figure
under that date?

Hope this makes sense

 

[JE McGimpsey]

One way:

Put this in your ThisWorkbook code module:

Private Sub Workbook_Open()
Const nTAB As Long = 15 'num chars to align on
Dim nOffset As Long
Dim i As Long
Dim sMsg As String
Dim sPad As String
sPad = Space(nTAB)
With Worksheets("Sheet3")
If Date >= .Range("B2").Value And Date <= .Range("AZ2").Value Then
nOffset = Date - .Range("B2").Value + 2
For i = 4 To 7
sMsg = sMsg & vbNewLine & Left$(.Cells(i, 1) & sPad, _
Len(sPad)) & .Cells(i, nOffset)
Next i
MsgBox Mid(sMsg, 2)
End If
End With
End Sub