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

**neilcarden** · 07-03-2007, 08:41 AM

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.

**antoka05** · 07-03-2007, 09:28 AM

See the attached file.

I hope it's what you need.

Regards,
Antonio

**jasoncw** · 07-03-2007, 09:29 AM

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

Private Sub Workbook_Open()
    Dim Rng As Range, dRng As Range, msg As String, i As Integer
    
    Set Rng = Worksheets("realtime").Range("B2:AZ2")
    
    Set dRng = Rng.Find(Date)
    
    If dRng Is Nothing Then
        MsgBox "Today's date not found."
        Exit Sub
    End If
    
    For i = 1 To 4
        msg = msg & Cells(i + 3, 1) & "  " & dRng.Offset(i) & vbCrLf
    Next i
    
    MsgBox msg, , Format(Date, "d/m/yyyy")
End Sub

HTH

Jason

**neilcarden** · 07-03-2007, 09:59 AM

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?

**jasoncw** · 07-03-2007, 10:03 AM

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.