Link comment boxes based on different cell values

[Mike]

Help!!!

I am trying to change the data within my comment boxes dependant on the cell
value and if the source changes.

In my workbook I have 4 worksheets

Sheets 1 - 3 are my data sheets where the values can change

Sheet 4 is my working sheet where I want comment boxes added to each cell
dependant on the cell value. Ie if Cell A1 = F then a comment box should be
added which takes the comment from Worksheet 1 cell A1. etc etc etc
However I also need any updates on my data sheets to be shown in the
comments boxes on worksheet 4.

Any ideas please .... ???

I hope that makes sense, if not please say and I will try to clarify it better

Thanks in advance

Mike

 

[Dave Peterson]

First, I am confused about what you want.

If cell A1 is different from F, then what happens? What are the rules?

But you may find that your life in excel will be much easier if you keep your
data that you want in cells--not comments. You'll be able to use the builtin
functions (like =vlookup()) to retrieve those values.

 

[Mike]

Hi Dave

Thanks for the response.
Ok I will have 5 states for each cell.
These will be:
F, P, S, E or <blank>

Depending on the state I need a comment added to the cell and the data for
that comment to come from cell A1 for F, A2 for P, A3 for S, A4 for E and if
<blank> no comment to be added.

If only I could do exactly what you suggested with vlookups or even just a
link to the other sheet would be fantastic, however I need to keep the state
in my cell so a comment box is the best way.

I know I can use: =getvalueandcomment(a1) statement in the actual cell to
pull in the data and comment from the corresponding cells in the other
worksheets, however - how do i get this to work in a macro that would allow
me to choose which value and comment to pull across depending on the cell
state?

Is this possible or am i trying to do something that is beyond excel?

Mike

 

[Dave Peterson]

I still wouldn't use a comment for this.

Why not just insert another column to the right of your indicator column:
=if(a1="f",sheet1!a1,if(a1="P",sheet1!a2, .....

 

[Mike]

i have about 65 columns and 200 rows!
was hoping to do this by a macro but if this isnt possible i will have to
use an IF statement.

Thanks anyway

 

[Dave Peterson]

So you want to copy the value of a cell (A1, A2, A3, A4) into the comment of a
different cell based on that cell's value?

If yes, maybe you could use a macro like this:

Option Explicit
Sub testme()
Dim myCell As Range
Dim myRng As Range
Dim res As Variant
Dim myVals As Variant
Dim myCommentStartingCell As Range

myVals = Array("F", "P", "S", "E")
Set myCommentStartingCell = Worksheets("CommentSheetName").Range("a1")

Set myRng = Selection
For Each myCell In myRng.Cells
With myCell
If .Comment Is Nothing Then
'do nothing
Else
.Comment.Delete
End If
res = Application.Match(.Value, myVals, 0)
If IsError(res) Then
'do nothing
Else
.AddComment _
Text:=myCommentStartingCell.Offset(res - 1, 0).Value
End If
End With
Next myCell

End Sub

i have about 65 columns and 200 rows!
was hoping to do this by a macro but if this isnt possible i will have to
use an IF statement.

Thanks anyway

 

[Mike]

Hi Dave

Thanks for your response
so would this macro go thru my whole spreadsheet and check the values of
each cell and where appropiate create a comment field of the right
information pulled from my other work sheet or would it need a few
alterations for that?

Sorry if this is a divvy question but i had trouble following the macro and
understanding how it was working!

Mike

 

[Dave Peterson]

Not the whole worksheet or workbook.

It only works on the range you selected.

Hi Dave

Thanks for your response
so would this macro go thru my whole spreadsheet and check the values of
each cell and where appropiate create a comment field of the right
information pulled from my other work sheet or would it need a few
alterations for that?

Sorry if this is a divvy question but i had trouble following the macro and
understanding how it was working!

Mike

 

[Mike]

Hi Dave

I have tried this macro but it keeps chucking up the following error:

'Run Time error "1004":
Application-defined or object-defined arror

Then it takes me to the following line in the code:

Else
.AddComment _
Text:=myCommentStartingCell.Offset(res - 1, 0).Value

What am i doing wrong please

Mike

 

[Dave Peterson]

It worked fine for me. Did you change anything else?

If you did, you'll want to post your current version of the code.

Hi Dave

I have tried this macro but it keeps chucking up the following error:

'Run Time error "1004":
Application-defined or object-defined arror

Then it takes me to the following line in the code:

Else
.AddComment _
Text:=myCommentStartingCell.Offset(res - 1, 0).Value

What am i doing wrong please

Mike