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.

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

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


In article ,
neilcarden wrote:

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.