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
179
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

PersonDate
abc30/05/2019
def29/05/2019
ghi30/05/2019
moh1/06/2019
dddd3/06/2019
xxxx1/06/2019
yyyy3/06/2019

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

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

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

same as
PersonDate
moh1/06/2019
xxxx1/06/2019

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


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

Excel Facts

Links? Where??
If Excel says you have links but you can't find them, go to Formulas, Name Manager. Look for old links to dead workbooks & delete.

My Aswer Is This

Well-known Member
Joined
Jul 5, 2014
Messages
17,555
Office Version
  1. 2013
Platform
  1. Windows
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
 

m_vishal_c

Board Regular
Joined
Dec 7, 2016
Messages
179
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
 

My Aswer Is This

Well-known Member
Joined
Jul 5, 2014
Messages
17,555
Office Version
  1. 2013
Platform
  1. Windows
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
 

m_vishal_c

Board Regular
Joined
Dec 7, 2016
Messages
179

ADVERTISEMENT

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:

m_vishal_c

Board Regular
Joined
Dec 7, 2016
Messages
179
hi, is there any way to close shape when i close that workbook?. heaps thanks
 

My Aswer Is This

Well-known Member
Joined
Jul 5, 2014
Messages
17,555
Office Version
  1. 2013
Platform
  1. Windows

ADVERTISEMENT

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

Board Regular
Joined
Dec 7, 2016
Messages
179
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

Well-known Member
Joined
Jul 5, 2014
Messages
17,555
Office Version
  1. 2013
Platform
  1. Windows
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>
 

Watch MrExcel Video

Forum statistics

Threads
1,129,310
Messages
5,635,468
Members
416,859
Latest member
GowthamiSita

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
Top