Need to Pull Data into a Comment

[BLB]

I am just learning VBA and am stuck on a procedure I am trying to write
I have 2 worksheets: sheet1 is a summary of sheet2. Example: Th
sheets have the following data:
Sheet1:
A1: 4
Sheet 2:
D5: 1
E6: 3

I want to place a comment in Sheet1 A1 that reads:
AP = 1
Forecast = 3

The "1" and the "3" gets pulled from the Sheet2 D5 and E6 cells an
updates automatically when Sheet1 is activated. I have done this par
with no problem using the following code in the Sheet1 module:

Private Sub Worksheet_Activate()
Range("A1").Comment.Text Text:=Chr(10) & "AP = " _
& Sheets("Calculations").Range("D5").Value & Chr(10) & _
"Forecast = " & Sheets("Calculations").Range("E5").Value
End Sub

Note: Sheets("Calculations") is Sheet2

However, the problem is that there are about 100 cells that need thi
same comment that pulls data from Sheet2, but from different cells.
How do I write the code so that the comment knows where to look o
Sheet2 to pull the data?

Any suggestions at all are much appreciated!

 

[John]

I'd be happy to give it a shot, but you need to clarify
the basis for finding the right cells on sheet 2.

Would it be possible to name the ranges on sheet 2 and
then refer to these names?

John

 

[BLB]

Hi John,

I've attached a simplified example of what I am trying to accomplish.
To find the right cell on sheet 2, it has to search by the category i
column A and by the month, but then also it has to find the MIN and MA
for each month.

Sheet1 is a summary of Sheet 2 where it sums the 2 tables together.
want the comment to break out the 2 numbers so you can see what th
total is comprised of. The cell references on the original example i
this thread do exactly match the attachment, but hopefully you ca
better understand what I am trying to do.

Or maybe trying to do a comment is not the way to go? I just need
window to pop up(that is usually hidden), when you click on that cel
to see the breakdown of the numbers.

Thanks for all your help.
Beck

Attachment filename: forum.xls
Download attachment: http://www.excelforum.com/attachment.php?postid=56910