[lynduc]

I have a really good template from MS called Event Schedule Planner 2009 but
want to change it to 2010. When I change the year on the heading, the 12
month calendar below doesn't change . How can I change that, other than
importing a calendar? The problem is that the project phases won't work (I
think) if I insert a 2010 calendar. Will the dates I choose automatically
change with the new calendar I insert?

[Graham Mayor]

The planner template is but a basic document template. It is not macro
driven and it could be a bit of a pain to make it so. There is no
correlation between the dates and the calendar. You can open the template
and edit the year and the dates in the calendar. The following macro will
insert the new dates in each monthly calendar. Put the cursor in the cell
that represents the first of the month and run the macro. Repeat for each
month.

It runs slowly, but it will change the numbers that follow for that month
(though not those before the start position or after the end position) to
reflect the correct day/date and clears the background shading. Not perfect,
but should be accurate ... but slow

Sub Macro1()
Dim i As Long
Dim iDays As Long
Dim oRng As Range
iDays = InputBox("Days in the month")
For i = 1 To iDays
Set oRng = Selection.Cells(1).Range
oRng.Shading.BackgroundPatternColor = wdColorAutomatic
oRng.End = oRng.End - 1
oRng.Text = i
Application.ScreenRefresh
Selection.MoveRight Unit:=wdCell
Next i
End Sub

http://www.gmayor.com/installing_macro.htm

--

Graham Mayor - Word MVP

My web site www.gmayor.com
Word MVP web site http://word.mvps.org



"lynduc" wrote in message
...
I have a really good template from MS called Event Schedule Planner 2009
but
want to change it to 2010. When I change the year on the heading, the 12
month calendar below doesn't change . How can I change that, other than
importing a calendar? The problem is that the project phases won't work (I
think) if I insert a 2010 calendar. Will the dates I choose automatically
change with the new calendar I insert?

[Graham Mayor]

The planner template is but a basic document template. It is not macro
driven and it could be a bit of a pain to make it so. There is no
correlation between the dates and the calendar. You can open the template
and edit the year and the dates in the calendar. The following macro will
insert the new dates in each monthly calendar. Put the cursor in the cell
that represents the first of the month and run the macro. Repeat for each
month.

It runs slowly, but it will change the numbers that follow for that month
(though not those before the start position or after the end position) to
reflect the correct day/date and clears the background shading. Not perfect,
but should be accurate ... but slow

Sub Macro1()
Dim i As Long
Dim iDays As Long
Dim oRng As Range
iDays = InputBox("Days in the month")
For i = 1 To iDays
Set oRng = Selection.Cells(1).Range
oRng.Shading.BackgroundPatternColor = wdColorAutomatic
oRng.End = oRng.End - 1
oRng.Text = i
Application.ScreenRefresh
Selection.MoveRight Unit:=wdCell
Next i
End Sub

http://www.gmayor.com/installing_macro.htm

--

Graham Mayor - Word MVP

My web site www.gmayor.com
Word MVP web site http://word.mvps.org



"lynduc" wrote in message
...
I have a really good template from MS called Event Schedule Planner 2009
but
want to change it to 2010. When I change the year on the heading, the 12
month calendar below doesn't change . How can I change that, other than
importing a calendar? The problem is that the project phases won't work (I
think) if I insert a 2010 calendar. Will the dates I choose automatically
change with the new calendar I insert?

[Graham Mayor]

Just for the hell of it I have been playing around with the macro I posted
yesterday. The changes still don't make a silk purse out of the sow's ear
that is the template, but they do take some of the thinking away. If you
change the year at the top of the document to whatever year you wish, and
then put your cursor in the cell that represents the first day of each month
of the year in turn, then run the following, it will determine the month and
calculate the number of days in that month (including leap years). Clears
any existing numbers and background fills then renumbers the month. There
doesn't seem to be much you can do to speed up the fill given the way the
tables in the template have been nested, but at least you can watch the
progress - and it is much quicker than filling by hand!

Dim i As Long
Dim iDays As Long
Dim oRng As Range
Dim oMonth As Range
Dim oYear As Range
Set oYear = ActiveDocument.Tables(1).Cell(1, 1).Range
oYear.End = oYear.End - 1
oYear.Start = oYear.End - 4
Set oMonth = Selection.Tables(1).Cell(1, 1).Range
oMonth.End = oMonth.End - 1
Select Case LCase(oMonth.Text)
Case Is = "april", "june", "september", "november"
iDays = 30
Case Is = "january", "march", "may", "july"
iDays = 31
Case Is = "august", "october", "december"
iDays = 31
Case Is = "february"
If Month(DateSerial(oYear, 2, 29)) = 2 Then
iDays = 29
Else
iDays = 28
End If
End Select
Selection.Bookmarks.Add "StartCell"
For i = 3 To 8
Selection.Tables(1).Rows(i).Range.Select
Selection.Shading.BackgroundPatternColor = wdColorAutomatic
Selection.Delete
Next i
Selection.GoTo What:=wdGoToBookmark, name:="StartCell"
For i = 1 To iDays
Set oRng = Selection.Cells(1).Range
oRng.End = oRng.End - 1
oRng.Text = i
Application.ScreenRefresh
Selection.MoveRight Unit:=wdCell
Next i


--

Graham Mayor - Word MVP

My web site www.gmayor.com
Word MVP web site http://word.mvps.org




"Graham Mayor" wrote in message
...
The planner template is but a basic document template. It is not macro
driven and it could be a bit of a pain to make it so. There is no
correlation between the dates and the calendar. You can open the template
and edit the year and the dates in the calendar. The following macro will
insert the new dates in each monthly calendar. Put the cursor in the cell
that represents the first of the month and run the macro. Repeat for each
month.

It runs slowly, but it will change the numbers that follow for that month
(though not those before the start position or after the end position) to
reflect the correct day/date and clears the background shading. Not
perfect, but should be accurate ... but slow

Sub Macro1()
Dim i As Long
Dim iDays As Long
Dim oRng As Range
iDays = InputBox("Days in the month")
For i = 1 To iDays
Set oRng = Selection.Cells(1).Range
oRng.Shading.BackgroundPatternColor = wdColorAutomatic
oRng.End = oRng.End - 1
oRng.Text = i
Application.ScreenRefresh
Selection.MoveRight Unit:=wdCell
Next i
End Sub

http://www.gmayor.com/installing_macro.htm

--

Graham Mayor - Word MVP

My web site www.gmayor.com
Word MVP web site http://word.mvps.org



"lynduc" wrote in message
...
I have a really good template from MS called Event Schedule Planner 2009
but
want to change it to 2010. When I change the year on the heading, the 12
month calendar below doesn't change . How can I change that, other than
importing a calendar? The problem is that the project phases won't work
(I
think) if I insert a 2010 calendar. Will the dates I choose automatically
change with the new calendar I insert?

[Graham Mayor]

Just for the hell of it I have been playing around with the macro I posted
yesterday. The changes still don't make a silk purse out of the sow's ear
that is the template, but they do take some of the thinking away. If you
change the year at the top of the document to whatever year you wish, and
then put your cursor in the cell that represents the first day of each month
of the year in turn, then run the following, it will determine the month and
calculate the number of days in that month (including leap years). Clears
any existing numbers and background fills then renumbers the month. There
doesn't seem to be much you can do to speed up the fill given the way the
tables in the template have been nested, but at least you can watch the
progress - and it is much quicker than filling by hand!

Dim i As Long
Dim iDays As Long
Dim oRng As Range
Dim oMonth As Range
Dim oYear As Range
Set oYear = ActiveDocument.Tables(1).Cell(1, 1).Range
oYear.End = oYear.End - 1
oYear.Start = oYear.End - 4
Set oMonth = Selection.Tables(1).Cell(1, 1).Range
oMonth.End = oMonth.End - 1
Select Case LCase(oMonth.Text)
Case Is = "april", "june", "september", "november"
iDays = 30
Case Is = "january", "march", "may", "july"
iDays = 31
Case Is = "august", "october", "december"
iDays = 31
Case Is = "february"
If Month(DateSerial(oYear, 2, 29)) = 2 Then
iDays = 29
Else
iDays = 28
End If
End Select
Selection.Bookmarks.Add "StartCell"
For i = 3 To 8
Selection.Tables(1).Rows(i).Range.Select
Selection.Shading.BackgroundPatternColor = wdColorAutomatic
Selection.Delete
Next i
Selection.GoTo What:=wdGoToBookmark, name:="StartCell"
For i = 1 To iDays
Set oRng = Selection.Cells(1).Range
oRng.End = oRng.End - 1
oRng.Text = i
Application.ScreenRefresh
Selection.MoveRight Unit:=wdCell
Next i


--

Graham Mayor - Word MVP

My web site www.gmayor.com
Word MVP web site http://word.mvps.org




"Graham Mayor" wrote in message
...
The planner template is but a basic document template. It is not macro
driven and it could be a bit of a pain to make it so. There is no
correlation between the dates and the calendar. You can open the template
and edit the year and the dates in the calendar. The following macro will
insert the new dates in each monthly calendar. Put the cursor in the cell
that represents the first of the month and run the macro. Repeat for each
month.

It runs slowly, but it will change the numbers that follow for that month
(though not those before the start position or after the end position) to
reflect the correct day/date and clears the background shading. Not
perfect, but should be accurate ... but slow

Sub Macro1()
Dim i As Long
Dim iDays As Long
Dim oRng As Range
iDays = InputBox("Days in the month")
For i = 1 To iDays
Set oRng = Selection.Cells(1).Range
oRng.Shading.BackgroundPatternColor = wdColorAutomatic
oRng.End = oRng.End - 1
oRng.Text = i
Application.ScreenRefresh
Selection.MoveRight Unit:=wdCell
Next i
End Sub

http://www.gmayor.com/installing_macro.htm

--

Graham Mayor - Word MVP

My web site www.gmayor.com
Word MVP web site http://word.mvps.org



"lynduc" wrote in message
...
I have a really good template from MS called Event Schedule Planner 2009
but
want to change it to 2010. When I change the year on the heading, the 12
month calendar below doesn't change . How can I change that, other than
importing a calendar? The problem is that the project phases won't work
(I
think) if I insert a 2010 calendar. Will the dates I choose automatically
change with the new calendar I insert?

[Lisa Stewart]

Greetings... I have the same need (changing the year on a Word calendar document that I use), but I don't know much about macros and do not have a lot of time to devote to learning right now (though that is a plan for the future, as I am realizing how handy they are!).

I am using Word 2011 for Mac. Your first Macro works for me (the second one did not for some reason).

However, I have two additional things that I need my implementation of your macro to accomplish:

1) I need it to only change the first line of text in each cell block (ie: only change the date numeral, not the remaining text in the cells, because that is the whole reason I am doing this in the first place; it is an employee schedule and the rest of the data in each cell contains shift times).

2) I would like for it to also fill in the remaining cells in each month (before and after the actual first and last days in the month) with the previous and next month's dates, prefixed by the first letter of those months. For example: The month of January 2011 starts on Friday, which leaves the cell blocks for Sunday-Thursday empty; I would like those cells to show D27, D28, D29, D30, and D31. Similarly, any empty cells after the last day of each month should show the next month's days (ie: January 2011 ends on Monday, so the remaining cells would show F1, F2, F3, F4, F5, and F6).

I realize that my second requirement would probably be difficult and time-consuming to implement, and I can manually change those if they are all I have left to change, but if you already know how to do it, I would really appreciate the capability of that as well!

If it isn't too much trouble for you, and if you have time, could you please respond with the correct macro code for tweaking it to include at least my #1 above?

On Sunday, April 11, 2010 4:51 AM lynduc wrote:

I have a really good template from MS called Event Schedule Planner 2009 but
want to change it to 2010. When I change the year on the heading, the 12
month calendar below does not change . How can I change that, other than
importing a calendar? The problem is that the project phases will not work (I
think) if I insert a 2010 calendar. Will the dates I choose automatically
change with the new calendar I insert?


On Sunday, April 11, 2010 10:29 AM Graham Mayor wrote:

The planner template is but a basic document template. It is not macro
driven and it could be a bit of a pain to make it so. There is no
correlation between the dates and the calendar. You can open the template
and edit the year and the dates in the calendar. The following macro will
insert the new dates in each monthly calendar. Put the cursor in the cell
that represents the first of the month and run the macro. Repeat for each
month.

It runs slowly, but it will change the numbers that follow for that month
(though not those before the start position or after the end position) to
reflect the correct day/date and clears the background shading. Not perfect,
but should be accurate ... but slow

Sub Macro1()
Dim i As Long
Dim iDays As Long
Dim oRng As Range
iDays = InputBox("Days in the month")
For i = 1 To iDays
Set oRng = Selection.Cells(1).Range
oRng.Shading.BackgroundPatternColor = wdColorAutomatic
oRng.End = oRng.End - 1
oRng.Text = i
Application.ScreenRefresh
Selection.MoveRight Unit:=wdCell
Next i
End Sub

http://www.gmayor.com/installing_macro.htm

--

Graham Mayor - Word MVP

My web site www.gmayor.com
Word MVP web site http://word.mvps.org



On Monday, April 12, 2010 7:34 AM Graham Mayor wrote:

Just for the hell of it I have been playing around with the macro I posted
yesterday. The changes still do not make a silk purse out of the sow's ear
that is the template, but they do take some of the thinking away. If you
change the year at the top of the document to whatever year you wish, and
then put your cursor in the cell that represents the first day of each month
of the year in turn, then run the following, it will determine the month and
calculate the number of days in that month (including leap years). Clears
any existing numbers and background fills then renumbers the month. There
does not seem to be much you can do to speed up the fill given the way the
tables in the template have been nested, but at least you can watch the
progress - and it is much quicker than filling by hand!

Dim i As Long
Dim iDays As Long
Dim oRng As Range
Dim oMonth As Range
Dim oYear As Range
Set oYear = ActiveDocument.Tables(1).Cell(1, 1).Range
oYear.End = oYear.End - 1
oYear.Start = oYear.End - 4
Set oMonth = Selection.Tables(1).Cell(1, 1).Range
oMonth.End = oMonth.End - 1
Select Case LCase(oMonth.Text)
Case Is = "april", "june", "september", "november"
iDays = 30
Case Is = "january", "march", "may", "july"
iDays = 31
Case Is = "august", "october", "december"
iDays = 31
Case Is = "february"
If Month(DateSerial(oYear, 2, 29)) = 2 Then
iDays = 29
Else
iDays = 28
End If
End Select
Selection.Bookmarks.Add "StartCell"
For i = 3 To 8
Selection.Tables(1).Rows(i).Range.Select
Selection.Shading.BackgroundPatternColor = wdColorAutomatic
Selection.Delete
Next i
Selection.GoTo What:=wdGoToBookmark, name:="StartCell"
For i = 1 To iDays
Set oRng = Selection.Cells(1).Range
oRng.End = oRng.End - 1
oRng.Text = i
Application.ScreenRefresh
Selection.MoveRight Unit:=wdCell
Next i


--

Graham Mayor - Word MVP

My web site www.gmayor.com
Word MVP web site http://word.mvps.org



Submitted via EggHeadCafe
Microsoft ASP.NET For Beginners
http://www.eggheadcafe.com/training-...NET/7/ASP.aspx