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

m_vishal_c

Board Regular
Joined
Dec 7, 2016
Messages
209
Office Version
  1. 365
  2. 2016
Platform
  1. Windows
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

[TABLE="width: 139"]
<colgroup><col><col></colgroup><tbody>[TR]
[TD]Person[/TD]
[TD]Date[/TD]
[/TR]
[TR]
[TD]abc[/TD]
[TD="align: right"]30/05/2019[/TD]
[/TR]
[TR]
[TD]def[/TD]
[TD="align: right"]29/05/2019[/TD]
[/TR]
[TR]
[TD]ghi[/TD]
[TD="align: right"]30/05/2019[/TD]
[/TR]
[TR]
[TD]moh[/TD]
[TD="align: right"]1/06/2019[/TD]
[/TR]
[TR]
[TD]dddd[/TD]
[TD="align: right"]3/06/2019[/TD]
[/TR]
[TR]
[TD]xxxx[/TD]
[TD="align: right"]1/06/2019[/TD]
[/TR]
[TR]
[TD]yyyy[/TD]
[TD="align: right"]3/06/2019[/TD]
[/TR]
</tbody>[/TABLE]

if today's date=date(means 30/5/2019) then below data will popup on screen
[TABLE="width: 139"]
<colgroup><col><col></colgroup><tbody>[TR]
[TD]Person[/TD]
[TD]Date[/TD]
[/TR]
[TR]
[TD]abc[/TD]
[TD="align: right"]30/05/2019[/TD]
[/TR]
[TR]
[TD]ghi[/TD]
[TD="align: right"]30/05/2019[/TD]
[/TR]
</tbody>[/TABLE]

same as
[TABLE="width: 139"]
<colgroup><col><col></colgroup><tbody>[TR]
[TD]Person[/TD]
[TD]Date[/TD]
[/TR]
[TR]
[TD]moh[/TD]
[TD="align: right"]1/06/2019[/TD]
[/TR]
[TR]
[TD]xxxx[/TD]
[TD="align: right"]1/06/2019[/TD]
[/TR]
</tbody>[/TABLE]


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

Excel Facts

Using Function Arguments with nested formulas
If writing INDEX in Func. Arguments, type MATCH(. Use the mouse to click inside MATCH in the formula bar. Dialog switches to MATCH.
Assuming your names is in column A and Date is in column B
And your looking for today's date.

Try this:

Code:
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
 
Upvote 0
Assuming your names is in column A and Date is in column B
And your looking for today's date.

Try this:

Code:
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
 
Upvote 0
Try this:

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

Code:
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
 
Upvote 0
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
 
Last edited:
Upvote 0
hi, is there any way to close shape when i close that workbook?. heaps thanks
 
Upvote 0
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.
 
Upvote 0
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
 
Upvote 0
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>
 
Upvote 0

Forum statistics

Threads
1,223,164
Messages
6,170,444
Members
452,326
Latest member
johnshaji

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top