Making Calendar

I'm using excel 2002 SP3 and I wonder if it's easy to make a calendar in which I can write various activities of my projects.

For example I would have liked to have in one worksheet lets say the month of May showing all days / dates and if I click on any date it will take me to another worksheet of this particular day and I will find or write all activities for that day.

Is this possible / easy?

Thanks for the help

Perhaps not for each day but what would be your suggestion?

I would prefer to have my activities easily displayed

That looks good Andy but not really what I'm after

Perhaps I'm asking too much but I would like to record more info and would also like the capability to change the font type / size / color on the items I write. Also it will be nice if I could record the info in an outline form. Presently I'm using an old PIM software (ECOPro) which is fantastic but thought that I may be able to make one in Excel

I'm building CNC machines as a hobby and would like to have a record of my work from design to material purchasing, fabrication, testing, etc.

That is the reason I was asking when I click on a date, it will take me to a new worksheet so I can write and format whatever I want. And to reduce the number of worksheets it can be one sheet per month and I will divide the sheets as I want because some days I have no activities, nothing to write, and some I have many activities

Why not use a standard "Calendar control" (Placed in the sheet or a Userform), that on selection of a date it takes you to a Sheet with that months name, it could then perhaps select that specific date from column "A".

Originally Posted by MickG

Why not use a standard "Calendar control" (Placed in the sheet or a Userform), that on selection of a date it takes you to a Sheet with that months name, it could then perhaps select that specific date from column "A".

Let me try this one MickG and see what I will get

Sorry but not knowledgable enough to fill in the required VBA project sheet for the calendar control

Perhaps if you give me a sample I may be able to do so

Please see the attached Example file:-
On selection from sheet "Calendar" the code will Take you to the selected month (Sheet) and then To the date in column "A" if available.
You can alter/Move/Delete dates in column "A" as required.
If you Can select "Design Mode" You can Alter All Details /Colours of the Calendar.

That's pretty good MickG, thanks so much

I think I can manage now and make the changes I want

Your welcome
Regrds Mick

Still working on my project and I was thinking to do something new.

Lets say that I have an Excel file which contains 10 sheets; the first sheet is named Activities and the rest are named lets say Design, Materials, Purchasing, etc. The Activities sheet is kind of an entry form

What I want to do is when I write an entry on the Activities sheet which contains the word Design at the beginning, this entry is also copied to the Design sheet. Therefore when I want to find something pertaining to Design I just click the Design sheet

I have this formula in my budget worksheet =IF(LEFT(B345,3)="GAS",IF(D345="",-C345,-D345),"") which I made perhaps 15 years ago but since I retire I have forgotten a lot about Excel. I guess it should be a similar formula but instead of writing the value to the Gas column it writes the value to the Design sheet

Is it possible to achieve what I want?

Dont know if its ok but decided to start a new thread regarding my post #13

Sorry, how do I mark the thread as solved?

I just found the answer, sorry again

Here is a calendar I did a while back.

Thank you Whizbang for the help

The attached snip is from an old PIM software which I use since 1994 and I try to replicate it. Is it easy to give me a hint as to how I should proceed to do so?

The times shown on the snip are not important for me and therefore can be eliminated
Calendar Sample.jpg

That's amazing Whizbang, thank so much

The sheet Report is what I'm after, have to study it further and see how I can improve the layout. Also I dont iunderstand the use of the sheets "Calendar Items" and "System", what they are for?

Much appreciated

Looks like the sheet "Report" is locked because I cant do any formatting neither I can see the formulas on the controls box which I would like to see and learn

Also the Appointment area stays the same regardless what date I have. What I would like to do is when I write for example on May 10 some activities, they stay on May 10 and when I change to May 11, a new blank Appointment area comes up and what I will write there stays on May 11. Is this easy to achieve ?

Originally Posted by Whizbang

None of the sheets should be protected. Check and see if the workbook is in Protected View. This happens a lot when you get files from email or the internet. MS Office detects the source and opens it in Protected View to help protect your computer. If you trust the document, then you have to disable protected view.

http://office.microsoft.com/en-us/ex...010355931.aspx

Also, check and make sure your calculation is set to automatic.

Calendar Items contains the appointment data in a table layout.

System contains some behind the scenes calculation and generation of the calendars, as well as the date displayed in the Report sheet.

Thank you for your time Whizbang

I thought I did check the protection, anyway now its ok

I like the idea of the Calendar Items sheet but when I type an entry in the Appointment section of the Report sheet it does not appear on the table layout

Also the System sheet only shows the 3 calendar sheets, no data

Bottom line its a very good layout and I'm working to see on how I can use it

Now I get it, thanks

The link you provided Whizbang looks like an excellent site; I will give it a try and see what I can get out of it. If I stumble I will ask for your help and most likely that's what will happen because simple coding is ok with me and anything complicated I'm lost LOL.

Thank you again for your time

I did the calendar from the Excelhero link; it was a good exercise and well done to learn all the associated formulas but the same can be done by inserting a calendar object on an Excel sheet and then use the VB editor to edit the properties.

If you know any similar site to give me directions coding the file you sent me it will be much appreciated. Or perhaps you can give me a hint what is needed and I can Google it for further help

Thank you Whizbang

Excel Hero is a great resource for some nifty formulas and really understanding how work Excel to the fullest. Pretty much every article listed here http://www.excelhero.com/blog/2011/0...-formulas.html will be insightful.

I also like this article on SUMPRODUCT. It really goes in-depth and helps you understand SUMPRODUCT and arrays enough to design your own on the fly. http://xldynamic.com/source/xld.SUMPRODUCT.html - Everything on XLDynamic is pretty top notch.

I like this article on speeding up your VBA code http://blogs.office.com/b/microsoft-...practices.aspx

Andy Pope's site is also an amazing adventure into "Smarter Than I Will Ever Be Land". http://www.andypope.info/

Thanks Whizbang, lots of info and help on these links and I guess will be enough for me

Sorry, I meant to add more to my last post, but somehow wasn't able to connect to the site at my work computer for a couple of days. Here is an updated file.

For the file, there are two main components; the calendars, and the appointments list. The calendars are explained in the link I already gave. The Appointments list can be explained here: http://office.microsoft.com/en-us/ex...001226038.aspx - The code for the date controls are fairly straightforward if you know VBA at all.

In this updated version, I enabled you to add/change appointments from the Report tab. It is not perfect, but it gets the job done.

Thanks again for taking the time Whizbang

Now it works really well and the new link provides good help. Got to study the new info so I can understand what is going on for future changes

Your help is very much appreciated

I realise that you have gone some way along this route by now, but if you are still looking for alternative approaches then you might care to follow this link:

http://www.excelforum.com/excel-gene...entry-tab.html

which shows how calendars can be generated using formulae only, from a list of activities (which is what you asked for in your first post on the thread).

Hope this helps.

Pete

Thank you Pete

That is excellent and have to study it, I like the formulas better than coding since I'm not so familiar with coding and honestly even formulas are difficult for me now.

What Whizbang developed is more on the line with what I prefer but your idea also is attractive and I will work to see if and how I can modify it

Post 13 indicates what I want and I'm working to achieve something similar

That is really good Whizbang and will give it a try

But what purpose the 3 calendars serve on the Report sheet? Looks like they serve no purpose?

*Shrugs* They were there in the sample image you posted a while back (http://www.excelforum.com/attachment...4&d=1368112905)

They don't function in the respect that you can click on them to change the date, but they do function in that they show you which days surrounding the selected date have appointments. Just a way to have a little headsup on upcoming appointments. If there were appointments every day, the calendars would lose usefullness, however.

That's no problem Whizbang, thank you.

I will work on your last file to see how I customize it to my needs

Neat Day Calendar,when I add something it doesn't input right.? As soon as I hit enter it goes away.

This thing is looking better all the time, still getting error on the 30,31. For some reason I only show January for all the months

Here is a US region copy of Andy's work, I added a Today button with no luck on getting it to work right. I can get it to go
to today but not have textbox update right. Going to need help on this one, it would be nice if everyday that has data inputted would
change to another color so you wouls know what days are free if using as a planner.

Thanks Andy looking forward on what you come up with
Z