Copying comments to master sheet?

[jammin7800]

I've searched the board and not found what I'm looking for.

Here's my "Comments" issue.

I have a single workbook with 4 worksheets in it. This is a dimensional report sheet. We measure stuff and report our findings.

The first worksheet is the master sheet. The next three sheets are filled in by three different inspectors.

I've got a formula in all the appropriate cells in the master sheet that brings in values from the other 3 sheets. It uses an IF formula. If there is no value reported on any of the 3 inspection sheets, the master sheet displays the text "MISSING".

So, the Master Sheet is the compilation or summary sheet, if you will.

Is there a way to have the master sheet bring in not only the values, but also the comments of cells from the other 3 sheets? There will be no cell that has comments on more than one inspector sheet.

Right now, I'm using Copy -> Paste Special -> Comments. I'm looking to automate this, so that as soon as an inspector fills in a value and adds a comment, both items get put into the master sheet automatically.

 

[Oorang]

You can use a user defined function. Press Alt F11 to launch the Visual Basic Editor (VBE). Ine the VBE, from the insert menu, select "Module" (not "Class Module"). Paste the following code into the module. Then in Excel you can just type:

=GetComment(A1)

Public Function GetComment(reference As Excel.Range) As String
    GetComment = reference.Cells(1, 1).Comment.Text
End Function

 

[EugeneCarter]

You can use a user defined function. Press Alt F11 to launch the Visual Basic Editor (VBE). Ine the VBE, from the insert menu, select "Module" (not "Class Module"). Paste the following code into the module. Then in Excel you can just type:

=GetComment(A1)

Public Function GetComment(reference As Excel.Range) As String
    GetComment = reference.Cells(1, 1).Comment.Text
End Function

If I put this into my personal workbook, and then add a UDF in any new workbook, do I aways have to put
=PERSONAL.XLS!GetComment(A1)
or can I do something different, so that I can just put
=GetComment(A1)

 

[jammin7800]

Thank you.

When I type in GetComment(A1) and hit the enter key, I get an erro message saying it can't find project or library. When I click 'OK', a window comes up with a bunch of different libraries, most of which are unchecked. Which project of library do I need to add?

Also, I should state I'm using Excel 2003

 

[EugeneCarter]

Thank you.

When I type in GetComment(A1) and hit the enter key, I get an erro message saying it can't find project or library. When I click 'OK', a window comes up with a bunch of different libraries, most of which are unchecked. Which project of library do I need to add?

Also, I should state I'm using Excel 2003

I had to close & open Excel to get it to recognize it. I don't know if that was the issue, or if it was coincidental.

 

[jammin7800]

Maybe I'm a complete idiot, but I have a few questions about the GetComment module:

1) Do I type "=GetComment(A1)" into cell A1 on the master sheet?
2) If I type it into different cells, do I need to change the cell reference?
3) If I do this, won't that overwrite the formula in that cell?

I'm looking to get comments AND values from cells in sheets 2 thru 4 automatically put into the master sheet, sheet 1.

 

[EugeneCarter]

Maybe I'm a complete idiot, but I have a few questions about the GetComment module:

1) Do I type "=GetComment(A1)" into cell A1 on the master sheet?
2) If I type it into different cells, do I need to change the cell reference?
3) If I do this, won't that overwrite the formula in that cell?

I'm looking to get comments AND values from cells in sheets 2 thru 4 automatically put into the master sheet, sheet 1.

What this does, is convert the comment from another location (ie A1) into the value of a cell somewhere else. It doesn't create a comment that appears with the same comments from the other reference location... let me think a sec on what to do for that.

 

[EugeneCarter]

K, this isn't coded well, but it'll give you an idea.

    Selection.ClearComments
    Selection.AddComment
    myReference = Selection.Formula
    myText = Range(myReference).Comment.Text
    With Selection
        .Comment.Visible = True
        .Comment.Text Text:=myText
    End With

There's chance for the cell to not have a formula, not have any comment, etc.

 

[jammin7800]

How do I run this?

 

[Oorang]

If I put this into my personal workbook, and then add a UDF in any new workbook, do I aways have to put
=PERSONAL.XLS!GetComment(A1)

AFAIK You need the prefix.

When I type in GetComment(A1) and hit the enter key, I get an erro message saying it can't find project or library. When I click 'OK', a window comes up with a bunch of different libraries, most of which are unchecked. Which project of library do I need to add?

That will work "out of the box". Redo the instructions to verify no mistake was made.

1) Do I type "=GetComment(A1)" into cell A1 on the master sheet?
2) If I type it into different cells, do I need to change the cell reference?
3) If I do this, won't that overwrite the formula in that cell?

1.) You type it in the formula in the cell where you want the results to appear.
2.) "A1" should be a reference to the cell that has the comment you want. Change the reference as needed.
3.) Don't put it in a cell you want to keep.