names and dates of birth of persons having a birthday on that day from Excel automatically to screen when starting up PC
-
- NewLounger
- Posts: 22
- Joined: 02 Jun 2011, 21:46
names and dates of birth of persons having a birthday on that day from Excel automatically to screen when starting up PC
I am using W10 with Office 2016.
In a folder called "birthdays" I have an EXCEL file called "today" in wich in sheet 1 from row 2 down a number of names are listed in column A and the corresponding dates of birth in column B.
In column C the formula is
= IF (AND ((MONTH (B2) = MONTH (NOW ())), (DAY (B2) = DAY (NOW ()))), "birthday", "")
In that file it happens that 4 people have their birthday on the same day; 5 might also occur in the future.
When I start my computer, I would like to see the names of those who have their birthday on that day appear on my opening screen, each followed by the date of birth.
That requires interaction between Windows and Excel, but I don't know how it can be accomplished.
Thanks in advance for your help.
In a folder called "birthdays" I have an EXCEL file called "today" in wich in sheet 1 from row 2 down a number of names are listed in column A and the corresponding dates of birth in column B.
In column C the formula is
= IF (AND ((MONTH (B2) = MONTH (NOW ())), (DAY (B2) = DAY (NOW ()))), "birthday", "")
In that file it happens that 4 people have their birthday on the same day; 5 might also occur in the future.
When I start my computer, I would like to see the names of those who have their birthday on that day appear on my opening screen, each followed by the date of birth.
That requires interaction between Windows and Excel, but I don't know how it can be accomplished.
Thanks in advance for your help.
-
- Administrator
- Posts: 78457
- Joined: 16 Jan 2010, 00:14
- Status: Microsoft MVP
- Location: Wageningen, The Netherlands
Re: names and dates of birth of persons having a birthday on that day from Excel automatically to screen when starting u
A belated welcome to Eileen's Lounge!
I don't know how to do that.
You could have the workbook open automatically each time Windows is started, and have it display a message box if there are birthdays today.
To do so:
1) Create the following code in the ThisWorkbook module of your workbook:
2) Save the workbook as a macro-enabled workbook (.xlsm).
3) Move it to the Windows startup folder %appdata%\Microsoft\Windows\Start Menu\Programs\Startup
See the attached sample workbook.
Alternatively, you could create contacts in Outlook and enter their birth dates. Outlook can automatically pop up a reminder on the birthday itself, or before it if you prefer. See Een verjaardag of speciale datum toevoegen voor een contactpersoon
I don't know how to do that.
You could have the workbook open automatically each time Windows is started, and have it display a message box if there are birthdays today.
To do so:
1) Create the following code in the ThisWorkbook module of your workbook:
Code: Select all
Private Sub Workbook_Open()
Dim r As Long
Dim m As Long
Dim s As String
With Worksheets("Verjaardagen")
m = .Range("A" & .Rows.Count).End(xlUp).Row
For r = 2 To m
If .Range("C" & r).Value = "birthday" Then
s = s & vbCrLf & .Range("A" & r).Value
End If
Next r
End With
If s <> "" Then
MsgBox "Vandaag zijn jarig:" & s, vbInformation
End If
End Sub
3) Move it to the Windows startup folder %appdata%\Microsoft\Windows\Start Menu\Programs\Startup
See the attached sample workbook.
Alternatively, you could create contacts in Outlook and enter their birth dates. Outlook can automatically pop up a reminder on the birthday itself, or before it if you prefer. See Een verjaardag of speciale datum toevoegen voor een contactpersoon
You do not have the required permissions to view the files attached to this post.
Best wishes,
Hans
Hans
-
- NewLounger
- Posts: 22
- Joined: 02 Jun 2011, 21:46
Re: names and dates of birth of persons having a birthday on that day from Excel automatically to screen when starting u
It works, very nice Hans, thank you! I never thought of this solution. I now realize that I was going way too far wanting to do it from windows.
At start-up I get the whole Excel sheet plus the message box, because of which Excel can only continue when the massage box is closed.
Is it possible to display only the message box? (I guess not)
Perhaps by copying the message box to Word, closing box and Excel and putting the box displayed in Word on the screen? Or get the content (the name of the person who has his birthday) on the taskbar at the bottom of the screen?
At start-up I get the whole Excel sheet plus the message box, because of which Excel can only continue when the massage box is closed.
Is it possible to display only the message box? (I guess not)
Perhaps by copying the message box to Word, closing box and Excel and putting the box displayed in Word on the screen? Or get the content (the name of the person who has his birthday) on the taskbar at the bottom of the screen?
-
- Administrator
- Posts: 78457
- Joined: 16 Jan 2010, 00:14
- Status: Microsoft MVP
- Location: Wageningen, The Netherlands
Re: names and dates of birth of persons having a birthday on that day from Excel automatically to screen when starting u
Here is a variation - see if you like it better.
You do not have the required permissions to view the files attached to this post.
Best wishes,
Hans
Hans
-
- NewLounger
- Posts: 22
- Joined: 02 Jun 2011, 21:46
Re: names and dates of birth of persons having a birthday on that day from Excel automatically to screen when starting u
Your first solution is more convenient, because it must be deliberately removed by clicking on the message box, so that there must have been a conscious consideration of what the message was.
By the way, your answers appear as fast as lightning, my compliments!
By the way, your answers appear as fast as lightning, my compliments!
-
- NewLounger
- Posts: 22
- Joined: 02 Jun 2011, 21:46
Re: names and dates of birth of persons having a birthday on that day from Excel automatically to screen when starting u
After these lines of code:
End With
If s <> "" Then
a new Word document can be started with this line in the macro:
Application.ActivateMicrosoftApp xlMicrosoftWord
Now code should follow that transfers the value of variable s to the Word document, after which execution returns to the macro to do the final steps.
Your help is welcome!
End With
If s <> "" Then
a new Word document can be started with this line in the macro:
Application.ActivateMicrosoftApp xlMicrosoftWord
Now code should follow that transfers the value of variable s to the Word document, after which execution returns to the macro to do the final steps.
Your help is welcome!
-
- Administrator
- Posts: 78457
- Joined: 16 Jan 2010, 00:14
- Status: Microsoft MVP
- Location: Wageningen, The Netherlands
Re: names and dates of birth of persons having a birthday on that day from Excel automatically to screen when starting u
Try this version:
Code: Select all
Private Sub Workbook_Open()
Dim r As Long
Dim m As Long
Dim s As String
Dim wrdApp As Object
Dim wrdDoc As Object
With Worksheets("Verjaardagen")
m = .Range("A" & .Rows.Count).End(xlUp).Row
For r = 2 To m
If .Range("C" & r).Value = "birthday" Then
s = s & vbCr & .Range("A" & r).Value
End If
Next r
End With
If s <> "" Then
On Error Resume Next
Set wrdApp = GetObject(Class:="Word.Application")
If wrdApp Is Nothing Then
Set wrdApp = CreateObject(Class:="Word.Application")
wrdApp.Visible = True
End If
On Error GoTo 0
Set wrdDoc = wrdApp.Documents.Add
wrdDoc.Content.InsertAfter "Vandaag zijn jarig:" & s
wrdApp.Activate
End If
End Sub
Best wishes,
Hans
Hans
-
- NewLounger
- Posts: 22
- Joined: 02 Jun 2011, 21:46
Re: names and dates of birth of persons having a birthday on that day from Excel automatically to screen when starting u
Thanks again Hans, it now works like a charm!
I'm glad to know now about the wrdApp and wrdDoc code.
I tried to find something myself by studying John Walkenbachs book "Excel 2010 Bible" (in Dutch: "Excel 2010 het complete HANDBoek") but to no avail, I'm too long out of training, I guess.
I'm glad to know now about the wrdApp and wrdDoc code.
I tried to find something myself by studying John Walkenbachs book "Excel 2010 Bible" (in Dutch: "Excel 2010 het complete HANDBoek") but to no avail, I'm too long out of training, I guess.
-
- Administrator
- Posts: 78457
- Joined: 16 Jan 2010, 00:14
- Status: Microsoft MVP
- Location: Wageningen, The Netherlands
Re: names and dates of birth of persons having a birthday on that day from Excel automatically to screen when starting u
Controlling one application (Word in this case) from another (Excel) using code is called Automation.
Best wishes,
Hans
Hans
-
- NewLounger
- Posts: 22
- Joined: 02 Jun 2011, 21:46
Re: names and dates of birth of persons having a birthday on that day from Excel automatically to screen when starting u
I made minor adjustments Hans, see below "Else" and "ActiveWorkbook.Save". At the end (after saving the workbook) I want the workbook to close (disappear from the screen) and than the Word document to open (to appear on the screen). I couldn't come up with the proper code, so help again please.
Jack.
Jack.
Code: Select all
Private Sub Workbook_Open()
'by HansV » 29 Oct 2020, 11:24
'https://eileenslounge.com/viewtopic.php?f=27&t=35575&p=276555#p276555
Dim r As Long
Dim m As Long
Dim s As String
Dim wrdApp As Object
Dim wrdDoc As Object
With Worksheets("Verjaardagen")
m = .Range("A" & .Rows.Count).End(xlUp).Row
For r = 2 To m
If .Range("C" & r).Value = "birthday" Then
s = s & vbCr & .Range("A" & r).Value
End If
Next r
End With
If s <> "" Then
On Error Resume Next
Set wrdApp = GetObject(Class:="Word.Application")
If wrdApp Is Nothing Then
Set wrdApp = CreateObject(Class:="Word.Application")
wrdApp.Visible = True
End If
On Error GoTo 0
Set wrdDoc = wrdApp.Documents.Add
wrdDoc.Content.InsertAfter "Vandaag zijn jarig:" & s
wrdApp.Activate
Else
On Error Resume Next
Set wrdApp = GetObject(Class:="Word.Application")
If wrdApp Is Nothing Then
Set wrdApp = CreateObject(Class:="Word.Application")
wrdApp.Visible = True
End If
On Error GoTo 0
Set wrdDoc = wrdApp.Documents.Add
wrdDoc.Content.InsertAfter "Vandaag geen jarigen." & s
wrdApp.Activate
End If
ActiveWorkbook.Save
End Sub
-
- Administrator
- Posts: 78457
- Joined: 16 Jan 2010, 00:14
- Status: Microsoft MVP
- Location: Wageningen, The Netherlands
Re: names and dates of birth of persons having a birthday on that day from Excel automatically to screen when starting u
Be aware that it'll be difficult to edit the workbook, since it will be closed immmediately after you open it...
Code: Select all
Private Sub Workbook_Open()
'by HansV » 29 Oct 2020, 11:24
'https://eileenslounge.com/viewtopic.php?f=27&t=35575&p=276555#p276555
Dim r As Long
Dim m As Long
Dim s As String
Dim wrdApp As Object
Dim wrdDoc As Object
With Worksheets("Verjaardagen")
m = .Range("A" & .Rows.Count).End(xlUp).Row
For r = 2 To m
If .Range("C" & r).Value = "birthday" Then
s = s & vbCr & .Range("A" & r).Value
End If
Next r
End With
On Error Resume Next
Set wrdApp = GetObject(Class:="Word.Application")
If wrdApp Is Nothing Then
Set wrdApp = CreateObject(Class:="Word.Application")
wrdApp.Visible = True
End If
On Error GoTo 0
Set wrdDoc = wrdApp.Documents.Add
If s <> "" Then
wrdDoc.Content.InsertAfter "Vandaag zijn jarig:" & s
Else
wrdDoc.Content.InsertAfter "Vandaag geen jarigen."
End If
wrdApp.Activate
Me.Close SaveChanges:=True
End Sub
Best wishes,
Hans
Hans
-
- NewLounger
- Posts: 22
- Joined: 02 Jun 2011, 21:46
Re: names and dates of birth of persons having a birthday on that day from Excel automatically to screen when starting u
When I open it in te "normal" way from C:\Users\Boven\AppData\Roaming\Microsoft\Windows\Start Menu\Programs\Startup it will stay on the screen I suppose? That's important for adding persons to the list.
-
- Administrator
- Posts: 78457
- Joined: 16 Jan 2010, 00:14
- Status: Microsoft MVP
- Location: Wageningen, The Netherlands
Re: names and dates of birth of persons having a birthday on that day from Excel automatically to screen when starting u
You'll have to do the following:
- Start Excel.
- Activate the Visual Basic Editor.
- Activate the Immediate window (Ctrl+G).
- Type or copy/paste the following line and (with the insertion point in that line) press Enter:
- Switch to Excel.
- Open the workbook.
- When you're done, activate the Visual Basic Editor.
- Activate the Immediate window (Ctrl+G).
- Type or copy/paste the following line and (with the insertion point in that line) press Enter:
- Switch back to Excel.
- Start Excel.
- Activate the Visual Basic Editor.
- Activate the Immediate window (Ctrl+G).
- Type or copy/paste the following line and (with the insertion point in that line) press Enter:
Code: Select all
Application.EnableEvents = False
- Open the workbook.
- When you're done, activate the Visual Basic Editor.
- Activate the Immediate window (Ctrl+G).
- Type or copy/paste the following line and (with the insertion point in that line) press Enter:
Code: Select all
Application.EnableEvents = True
Best wishes,
Hans
Hans
-
- Administrator
- Posts: 12604
- Joined: 16 Jan 2010, 15:49
- Location: London, Europe
Re: names and dates of birth of persons having a birthday on that day from Excel automatically to screen when starting u
And, of course, if you need to do this frequently you could write a short VBA macro to automate these steps.HansV wrote: ↑31 Oct 2020, 13:57You'll have to do the following:
- Start Excel.
- Activate the Visual Basic Editor.
- Activate the Immediate window (Ctrl+G).
- Type or copy/paste the following line and (with the insertion point in that line) press Enter:
- Switch to Excel.Code: Select all
Application.EnableEvents = False
- Open the workbook.
- When you're done, activate the Visual Basic Editor.
- Activate the Immediate window (Ctrl+G).
- Type or copy/paste the following line and (with the insertion point in that line) press Enter:
- Switch back to Excel.Code: Select all
Application.EnableEvents = True
StuartR
-
- Administrator
- Posts: 78457
- Joined: 16 Jan 2010, 00:14
- Status: Microsoft MVP
- Location: Wageningen, The Netherlands
Re: names and dates of birth of persons having a birthday on that day from Excel automatically to screen when starting u
Yes, indeed!
Best wishes,
Hans
Hans
-
- NewLounger
- Posts: 22
- Joined: 02 Jun 2011, 21:46
Re: names and dates of birth of persons having a birthday on that day from Excel automatically to screen when starting u
I'm at a loss Hans because following your instructions the way I did results in what you see below.
You'll have to do the following:
Start Excel.
That will result in the ribbons on my screen, nothing below that (no worksheet).
- Activate the Visual Basic Editor.
Therefore I go to menu item "Developers" of the ribbon and there I click on the icon "Visual Basic" That will open PERSNLK.XLS (PERSOONLIJK. xls) at nr. 93 of 100 modules. Always at that nr. 93 called "Swap", maybe because it has the shortes name (4 characters) of all?
- Activate the Immediate window (Ctrl+G).
- Type or copy/paste the following line and (with the insertion point in that line) press Enter:
CODE: SELECT ALL
Application.EnableEvents = False
I don't believe it is useful to put that in the immediate window of module Swap of personal.xls. Maybe I should have opened Today.xlsm after the start of Excel? That's what I now do, when I repeat the above intructions that wil put the two lines of code in the immediate window of "module 1" where I type:
CODE: SELECT ALL
Application.EnableEvents = False
- Switch to Excel.
Therefore I click on the leftmost icon (the characteristic Excel icon) in the top row at my screen. That will (again) result in the ribbons on my screen, nothing below that (no worksheet).
- Open the workbook.
Off course opening Excel results showing Today.xlsm again at sheet "Verjaardagen".
- When you're done, activate the Visual Basic Editor.
That again brings me to the immediate window of module 1.
- Activate the Immediate window (Ctrl+G).
No need for that because I'm already there.
- Type or copy/paste the following line and (with the insertion point in that line) press Enter:
CODE: SELECT ALL
Application.EnableEvents = True
That now immediately follows the previous lines of code so in that module are now tese 4 lines of code:
CODE: SELECT ALL
Application.EnableEvents = False
CODE: SELECT ALL
Application.EnableEvents = True
- Switch back to Excel
That results in showing Today.xlsm again at sheet "Verjaardagen".
You'll have to do the following:
Start Excel.
That will result in the ribbons on my screen, nothing below that (no worksheet).
- Activate the Visual Basic Editor.
Therefore I go to menu item "Developers" of the ribbon and there I click on the icon "Visual Basic" That will open PERSNLK.XLS (PERSOONLIJK. xls) at nr. 93 of 100 modules. Always at that nr. 93 called "Swap", maybe because it has the shortes name (4 characters) of all?
- Activate the Immediate window (Ctrl+G).
- Type or copy/paste the following line and (with the insertion point in that line) press Enter:
CODE: SELECT ALL
Application.EnableEvents = False
I don't believe it is useful to put that in the immediate window of module Swap of personal.xls. Maybe I should have opened Today.xlsm after the start of Excel? That's what I now do, when I repeat the above intructions that wil put the two lines of code in the immediate window of "module 1" where I type:
CODE: SELECT ALL
Application.EnableEvents = False
- Switch to Excel.
Therefore I click on the leftmost icon (the characteristic Excel icon) in the top row at my screen. That will (again) result in the ribbons on my screen, nothing below that (no worksheet).
- Open the workbook.
Off course opening Excel results showing Today.xlsm again at sheet "Verjaardagen".
- When you're done, activate the Visual Basic Editor.
That again brings me to the immediate window of module 1.
- Activate the Immediate window (Ctrl+G).
No need for that because I'm already there.
- Type or copy/paste the following line and (with the insertion point in that line) press Enter:
CODE: SELECT ALL
Application.EnableEvents = True
That now immediately follows the previous lines of code so in that module are now tese 4 lines of code:
CODE: SELECT ALL
Application.EnableEvents = False
CODE: SELECT ALL
Application.EnableEvents = True
- Switch back to Excel
That results in showing Today.xlsm again at sheet "Verjaardagen".
-
- Administrator
- Posts: 12604
- Joined: 16 Jan 2010, 15:49
- Location: London, Europe
Re: names and dates of birth of persons having a birthday on that day from Excel automatically to screen when starting u
You should not be adding Application.EnableEvents to ANY code module. The immediate window is not associated with a workbook, it simply executes whatever code you enter as a single line and shows you the output.
When you type Application.EnableEvents in to the immediate window and press enter, this stops Excel from processing events. This means that when you open your workbook, the workbook_open event won't run.
When you type Application.EnableEvents in to the immediate window and press enter, this stops Excel from processing events. This means that when you open your workbook, the workbook_open event won't run.
StuartR
-
- Administrator
- Posts: 78457
- Joined: 16 Jan 2010, 00:14
- Status: Microsoft MVP
- Location: Wageningen, The Netherlands
Re: names and dates of birth of persons having a birthday on that day from Excel automatically to screen when starting u
It's strange that you don't see a new blank workbook when you start Excel. But apart from that:
The Imnediate window in the Visual Basic Editor does not belong to any particular workbook. It is a kind of scratch pad in which you can enter commands to be executed immediately (hence the name of the window).
If you would open Today.xlsm the normal way, it would execute the Workbook_Open event procedure. This would create a Word document, then close Today.xlsm. So it would be impossible to edit the list of birthdays or the code - you'd never get a chance to do that.
If you want to edit the list of birthdays or the code, you have to disable the Workbook_Open event (and all other events) temporarily. To do that, you execute the line
Application.EnableEvents = False
in the Immediate window. When you open Today.xlsm after that, the Workbook_Open event procedure will not be executed, and - as you have found - you can view and edit this workbook.
Events will remain disabled until you quit Excel, unless you explicitly enable them again. So if you want to test whether the changes to Today.xlsm work the way you want without quitting and restarting Excel, you must enable events by executing
Application.EnableEvents = True
from the Immediate window. You can do this by entering this line, or by changing False to True in the already existing line.
Remember, you execute a line in the Immediate window by clicking anywhere in that line and pressing Enter.
The Imnediate window in the Visual Basic Editor does not belong to any particular workbook. It is a kind of scratch pad in which you can enter commands to be executed immediately (hence the name of the window).
If you would open Today.xlsm the normal way, it would execute the Workbook_Open event procedure. This would create a Word document, then close Today.xlsm. So it would be impossible to edit the list of birthdays or the code - you'd never get a chance to do that.
If you want to edit the list of birthdays or the code, you have to disable the Workbook_Open event (and all other events) temporarily. To do that, you execute the line
Application.EnableEvents = False
in the Immediate window. When you open Today.xlsm after that, the Workbook_Open event procedure will not be executed, and - as you have found - you can view and edit this workbook.
Events will remain disabled until you quit Excel, unless you explicitly enable them again. So if you want to test whether the changes to Today.xlsm work the way you want without quitting and restarting Excel, you must enable events by executing
Application.EnableEvents = True
from the Immediate window. You can do this by entering this line, or by changing False to True in the already existing line.
Remember, you execute a line in the Immediate window by clicking anywhere in that line and pressing Enter.
Best wishes,
Hans
Hans