Creating a calendar

[anon]

Hi,

I'm aware this questions has been asked before in many forms, however
I am looking for something quite specific.

I am looking to create and Outlook style calendar within Excel. I
cannot use the Outlook calendar, nor can I use any type of add in (due
to distribution).

I need a calendar that a user can view monthly, and weekly (and
possibly drill into for a daily view dependant on the below).

The user will need to see a printable weekly view, and store around 10
- 15 entries per day. The entries will be populated from selections on
other sheets of the workbook and all will have a similar format (NAME,
ADDRESS, ADDRESS2, ADDRESS3, POSTCODE, TEL, CONTACT,NOTES).

I have tried creating a basic weekly layout that can hold 15 entries
per day, however I cannot get all of the information above to fit into
the entries without the week being too large to print on one page.
This is why I feel I may need a daily view, so the user can view and
print the basics, (perhaps NAME / POSTCODE) from the weekly view, and
drill in to the daily view to see the detail.

I would prefer to have a rolling calendar (ie never runs out), however
at a minimum would like 18 months if the rolling calendar would be too
complicated. I do not want to use lots of sheets, and do not want the
old entries deleted (however would like them hidden and only viewable
if required).

I am comfortable coding / creating a calendar once I can get my head
around the logistics of how it would look / work. Any help or
suggestions would be appreciated as I am fairly stuck at the moment.

 

[SteveM]

Hi,

I'm aware this questions has been asked before in many forms, however
I am looking for something quite specific.

I am looking to create and Outlook style calendar within Excel. I
cannot use the Outlook calendar, nor can I use any type of add in (due
to distribution).

I need a calendar that a user can view monthly, and weekly (and
possibly drill into for a daily view dependant on the below).

The user will need to see a printable weekly view, and store around 10
- 15 entries per day. The entries will be populated from selections on
other sheets of the workbook and all will have a similar format (NAME,
ADDRESS, ADDRESS2, ADDRESS3, POSTCODE, TEL, CONTACT,NOTES).

I have tried creating a basic weekly layout that can hold 15 entries
per day, however I cannot get all of the information above to fit into
the entries without the week being too large to print on one page.
This is why I feel I may need a daily view, so the user can view and
print the basics, (perhaps NAME / POSTCODE) from the weekly view, and
drill in to the daily view to see the detail.

I would prefer to have a rolling calendar (ie never runs out), however
at a minimum would like 18 months if the rolling calendar would be too
complicated. I do not want to use lots of sheets, and do not want the
old entries deleted (however would like them hidden and only viewable
if required).

I am comfortable coding / creating a calendar once I can get my head
around the logistics of how it would look / work. Any help or
suggestions would be appreciated as I am fairly stuck at the moment.

Man, you got it easy. There is already a Calendar control available
in Excel. Open the Controls Tool Bar, select More Controls and scroll
down. You'll see it. You're gonna have to write the event code for
it to do what you want, but at least you have a template. You can
probably locate other versions cheap on the web. Whatever you do,
don't code it up entirely yourself. Unless of course wheel re-
invention is a personal avocation.

SteveM

 

[Nigel]

This appears to be a need for a database application. Excel can of course
provide this capability but unlike Access you are going to need to maintain
the links between the various tables. Design this first before you go
anywhere near Excel !!

You clearly need a calendar module to populate a sheet with the period
requested and display this to the user. Excel of course can create dates
both into the past and the future so there should be no issues with that.

Each date needs to be sensitive to an event so you can deal with entries the
user needs to make for that day. This could open up a UserForm that fills
up with the names-address data you refer to and is stored in another table.
Having made an update to the UserForm data is stored in a separate table
containing date-user-entry combination.
If you need to maintain referential integrity between the entries and the
names-address data then store this an index value and maintain the index
links.

Finally you want to review all the entries for a selected period and this
could be achieved using a column approach with the date on the left, with
multiple rows for each entry to the right.

Hope this concept helps

--

Regards,
Nigel
(e-mail address removed)

 

[Nigel]

Be aware the calendar.ocx is an active X control and for distribution it is
not always going to exist in target machines.


--

Regards,
Nigel
(e-mail address removed)

 

[anon]

Thanks for the suggestions.

1. I had already disregarded the calendar control because of the
reason given above. We know this does not exist on some users machines
(and it is not a simple task to update)

2. Have been instructed not to use Access (originally designed my
whole program in access with the calendar feature however was told it
HAD to be in Excel by the powers that be (our users are not
compfrtable with Access and no matter how much I explained it was
turned down))

Therefore I am still nowhere. I understand this is an extremly long
winded and complex task, however I have no other options.

FYI - the code is already in the sheet to put in the calendar entries
- we originally trialled this using Outlook (another great idea that
was rejected) so the code to put the entries somewhere is there - I
just need a place to put them.

Some thoughts - putting the entries into the daily view (using code
mentioned above) and using formula to link the entries from the daily
view to the monthly view (eg. =Left etc etc). Any ideas whether this
would work.

As I said there is no requirement for a daily view if I can find a way
to fit all this information comfortably into the weekly view - but so
far I am struggling with this.

Again thanks for your suggestions and help so far.

 

[Greg Wilson]

It doesn't seem to be what you're looking for, but I created my own calendar
control that is native to Excel (actually a highly modified toolbar). It
looks and behaves very similar to the MonthView control. Perhaps you can use
it for part of what you need and use the logic behind it to develop the rest?

Greg