Latest news msg box

[AlexGill89]

Hi all, I have an Excel document that I use to showcase upcoming events to other users. I'd like events that I've added recently to pop up in a message box when the document is opened. I have a recently added colum with the date that the event is added followed by a colum with the event details, it's the details that I'd like to pop up in the msg box on opening the document. I will only ever have 3 or 4 things that need to be in the msg pop up, Preferably in one box. Any help or direction would be appreciated as my vba knowledge is limited.

 

[footoo]

How to determine what events to put in the message box?
What is the sheet name?
The event details are in what column(s)?
Post some sample data and what you would like to see in the message box.

 

[AlexGill89]

C14-C35 D-14-D35 E14-E35 F14-F35

Added	Occurs	Upcoming Events Information/Flyer	Click To Sign Up
25/07/18	27/07/18	Events Info - This is what I would like displaying in the msg box on open if recently added	Designer Outlet - interview sheet

<tbody>
</tbody>

The Added Cell shows when the event was added. If the date is today or the following day I would like it to be treated as "recently added" I'd like the text displaying from the cell in the E column as shown above. I could have a cell with today's date in for example E4 if this would help.

 

[footoo]

Put in the ThisWorkbook module :

Private Sub Workbook_Open()
Dim rng As Range, cel As Range, msg$
Set rng = [C14:C35]
For Each cel In rng
    If cel = Date Or cel = Date - 1 Then _
    msg = msg & cel(1, 3) & Chr(13) & Chr(13)
Next
MsgBox msg
End Sub

 

[AlexGill89]

That's great! Just what I needed. Can I name the message box Recently added events? Regards Alex

 

[AlexGill89]

Sorry about the double post can I bold the text at all. I need it to stand out more.

 

[footoo]

Formatting the message box text would involve some complicated programming.
It would be easier to use a User Form to do this instead of a message box, but this would involve you in a lot more steps to implement (and probably not worth the effort).
See if the following is suitable :

Private Sub Workbook_Open()
Dim rng As Range, cel As Range, msg$
Set rng = [C14:C35]
For Each cel In rng
    If cel = Date Or cel = Date - 1 Then _
    msg = msg & cel(1, 3) & Chr(13) & Chr(13)
Next
MsgBox msg, vbInformation, "RECENTLY ADDED EVENTS"
End Sub

 

[My Aswer Is This]

Try this:

Now I have a ' symbol in front of first line of code which means this line will not execute.
I have this script only running when you add this script to a button.
Try this first like this and see if it does what you want.

If it works remove the ' symbol and the next line of code which says:

Sub My_Message()
Now the script will run when you open the workbook.
​

Install the code the same way you did with earlier code provided that did partially what you wanted.

'Private Sub Workbook_Open()
Sub My_Message()
'Modified  7/26/2018  1:04:04 AM  EDT
Dim c As Range
Dim ans As String
For Each c In Range("C14:C35")
If c.Value = Date Or c.Value = Date - 1 Then ans = ans & c.Offset(, 2).Value & vbNewLine
Next
ActiveSheet.Shapes.AddTextbox(msoTextOrientationHorizontal, 435, 90.75, 177.75, 102).Select
    Selection.ShapeRange.Fill.ForeColor.RGB = RGB(255, 255, 0)
    Selection.Left = Cells(14, "G").Left
    Selection.Top = Cells(14, "G").Top
    ShapeName = Selection.Name
   
    With Selection.ShapeRange.TextFrame2
        .TextRange.Font.Size = 16
        
        .TextRange.ParagraphFormat.Alignment = msoAlignCenter
        .TextRange.Characters.Text = "Recently added events" & vbNewLine & ans
        .TextRange.Font.Fill.ForeColor.RGB = RGB(0, 0, 255)
        .TextRange.Font.Bold = True
        .AutoSize = msoAutoSizeShapeToFitText
    End With
Selection.AutoSize = msoAutoSizeShapeToFitText
End Sub

 

[My Aswer Is This]

After viewing my Textbox with the message. Either click on it and delete it or move it to another location.
Otherwise it will be over written next time you run the script.

 

[footoo]

Another possibility in addition to, or instead of, a message box is conditional formatting of E14:E45
CF formula : =OR(C14=TODAY(),C14=TODAY()-1)