Calendar Help

[Joneye]

Im trying to make a perputal calendar, and failing, Id like the users to select the year and have a calendar populate itself, as you can see lines 15 & 16 work today and will change fine, the problem here is to know how many days in a month and how to get excel showing this when a user selects a year of a month?


<b>Excel 2003</b><table cellpadding="2.5px" rules="all" style=";background-color: #FFFFFF;border: 1px solid;border-collapse: collapse; border-color: #A6AAB6"><colgroup><col width="25px" style="background-color: #E0E0F0" /><col /><col /><col /><col /><col /><col /><col /></colgroup><thead><tr style=" background-color: #E0E0F0;text-align: center;color: #161120"><th></th><th>D</th><th>E</th><th>F</th><th>G</th><th>H</th><th>I</th><th>J</th></tr></thead><tbody><tr ><td style="color: #161120;text-align: center;">12</td><td style="text-align: right;;">2011</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">13</td><td style="text-align: right;border-bottom: 1px solid black;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">14</td><td style="text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">August</td><td style="text-align: center;border-bottom: 1px solid black;border-left: 1px solid black;;"></td><td style="text-align: center;border-bottom: 1px solid black;;"></td><td style="text-align: center;border-bottom: 1px solid black;;"></td><td style="text-align: center;border-bottom: 1px solid black;;"></td><td style="text-align: center;border-bottom: 1px solid black;;"></td><td style="text-align: center;border-bottom: 1px solid black;;"></td></tr><tr ><td style="color: #161120;text-align: center;">15</td><td style="text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">Fri</td><td style="text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">Sat</td><td style="text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">Sun</td><td style="text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">Mon</td><td style="text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">Tue</td><td style="text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">Wed</td><td style="text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">Thu</td></tr><tr ><td style="color: #161120;text-align: center;">16</td><td style="text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">5</td><td style="text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">6</td><td style="text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">7</td><td style="text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">8</td><td style="text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">9</td><td style="text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">10</td><td style="text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">11</td></tr><tr ><td style="color: #161120;text-align: center;">17</td><td style="text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">12</td><td style="text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">13</td><td style="text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">14</td><td style="text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">15</td><td style="text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">16</td><td style="text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">17</td><td style="text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">18</td></tr><tr ><td style="color: #161120;text-align: center;">18</td><td style="text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">19</td><td style="text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">20</td><td style="text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">21</td><td style="text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">22</td><td style="text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">23</td><td style="text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">24</td><td style="text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">25</td></tr></tbody></table><p style="width:3.6em;font-weight:bold;margin:0;padding:0.2em 0.6em 0.2em 0.5em;border: 1px solid #A6AAB6;border-top:none;text-align: center;background-color: #E0E0F0;color: #161120">Sheet1</p><br /><br /><table width="85%" cellpadding="2.5px" rules="all" style=";border: 2px solid black;border-collapse:collapse;padding: 0.4em;background-color: #FFFFFF" ><tr><td style="padding:6px" ><b>Worksheet Formulas</b><table cellpadding="2.5px" width="100%" rules="all" style="border: 1px solid;text-align:center;background-color: #FFFFFF;border-collapse: collapse; border-color: #A6AAB6"><thead><tr style=" background-color: #E0E0F0;color: #161120"><th width="10px">Cell</th><th style="text-align:left;padding-left:5px;">Formula</th></tr></thead><tbody><tr><th width="10px" style=" background-color: #E0E0F0;color: #161120">D12</th><td style="text-align:left">=NOW(<font color="Blue"></font>)</td></tr><tr><th width="10px" style=" background-color: #E0E0F0;color: #161120">D14</th><td style="text-align:left">=NOW(<font color="Blue"></font>)</td></tr><tr><th width="10px" style=" background-color: #E0E0F0;color: #161120">D15</th><td style="text-align:left">=NOW(<font color="Blue"></font>)</td></tr><tr><th width="10px" style=" background-color: #E0E0F0;color: #161120">E15</th><td style="text-align:left">=NOW(<font color="Blue"></font>)+1</td></tr><tr><th width="10px" style=" background-color: #E0E0F0;color: #161120">F15</th><td style="text-align:left">=NOW(<font color="Blue"></font>)+2</td></tr><tr><th width="10px" style=" background-color: #E0E0F0;color: #161120">G15</th><td style="text-align:left">=NOW(<font color="Blue"></font>)+3</td></tr><tr><th width="10px" style=" background-color: #E0E0F0;color: #161120">H15</th><td style="text-align:left">=NOW(<font color="Blue"></font>)+4</td></tr><tr><th width="10px" style=" background-color: #E0E0F0;color: #161120">I15</th><td style="text-align:left">=NOW(<font color="Blue"></font>)+5</td></tr><tr><th width="10px" style=" background-color: #E0E0F0;color: #161120">J15</th><td style="text-align:left">=NOW(<font color="Blue"></font>)+6</td></tr><tr><th width="10px" style=" background-color: #E0E0F0;color: #161120">E16</th><td style="text-align:left">=NOW(<font color="Blue"></font>)+1</td></tr><tr><th width="10px" style=" background-color: #E0E0F0;color: #161120">F16</th><td style="text-align:left">=NOW(<font color="Blue"></font>)+2</td></tr><tr><th width="10px" style=" background-color: #E0E0F0;color: #161120">G16</th><td style="text-align:left">=NOW(<font color="Blue"></font>)+3</td></tr><tr><th width="10px" style=" background-color: #E0E0F0;color: #161120">H16</th><td style="text-align:left">=NOW(<font color="Blue"></font>)+4</td></tr><tr><th width="10px" style=" background-color: #E0E0F0;color: #161120">I16</th><td style="text-align:left">=NOW(<font color="Blue"></font>)+5</td></tr><tr><th width="10px" style=" background-color: #E0E0F0;color: #161120">J16</th><td style="text-align:left">=NOW(<font color="Blue"></font>)+6</td></tr><tr><th width="10px" style=" background-color: #E0E0F0;color: #161120">D17</th><td style="text-align:left">=NOW(<font color="Blue"></font>)+7</td></tr><tr><th width="10px" style=" background-color: #E0E0F0;color: #161120">E17</th><td style="text-align:left">=NOW(<font color="Blue"></font>)+8</td></tr><tr><th width="10px" style=" background-color: #E0E0F0;color: #161120">F17</th><td style="text-align:left">=NOW(<font color="Blue"></font>)+9</td></tr><tr><th width="10px" style=" background-color: #E0E0F0;color: #161120">G17</th><td style="text-align:left">=NOW(<font color="Blue"></font>)+10</td></tr><tr><th width="10px" style=" background-color: #E0E0F0;color: #161120">H17</th><td style="text-align:left">=NOW(<font color="Blue"></font>)+11</td></tr><tr><th width="10px" style=" background-color: #E0E0F0;color: #161120">I17</th><td style="text-align:left">=NOW(<font color="Blue"></font>)+12</td></tr><tr><th width="10px" style=" background-color: #E0E0F0;color: #161120">J17</th><td style="text-align:left">=NOW(<font color="Blue"></font>)+13</td></tr><tr><th width="10px" style=" background-color: #E0E0F0;color: #161120">D18</th><td style="text-align:left">=NOW(<font color="Blue"></font>)+14</td></tr><tr><th width="10px" style=" background-color: #E0E0F0;color: #161120">E18</th><td style="text-align:left">=NOW(<font color="Blue"></font>)+15</td></tr><tr><th width="10px" style=" background-color: #E0E0F0;color: #161120">F18</th><td style="text-align:left">=NOW(<font color="Blue"></font>)+16</td></tr><tr><th width="10px" style=" background-color: #E0E0F0;color: #161120">G18</th><td style="text-align:left">=NOW(<font color="Blue"></font>)+17</td></tr><tr><th width="10px" style=" background-color: #E0E0F0;color: #161120">H18</th><td style="text-align:left">=NOW(<font color="Blue"></font>)+18</td></tr><tr><th width="10px" style=" background-color: #E0E0F0;color: #161120">I18</th><td style="text-align:left">=NOW(<font color="Blue"></font>)+19</td></tr><tr><th width="10px" style=" background-color: #E0E0F0;color: #161120">J18</th><td style="text-align:left">=NOW(<font color="Blue"></font>)+20</td></tr></tbody></table></td></tr></table><br /><table width="85%" cellpadding="2.5px" rules="all" style=";border: 2px solid black;border-collapse:collapse;padding: 0.4em;background-color: #FFFFFF" ><tr><td style="padding:6px" ><b>Array Formulas</b><table cellpadding="2.5px" width="100%" rules="all" style="border: 1px solid;text-align:center;background-color: #FFFFFF;border-collapse: collapse; border-color: #A6AAB6"><thead><tr style=" background-color: #E0E0F0;color: #161120"><th width="10px">Cell</th><th style="text-align:left;padding-left:5px;">Formula</th></tr></thead><tbody><tr><th width="10px" style=" background-color: #E0E0F0;color: #161120">D16</th><td style="text-align:left">{=NOW(<font color="Blue"></font>)}</td></tr></tbody></table><b>Entered with Ctrl+Shift+Enter.</b> If entered correctly, Excel will surround with curly braces {}.
<b>Note: Do not try and enter the {} manually yourself</b></td></tr></table><br />

 

[VoG]

This doesn't directly answer your question but why not use a Calendar Control

http://www.fontstuff.com/vba/vbatut07.htm

http://www.fontstuff.com/excel/exltut02.htm

 

[Joneye]

Vog, I like it will look at them now.

I saw this http://www.excelhero.com/blog/2010/01/live-calendar-musings.html but sadly I can not get some of the function to work, either poor instructions or just plane me and excel 2003 in work

 

[R Pelle]

Vog, I like it will look at them now.

I saw this http://www.excelhero.com/blog/2010/01/live-calendar-musings.html but sadly I can not get some of the function to work, either poor instructions or just plane me and excel 2003 in work

Hi there, thanks for your link. At first I didnt get it to work but after watching the video and rereading the written instructions it actually worked. I initially failed to see that there were two different separators mentioned
{0,1,2etc} and {0;1;2;} and To insert '=Calendar' using F2 as an array! (Ctr Shift Enter)
You should give it another try!

Rob

 

[Joneye]

I will review it again at home were I have excel 2011 mac and 2007 windows, using 2003 at work and it falls over, something wrong with the edit, go to then special it fails to stick, it would be usful if it was left as a download as well.