how to get notification for multiple values when today date comes near by in excel by vba

[m_vishal_c]

Hi

I have some data with dates, I need to get notification for all work which are having near by today's date by excel vba

for example

if today's date = date then those data will be popup on screen

this is my main file having these data

Person	Date
abc	30/05/2019
def	29/05/2019
ghi	30/05/2019
moh	1/06/2019
dddd	3/06/2019
xxxx	1/06/2019
yyyy	3/06/2019

<colgroup><col><col></colgroup><tbody>
</tbody>

if today's date=date(means 30/5/2019) then below data will popup on screen

Person	Date
abc	30/05/2019
ghi	30/05/2019

<colgroup><col><col></colgroup><tbody>
</tbody>

same as

Person	Date
moh	1/06/2019
xxxx	1/06/2019

<colgroup><col><col></colgroup><tbody>
</tbody>

how it is possible by vba, please let me know.
Heaps thanks

 

[My Aswer Is This]

Assuming your names is in column A and Date is in column B
And your looking for today's date.

Try this:

Application.ScreenUpdating = False
Dim i As Long
Dim Lastrow As Long
Dim ans As String
Lastrow = Cells(Rows.Count, "A").End(xlUp).Row
Dim anss As String
For i = 1 To Lastrow
    If Cells(i, 2).Value = Date Then ans = ans & Cells(i, 1).Value & vbTab & Cells(i, 2).Value & vbNewLine
Next
If ans <> "" Then
    anss = "These Persons need attention"
Else
    anss = "No one found for this Date  " & Date
End If
MsgBox anss & vbNewLine & ans
Application.ScreenUpdating = True
End Sub

 

[m_vishal_c]

Assuming your names is in column A and Date is in column B
And your looking for today's date.

Try this:

Application.ScreenUpdating = False
Dim i As Long
Dim Lastrow As Long
Dim ans As String
Lastrow = Cells(Rows.Count, "A").End(xlUp).Row
Dim anss As String
For i = 1 To Lastrow
    If Cells(i, 2).Value = Date Then ans = ans & Cells(i, 1).Value & vbTab & Cells(i, 2).Value & vbNewLine
Next
If ans <> "" Then
    anss = "These Persons need attention"
Else
    anss = "No one found for this Date  " & Date
End If
MsgBox anss & vbNewLine & ans
Application.ScreenUpdating = True
End Sub

Hi

Thanks for replying

is it possible to store those values in notepad and show result in notpad or something like that

thanks

 

[My Aswer Is This]

Try this:

Will put results in a shape:
I do not no how to add to Notepad

Sub Add_Shape()
'Modified  5/29/2019  3:51:31 PM  EDT
Dim i As Long
Dim Lastrow As Long
Dim ans As String
Lastrow = Cells(Rows.Count, "A").End(xlUp).Row
Dim anss As String
For i = 1 To Lastrow
    If Cells(i, 2).Value = Date Then ans = ans & Cells(i, 1).Value & vbTab & Cells(i, 2).Value & vbNewLine
Next
If ans <> "" Then
    anss = "These Persons need attention"
Else
    anss = "No one found for this Date  " & Date
End If
ActiveSheet.Shapes.AddTextbox(msoTextOrientationHorizontal, 1, 1, 1, 1).Select
With Selection.ShapeRange.TextFrame2
        .TextRange.Font.Size = 16
        .TextRange.ParagraphFormat.Alignment = msoAlignCenter
        .TextRange.Characters.Text = anss & vbNewLine & ans
        .TextRange.Font.Fill.ForeColor.RGB = RGB(0, 0, 0)
        .TextRange.Font.Bold = True
End With
With Selection
    .Left = Cells(5, 5).Left
    .Top = Cells(5, 5).Top
    .ShapeRange.Fill.ForeColor.RGB = RGB(0, 0, 255)
    .AutoSize = msoAutoSizeShapeToFitText
End With
End Sub

 

[m_vishal_c]

hi Thanks for replying. this is amazing , very impressive support. is it possible get reminder when we open windows or start computer instead of open this particular file. And another thing, this shape does not X button to close. if you can make something to close then it will be great Please let me know. thanks

 

[m_vishal_c]

hi, is there any way to close shape when i close that workbook?. heaps thanks

 

[My Aswer Is This]

I do not understand this:.
You said:

is it possible get reminder when we open windows or start computer instead of open this particular file.

You some how think this script should automatically run when you start your Computer.
Is that what your wanting and you want the script to run on some unknown Workbook?

I'm not sure how you would get that to work without a whole lot of extra code. And how do you think any script could do this on some Unknow Workbook​

You can just click on the shape and press delete key to delete shape
Are you sure you need a script to do this for you.​

 

[m_vishal_c]

I do not understand this:.
You said:

is it possible get reminder when we open windows or start computer instead of open this particular file.

You some how think this script should automatically run when you start your Computer.
Is that what your wanting and you want the script to run on some unknown Workbook?

I'm not sure how you would get that to work without a whole lot of extra code. And how do you think any script could do this on some Unknow Workbook​

You can just click on the shape and press delete key to delete shape
Are you sure you need a script to do this for you.​

we have to save this file and call from outside. i am not sure if this is possible by vba or not.
and second thing. i have to give this file to someone so if he/she closes this file then image/textbox should be removed.
thanks

 

[My Aswer Is This]

I think someone else will need to help you with your new requests.

You original post said:

if today's date = date then those data will be popup on screen ​

I provided a post which did that.
Then you said:

is it possible to store those values in notepad and show result in notpad or something like that​

Then you said:

hi Thanks for replying. this is amazing , very impressive support

But then you added more to your request.

I'm not able to help with these new requests.

But someone else here may be able to help with the rest of your request.

This is beyond my knowledgebase.
I will continue to monitor this thread to see what I can learn.
_{^{<strike>
</strike>}}​