names and dates of birth of persons having a birthday on that day from Excel automatically to screen when starting up PC

sonsjack
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

Post by sonsjack »

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.

User avatar
HansV
Administrator
Posts: 78234
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

Post by HansV »

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:

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

Today.xlsm

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

sonsjack
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

Post by sonsjack »

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?

User avatar
HansV
Administrator
Posts: 78234
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

Post by HansV »

Here is a variation - see if you like it better.

Today.xlsm
You do not have the required permissions to view the files attached to this post.
Best wishes,
Hans

sonsjack
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

Post by sonsjack »

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!

sonsjack
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

Post by sonsjack »

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!

User avatar
HansV
Administrator
Posts: 78234
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

Post by HansV »

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

sonsjack
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

Post by sonsjack »

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.

User avatar
HansV
Administrator
Posts: 78234
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

Post by HansV »

Controlling one application (Word in this case) from another (Excel) using code is called Automation.
Best wishes,
Hans

sonsjack
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

Post by sonsjack »

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.

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

User avatar
HansV
Administrator
Posts: 78234
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

Post by HansV »

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

sonsjack
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

Post by sonsjack »

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.

User avatar
HansV
Administrator
Posts: 78234
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

Post by HansV »

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:

Code: Select all

Application.EnableEvents = False
- 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:

Code: Select all

Application.EnableEvents = True
- Switch back to Excel.
Best wishes,
Hans

User avatar
StuartR
Administrator
Posts: 12577
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

Post by StuartR »

HansV wrote:
31 Oct 2020, 13:57
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:

Code: Select all

Application.EnableEvents = False
- 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:

Code: Select all

Application.EnableEvents = True
- Switch back to Excel.
And, of course, if you need to do this frequently you could write a short VBA macro to automate these steps.
StuartR


User avatar
HansV
Administrator
Posts: 78234
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

Post by HansV »

Yes, indeed!
Best wishes,
Hans

sonsjack
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

Post by sonsjack »

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".

User avatar
StuartR
Administrator
Posts: 12577
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

Post by StuartR »

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.
StuartR


User avatar
HansV
Administrator
Posts: 78234
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

Post by HansV »

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.
Best wishes,
Hans