As part of my 2003 life plan, I'm using an Excel 2003 montly calendar
(downloaded from MS Office website) to record my daily log of the
good, bad and ugly to support my review of how I'm doing with what's
important in my life (If you need to see the xls, I can uppload).

In each day cell I enter the highlights and color code the cell
(green, yellow, red - good, badly, ugly :=). I use the comment's
fuction to add details of the day. I have six areas of my life I'm
focusing on to have a balanced life:
* Physical
* Emotional
* Financial
* Relationships
* Spiritual
* Career

How can I customize this xls so when I right click on the date cell
and select "Insert Comment" the comment window pops up and in addition
to my name, each of the six areas I'm focusing on in my life auto
paste into the  text box? The intent is to highlight, what if
anything, I've done that day in support of each area.

Example (with possible formatting):

Cell Comment Window:

<Nmae> (already inserted)

Physical:

Emotional:

Financial:

Relationships:

Spiritual:

Career:

Thanks in advance.

---

Clarification of Question by infohelp-ga on 19 Jan 2003 14:32 PST

Rac,

Thanks for the fast reply. I thought I followed your instructions
carefully, but when I select a cell and click on the icon button, I
get an error message box stating it can't find the macro (even though
I thought I assign it as you described) The error text states:

The macro "2003Cal - Life Plan Log.xls!AddComment.AddComment' cannot
be found.

Like your xls I added the code as a module (named it AddComment) in my
Cal xls. Was this not correct?

Thx.

---

Clarification of Question by infohelp-ga on 19 Jan 2003 15:56 PST

RAC,

Besides the two questions under the comment section, can you help with
one minor format addition to the code?
1) I need the 6 lines of text in bold

Other than that, I say we can close this question. Thx. again.

infohelp

Hi,
To do this I written a VBA macro. Please try and give feedback.
Attached in the following link sample xls file with macro.  
http://rac-ga.tripod.com/addcomment.xls  

Download the addcomment.xls file.
open the addcomment.xls file.
From the tools menu-->Macros-->Visual basic editor
Copy the sub addcomment()....End Sub and paste it  to your excel
sheet(your calendar excelsheet) visual basic editor.
Save it.
To create a toolbar button follow the steps as follows.

1.From your worksheet Right click on the toolbar.
2.Select customise
3.Select New
4.Select Ok. This will create a custom toolbar
5.Select the commands tab.
6.From the categories select Macros
7.Drag the custombutton(A yellow face) to the newly created toolbar
and drop it. Now the Icon is in the toolbar.
8.Right click the icon(Yellowface) in the toolbar
9.In the Name give &Add Comment (This will come as tool tip)
10.Select Assign Macro
11.In the macro list select AddComment macro and say Ok.
Save the workbook.

Now Select a cell and clik the toolbar button. You will get the
comment cell filled up.

Hi,
pasted the macro below it use it, Just incase if you find difficulty
in downloading the xls file.

Sub AddComment()
'This will add the comment text in the active cell
With ActiveCell
    .AddComment
    .Comment.Text Text:=Application.UserName & Chr(10) & Chr(10) & _
                        "Physical:" & Chr(10) & Chr(10) & _
                        "Emotional:" & Chr(10) & Chr(10) & _
                        "Financial:" & Chr(10) & Chr(10) & _
                        "Relationships:" & Chr(10) & Chr(10) & _
                        "Spiritual:" & Chr(10) & Chr(10) & _
                        "Career:" & Chr(10) & Chr(10)
 End With
ActiveCell.Comment.Shape.ScaleHeight 2.5, msoFalse,
msoScaleFromTopLeft

ActiveCell.Comment.Shape.ScaleWidth 2.5, msoFalse, msoScaleFromTopLeft
ActiveCell.Comment.Visible = True

End Sub

Thanks,
RAC

Rac, 
 
Thanks for the fast reply. I thought I followed your instructions
carefully, but when I select a cell and click on the icon button, I
get an error message box stating it can't find the macro (even though
I thought I assign it as you described) The error text states:
 
The macro "2003Cal - Life Plan Log.xls!AddComment.AddComment' cannot
be found.
 
Like your xls I added the code as a module (named it AddComment) in my
Cal xls. Was this not correct?
 
Thx.

Rac,

Got it to work (my security settings where set to High" Two questions:
1) In the code, why two ActiveCell.Comment. ... after the Exit With
line.
2) Is there a way to sign the macro in a way to allow it to run with a
security setting of High vs. Medium.

Thanks again.

Hi,
Nice to note that you are able to run the macro.
1. The 2 lines below endwith makes the size(height and width) of the
comment box. As of now they are set as 2.5. you can modify it based on
your need.
ActiveCell.Comment.Shape.ScaleHeight 2.5, msoFalse,msoScaleFromTopLeft
ActiveCell.Comment.Shape.ScaleWidth 2.5, msoFalse, msoScaleFromTopLeft
ActiveCell.Comment.Visible = True 

The last line makes the comment box visible.

2. I am not aware of how to make the comment text bold. May  be some
other researchers may be able to help.
3. Security settings: I am using excel-97. It seems you are latter
version of excel.  I am not aware of how to make security settings.
Please refer vba help menu.

Thanks,
RAC

Thanks RAC,

Perhaps, as you suggest, another researcher will know how to bold.
Regardless, I believe your efforts are well worth the price, so feel
free to post as answer and close. Thx. again!

InfoHelp