Having a Message "Anniversary" on the userform if the HireDate equals Current Date?

[JJFletcher]

Hi,

In the Userform, is it possible to have a flashing text popup or message pop when the Hiredate equals the current date saying Anniversary??

HR Dashboard Master V3.xlsm

John

[Leith Ross]

Hello John,

Formulas can not flash text, manipulate shapes, display dialogs, or control other Excel objects. You would need to create a VBA UserForm to replace what is on the worksheet.

[Pepe Le Mokko]

Be aware that flashing cells might cause epileptic reactions with some people ( no joke)

[JJFletcher]

Hi - So it's not possible to have the field date see if the current date is a match and a popup message launches and says Anniversary

hhhmmmm....

John

[JJFletcher]

Is it possible to do an if statement?

[Jim885]

Here's something that might work for you, but you will have to change the cell values to meet your needs.
It was written to suit just one employee/situation. You will just have to modify the code to loop through a list of employees that you have.

Assuming Hire Date is in cell A2 and Employee name is in cell C2, put this in the ThisWorkbook module;

Please Login or Register  to view this content.

[JJFletcher]

Can this be used for the Workbook that is attached in this thread?

[JJFletcher]

Please Login or Register  to view this content.

I do not think I understand the sequence of how to write this! The field that is needing to be accessed is in the EmployeeData Spreadsheet and the Userform has the textbox that is linked to the filed in the userform

John

[Jim885]

John,

As written, this is a standard macro that will suit your needs and loop through each employee start date looking for the anniversary date. When found, a message box will appear with the name of the employee that is having the anniversary.

Please Login or Register  to view this content.

Put this code in the Employee Data worksheet module. You can add a button to the sheet that will trigger this macro to run.
Other than the method I'm offering you, I don't see a way to make this work within the User Form.

[JJFletcher]

JIm,

I really thank you for your response -


Thanks,

John

[Jim885]

You are welcome.

You could also make this an event code that would run whenever the workbook is opened.

I hope this satisifies what you need. If so, please mark this thread as solved.

[JJFletcher]

Jim, Thanks...

I really have to try to create a solution in the userform - it works so well - getting the form to provide that data as the code does in the sheet is optimal
Thanks so much - I know you said there is not way - but maybe another method etc... Your solution actually works fantastic in the EmployeeData sheet...

John

[JJFletcher]

Why can't this be achieved in a listbox in the userform ???

Please Login or Register  to view this content.

[Jim885]

Hi John,
I thought about for a while, but it would be above my skills to make it work in a User Form, if it is even possible. I also see it getting complicated, time consuming and require a modification of the User Form, as well as incorporating the additional code into the code behind the User Form.

You could possibly avoid all that by putting a button in the User Form that will run the code I gave you. I see that you attached your name to several of the other codes in the workbook. So I'm sure you could add a button to run the code, if you want to consider that option.

Or maybe you can start another thread and someone else can assist you so that it appears in a text box.

The advise everyone else gave you to avoid the flashing text is in your best interest. No programmer likes flashing text. It requires a timer to be set within the code, and the code has to keep running to make the text flash. That will interfere with the User Form and the ability to use the worksheet.

I happen to have two examples of code for blinking cells / text for you regarding your request for flashing text. I did not write these codes. I found them on the internet. They were written by very talented people who also advised against using flashing text. Here they are;

Please Login or Register  to view this content.

and;

Please Login or Register  to view this content.

You can find several more of them if you google "vba blinking text"
You will also see that many well respected people advise against it for many reasons.

Thanks for the rep! Much appreciated.
Best of luck to you.