automatic cell comment

[ADK]

Subject: Adding a cell comment with a value from a diferent cell and on a
different sheet

Is there a way to add a comment to a cell which the comment contains the
value from a different cell.

Scenario:

I have cells which have the days of the week abbreviated (Su, Mon, Tues,
etc) These cells have a narrow width just enough for the abbreviation. I
would like to add a comment to that cell which the comment would be: Sunday,
August 27th, 2006

Screenshot:
http://img172.imageshack.us/img172/1516/worksheethoursmu6.jpg

The 09/02/06 is the week ending date (Saturday for week 1). The value (week
ending date) can change depending on what start date the user indicates on
another sheet.

What it would look like:
http://img101.imageshack.us/img101/8393/worksheethours2fc2.jpg

Thanks in advance for any help!!!

ADK

 

[Guest]

Herr is a small macro that takes the value from Sheet1 cell A1 and puts it in
a comment in Sheeet2 cell B2:

Sub Macro1()

Dim r1 As Range
Dim r2 As Range

Set r1 = Worksheets("Sheet1").Range("A1")
Set r2 = Worksheets("Sheet2").Range("B2")

r2.AddComment
r2.Comment.Visible = False
r2.Comment.Text Text:=r1.Value
End Sub