Calendar event notifications

**Dan Mackman** · 07-31-2008, 05:06 AM

Hi,

I have a calendar created as an excel workbook that currently has no functionality other than writing notes for specific dates. How can I get excel to show a message/popup/notification when the calender is opened up each time for what has been input as a note for the date today? The calendar has a year to view on one worksheet and then a month to view on subsequent tabs. please note i am a relative novice with excel so please be gentle!

Dan

**dominicb** · 07-31-2008, 05:37 AM

Good morning Dan Mackman

This should be pretty do-able. Could you post a blank calendar (just one year / worksheet will be fine) just so we can look at how it's set out.

DominicB

**Dan Mackman** · 07-31-2008, 05:49 AM

many thanks in advance!

**Dan Mackman** · 07-31-2008, 05:57 AM

PC threw a wobbly....

**dominicb** · 07-31-2008, 08:05 AM

Hi Dan Mackman

I've added a small module that selects the current month, and today's day. It will also flash up a message box if there is anything in the cell next to the day number.

HTH

DominicB

**Dan Mackman** · 07-31-2008, 08:11 AM

Thats great! One question, Can the message displayed be what the note written in the calendar says instead of "You have a reminder against today"?

Dan

**dominicb** · 07-31-2008, 08:22 AM

Hi Dan Mackman

Originally Posted by Dan Mackman

Can the message displayed be what the note written in the calendar says instead of "You have a reminder against today"?

How's this?

HTH

DominicB

**Dan Mackman** · 07-31-2008, 08:33 AM

one final thing. if multiple entries are made in the range of cells for that date it will only show the notification for what is written in the cell adjacent to the date. Can it be done for several items listed in the cells below relating to that date?

Dan

**dominicb** · 07-31-2008, 08:41 AM

Hi Dan Mackman

Originally Posted by Dan Mackman

Can it be done for several items listed in the cells below relating to that date?

Sure can - just tell me, if you were to enter comments against today (31/07/08) would you enter it in column I or column J?

DominicB

**Dan Mackman** · 07-31-2008, 09:20 AM

column J ('I' will be locked). Just out of interest - how do you do all of the stuff you have done so far (maybe useful in future for me!!)

Dan

**dominicb** · 07-31-2008, 09:48 AM

Hi Dan Mackman

Have a look at this version ...

This is done using VBA (macros) which is Excel's built in programming language. From your calendar, press Alt + F11 to open the VBE (programming environment), and in the VBAProject window find your file, and the sub-node within it called ThisWorkbook. Double click on this to open a window which will show all the code that is kicked off whenever the workbook is opened.

HTH

DominicB

**Dan Mackman** · 07-31-2008, 10:23 AM

Just had a look at the VBA bit - i have a lot to learn! Just out of interest when i looked at the last version you attached it displayed all notifications in the cells below the date in column J except the very top one next to the date. Which line in the VBA bit do I edit so that all 6 cells contents are displayed instead of the last 5 only?

Dan

**dominicb** · 07-31-2008, 10:40 AM

Hi Dan Mackman

To do that, replace all the code with the code here - that should sort it out for you.

Please Login or Register  to view this content.

HTH

DominicB

**Dan Mackman** · 07-31-2008, 11:02 AM

I seem to have broken it! i have added to tabs before the calendar tabs, changed the VBA as instructed and now it doesn't flash a message up! Any chance you could have a lokk to see what I have done! Sorry

Dan

(file now re-named)

**dominicb** · 07-31-2008, 11:14 AM

Hi Dan Mackman

OK, Quick VBA lesson.
This is the line that selects the sheet.

Please Login or Register  to view this content.

Month returns a numeric value - 7 for July - and can be coerced to show the month (ie to a user), but VBA will always read it back to itself as a number, so it can't recognise the sheet names.

Therefore, it will add 1 to the month number (7) and find the eighth sheet along. As you have inserted 2 more sheets before the month tabs, you will have to add 2 to that 1, so the new line becomes :

Please Login or Register  to view this content.

See, it's not that bad, is it ...

HTH

DominicB

**Dan Mackman** · 07-31-2008, 11:31 AM

Dominic,

Thank you sincerely for all your help today. You have gone above and beyond the call and I am extremely greatful for your time and effort.

Many Many Thanks

Dan

**Dan Mackman** · 08-01-2008, 09:02 AM

Hi Dominic,

I have just been asked if it is possible to link the calendar function to the Data Input sheet so that if a date is entered in columns "J, L, N, P or R" it will input that entry along with the customer name from column B on the Data Input Sheet onto the corresponding month and day in the calendar, thus generating a notification as solved yesterday. Is this possible?

Dan

**dominicb** · 08-01-2008, 11:56 AM

Hi Dan Mackman

I will take a look at this but I might not be able to do so over the weekend, but will try and sort something out on Monday (if no-one jumps in before me

).

DominicB

**Dan Mackman** · 08-01-2008, 03:31 PM

I look forward to your reply!

**dominicb** · 08-05-2008, 03:34 AM

Hi Dan Mackman

OK, I've managed to have a look at this (actually did it yesterday but couldn't upload it as the board was down). On your input form there's now a button at the top of the sheet. After you've entered a line of information, stay on that line and click the button.

Not had time to fully test it but it seems to work OK. The company names should be entered into the correct date on the calendar, and the code should automatically detect the next blank row for each date that is being posted. If there are six lines to a date, then a company name will not be posted to this date, and a message will flash up at the end of the routine informing the user.

HTH

DominicB

Edit : Uploaded wrong file!

Correct file now uploaded.

**Dan Mackman** · 08-05-2008, 06:35 AM

Hi,

Thanks for that - I have had a play but it seems to have a few bugs. It is posting the data in a few different places but not the date inputted. It seems to work in principal tho! I had a look at the VB but it may as well be in French to me!

Dan.

**dominicb** · 08-05-2008, 08:00 AM

Hi Dan Mackman

You're going to have to give me some examples so I can recreate any problems you are having. I used the dates 26/01/08, 29/01/08, 11/02/08, 17/06/08 and 24/07/08 and all these posted the company name into the correct places. Are you using English style or American style dates?

DominicB

**Dan Mackman** · 08-05-2008, 12:17 PM

I have deleted all data out of the spreadsheet and inputted new data and dates to test it. The first attempt should have entered data for tomorrow. Nothing was showing so i looked through the tabs and found 6 entries on Dec 30th. I am using UK date format as follows dd/mm/yy. hope this helps.

Dan

**dominicb** · 08-06-2008, 06:58 AM

Hi Dan Mackman

I'm afraid I can't replicate this error you're reporting. Using the sheet I uploaded, I have just entered 06/08/08 (for today - UK format) in cells J3, L3, N3, P3 and R3 and clicked on the button. The contents of B3 were added to cells H9:H13 in August. This is the expected behaviour.

I can't take this any further if I can't replicate the problem.

DominicB

Calendar event notifications

LinkBack

Thread Tools

Rate This Thread

Display

Calendar event notifications

calendar posted...

try again!

Thank you

Gennius!

Column J....

I am forever in your debt!

I am being a pain now - i apologise

Fixed!

Sorry to bother you again!

thanks as ever

a few bugs...

this is what happens

Thread Information

Users Browsing this Thread

Bookmarks

Bookmarks

Posting Permissions