linking comments between spreadsheets

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Hi, I'm new to this discussion group and relatively new to Excel. Im afraid
I have no programming experience either.
I am using Excel 2003
I have linked a number of cells between two spreadsheets so that the second
sheet "mirrors" the first one in the relevant cells and updates when changes
are made. I would also like to link the comments in these cells so they are
also mirrored and updated in the second sheet. Is this possible?

Any advice would be welcome,
Thanks
 
You'll need a macro.

Saved from a previous post:

If you want to use a user defined function to get the value and the comment --
but not the formatting, you could use:

Option Explicit
Function GetValueAndComment(FCell As Range) As Variant

Application.Volatile

Dim TCell As Range

Set TCell = Application.Caller

If TCell.Comment Is Nothing Then
'do nothing
Else
TCell.Comment.Delete
End If

If FCell.Comment Is Nothing Then
'do nothing
Else
TCell.AddComment Text:=FCell.Comment.Text
End If

If FCell.Value = "" Then
GetValueAndComment = ""
Else
GetValueAndComment = FCell.Value
End If

End Function

You'd use it like this:
=GetValueAndComment(A1)

The value in A1 would appear in the cell and the comment would get copied, too.

The application.volatile is there to update the comments if you change them.
(Changing the comment won't make the function run, but it'll catch up with the
next recalculation.)

If you're new to macros, you may want to read David McRitchie's intro at:
http://www.mvps.org/dmcritchie/excel/getstarted.htm

Short course:

Open your workbook.
Hit alt-f11 to get to the VBE (where macros/UDF's live)
hit ctrl-R to view the project explorer
Find your workbook.
should look like: VBAProject (yourfilename.xls)

right click on the project name
Insert, then Module
You should see the code window pop up on the right hand side

Paste the code in there.

Now go back to excel.
Into a test cell and type:
=GetValueAndComment(A1)
 
Hi Dave
thanks for this....I tried it out using your "short course" instruction, but
all I get in the cell I used it in is "#NAME?".

I think I would normally see this if I'd entered a formula incorrectly, but
I've checked the formula in the original cell and it seems OK.

I'll certainly have a look at David McRithie's macro intro but, as I'm off
on my hols tomorrow, it will have to wait 'til I get back. In the meantime,
if you have any suggestions for a "quick fix" to the problem, I'd welcome
your suggestions.

many thanks
 
Hi Dave
yes, thanks, checked it out and I had put the macro into the source
spreadsheet rather than into the sheet I wanted it to work in......Duh! - I
told you I was new to this! Thanks very much for your help, got it working
now
Bobby
 
Glad you got it working.
Hi Dave
yes, thanks, checked it out and I had put the macro into the source
spreadsheet rather than into the sheet I wanted it to work in......Duh! - I
told you I was new to this! Thanks very much for your help, got it working
now
Bobby
Click to expand...
 
Back
Top