ExcelBanter

ExcelBanter (https://www.excelbanter.com/)
-   Excel Worksheet Functions (https://www.excelbanter.com/excel-worksheet-functions/)
-   -   Formula in comments? (https://www.excelbanter.com/excel-worksheet-functions/124889-formula-comments.html)

Manju

Formula in comments?
 
Is it possible to write a formula in comment
What I want is to display a 'value' (say percentage) in comment when I
move the mouse over that particular cell.
If A1 is having value 10 and A2 has a value of 200, when I move the
cursor on cell A1, the comment should display 5.00%.(I know I can add
another column A3 and write a formula over there A1/A2 * 100 but I need
to show it in the comments).
Regards


JLatham

Formula in comments?
 
Sorry, no. A comment is just a comment.

It could be done with VBA and the Worksheet_Change() event, at the expense
of a lot of time consumed rewriting your comments in that column every time
something else changed.

"Manju" wrote:

Is it possible to write a formula in comment
What I want is to display a 'value' (say percentage) in comment when I
move the mouse over that particular cell.
If A1 is having value 10 and A2 has a value of 200, when I move the
cursor on cell A1, the comment should display 5.00%.(I know I can add
another column A3 and write a formula over there A1/A2 * 100 but I need
to show it in the comments).
Regards



JLatham

Formula in comments?
 
If you are interested in the VBA solution, it is below. This is very
specific to the conditions you set out: working with values in columns A and
B, comment to go into column B cell.

To put this code into the proper place (goes into the Worksheet module for
the sheet with the numbers you're working with) : right-click on the sheet's
tab and choose View Code. Cut and paste the code below into that module,
close the VB Editor and then start populating columns A and B on your sheet.

Since this only works 'per row' when a change is made in columns A or B
only, existing entries will not get a comment unless you manually change one
of the two values. It also works very quickly since it is specific in only
creating a single comment based on a change in a single row.

Private Sub Worksheet_Change(ByVal Target As Range)
Dim myComment As String
If Target.Cells.Count 1 Then
'multiple cells selected
Exit Sub
End If
If Target.Column 2 Then
'not in column A or B
Exit Sub
End If
Application.EnableEvents = False
Range("B" & Target.Row).ClearComments
Range("B" & Target.Row).AddComment
Range("B" & Target.Row).Comment.Visible = False
Dim anyResult As Single
myComment = "n/a"
If Range("B" & Target.Row).Value 0 Then
On Error Resume Next
myComment = Format(Range("A" & Target.Row).Value _
/ Range("B" & Target.Row).Value, "0.00%")
On Error GoTo 0
End If
Range("B" & Target.Row).Comment.Text Text:=myComment
Application.EnableEvents = True
End Sub

"Manju" wrote:

Is it possible to write a formula in comment
What I want is to display a 'value' (say percentage) in comment when I
move the mouse over that particular cell.
If A1 is having value 10 and A2 has a value of 200, when I move the
cursor on cell A1, the comment should display 5.00%.(I know I can add
another column A3 and write a formula over there A1/A2 * 100 but I need
to show it in the comments).
Regards



JLatham

Formula in comments?
 
Oops, just realized you want comment in column A, not B. Code for that is
he

Private Sub Worksheet_Change(ByVal Target As Range)
Dim myComment As String
If Target.Cells.Count 1 Then
'multiple cells selected
Exit Sub
End If
If Target.Column 2 Then
'not in column A or B
Exit Sub
End If
Application.EnableEvents = False
Range("A" & Target.Row).ClearComments
Range("A" & Target.Row).AddComment
Range("A" & Target.Row).Comment.Visible = False
Dim anyResult As Single
myComment = "n/a"
If Range("B" & Target.Row).Value 0 Then
On Error Resume Next
myComment = Format(Range("A" & Target.Row).Value _
/ Range("B" & Target.Row).Value, "0.00%")
On Error GoTo 0
End If
Range("A" & Target.Row).Comment.Text Text:=myComment
Application.EnableEvents = True
End Sub


"JLatham" wrote:

If you are interested in the VBA solution, it is below. This is very
specific to the conditions you set out: working with values in columns A and
B, comment to go into column B cell.

To put this code into the proper place (goes into the Worksheet module for
the sheet with the numbers you're working with) : right-click on the sheet's
tab and choose View Code. Cut and paste the code below into that module,
close the VB Editor and then start populating columns A and B on your sheet.

Since this only works 'per row' when a change is made in columns A or B
only, existing entries will not get a comment unless you manually change one
of the two values. It also works very quickly since it is specific in only
creating a single comment based on a change in a single row.

Private Sub Worksheet_Change(ByVal Target As Range)
Dim myComment As String
If Target.Cells.Count 1 Then
'multiple cells selected
Exit Sub
End If
If Target.Column 2 Then
'not in column A or B
Exit Sub
End If
Application.EnableEvents = False
Range("B" & Target.Row).ClearComments
Range("B" & Target.Row).AddComment
Range("B" & Target.Row).Comment.Visible = False
Dim anyResult As Single
myComment = "n/a"
If Range("B" & Target.Row).Value 0 Then
On Error Resume Next
myComment = Format(Range("A" & Target.Row).Value _
/ Range("B" & Target.Row).Value, "0.00%")
On Error GoTo 0
End If
Range("B" & Target.Row).Comment.Text Text:=myComment
Application.EnableEvents = True
End Sub

"Manju" wrote:

Is it possible to write a formula in comment
What I want is to display a 'value' (say percentage) in comment when I
move the mouse over that particular cell.
If A1 is having value 10 and A2 has a value of 200, when I move the
cursor on cell A1, the comment should display 5.00%.(I know I can add
another column A3 and write a formula over there A1/A2 * 100 but I need
to show it in the comments).
Regards



Manju

Formula in comments?
 
Thanks. I was delighted to see the result in my worksheet.

But sorry, I just gave an example of column A and B. What are the
changes I have to do if the value is say in column H and Column L.

Also, I want the percent of contenets in H,I,J,and K against column L.
That is respective percents comment in column H,I,J,and K.
Thanks in advance.

JLatham (removethis) wrote:
Oops, just realized you want comment in column A, not B. Code for that is
he

Private Sub Worksheet_Change(ByVal Target As Range)
Dim myComment As String
If Target.Cells.Count 1 Then
'multiple cells selected
Exit Sub
End If
If Target.Column 2 Then
'not in column A or B
Exit Sub
End If
Application.EnableEvents = False
Range("A" & Target.Row).ClearComments
Range("A" & Target.Row).AddComment
Range("A" & Target.Row).Comment.Visible = False
Dim anyResult As Single
myComment = "n/a"
If Range("B" & Target.Row).Value 0 Then
On Error Resume Next
myComment = Format(Range("A" & Target.Row).Value _
/ Range("B" & Target.Row).Value, "0.00%")
On Error GoTo 0
End If
Range("A" & Target.Row).Comment.Text Text:=myComment
Application.EnableEvents = True
End Sub


"JLatham" wrote:

If you are interested in the VBA solution, it is below. This is very
specific to the conditions you set out: working with values in columns A and
B, comment to go into column B cell.

To put this code into the proper place (goes into the Worksheet module for
the sheet with the numbers you're working with) : right-click on the sheet's
tab and choose View Code. Cut and paste the code below into that module,
close the VB Editor and then start populating columns A and B on your sheet.

Since this only works 'per row' when a change is made in columns A or B
only, existing entries will not get a comment unless you manually change one
of the two values. It also works very quickly since it is specific in only
creating a single comment based on a change in a single row.

Private Sub Worksheet_Change(ByVal Target As Range)
Dim myComment As String
If Target.Cells.Count 1 Then
'multiple cells selected
Exit Sub
End If
If Target.Column 2 Then
'not in column A or B
Exit Sub
End If
Application.EnableEvents = False
Range("B" & Target.Row).ClearComments
Range("B" & Target.Row).AddComment
Range("B" & Target.Row).Comment.Visible = False
Dim anyResult As Single
myComment = "n/a"
If Range("B" & Target.Row).Value 0 Then
On Error Resume Next
myComment = Format(Range("A" & Target.Row).Value _
/ Range("B" & Target.Row).Value, "0.00%")
On Error GoTo 0
End If
Range("B" & Target.Row).Comment.Text Text:=myComment
Application.EnableEvents = True
End Sub

"Manju" wrote:

Is it possible to write a formula in comment
What I want is to display a 'value' (say percentage) in comment when I
move the mouse over that particular cell.
If A1 is having value 10 and A2 has a value of 200, when I move the
cursor on cell A1, the comment should display 5.00%.(I know I can add
another column A3 and write a formula over there A1/A2 * 100 but I need
to show it in the comments).
Regards




JLatham

Formula in comments?
 
Use this to replace what you have to deal with columns H-L instead of A-B.
It's not as compact as it could be, but I think you'll find it easier to
modify if you should ever need to this way.

Private Sub Worksheet_Change(ByVal Target As Range)
Dim myComment As String
Dim columnID As String

If Target.Cells.Count 1 Then
'multiple cells selected
Exit Sub
End If
If Target.Column < Range("H1").Column Or _
Target.Column Range("L1").Column Then
'not in columns H through L
Exit Sub
End If
Application.EnableEvents = False

columnID = "H"
Range(columnID & Target.Row).ClearComments
Range(columnID & Target.Row).AddComment
Range(columnID & Target.Row).Comment.Visible = False
myComment = "n/a"
If Range("L" & Target.Row).Value 0 Then
On Error Resume Next
myComment = Format(Range(columnID & Target.Row).Value _
/ Range("L" & Target.Row).Value, "0.00%")
On Error GoTo 0
End If
Range(columnID & Target.Row).Comment.Text Text:=myComment

columnID = "I"
Range(columnID & Target.Row).ClearComments
Range(columnID & Target.Row).AddComment
Range(columnID & Target.Row).Comment.Visible = False
myComment = "n/a"
If Range("L" & Target.Row).Value 0 Then
On Error Resume Next
myComment = Format(Range(columnID & Target.Row).Value _
/ Range("L" & Target.Row).Value, "0.00%")
On Error GoTo 0
End If
Range(columnID & Target.Row).Comment.Text Text:=myComment

columnID = "J"
Range(columnID & Target.Row).ClearComments
Range(columnID & Target.Row).AddComment
Range(columnID & Target.Row).Comment.Visible = False
myComment = "n/a"
If Range("L" & Target.Row).Value 0 Then
On Error Resume Next
myComment = Format(Range(columnID & Target.Row).Value _
/ Range("L" & Target.Row).Value, "0.00%")
On Error GoTo 0
End If
Range(columnID & Target.Row).Comment.Text Text:=myComment

columnID = "K"
Range(columnID & Target.Row).ClearComments
Range(columnID & Target.Row).AddComment
Range(columnID & Target.Row).Comment.Visible = False
myComment = "n/a"
If Range("L" & Target.Row).Value 0 Then
On Error Resume Next
myComment = Format(Range(columnID & Target.Row).Value _
/ Range("L" & Target.Row).Value, "0.00%")
On Error GoTo 0
End If
Range(columnID & Target.Row).Comment.Text Text:=myComment

Application.EnableEvents = True
End Sub


"Manju" wrote:

Thanks. I was delighted to see the result in my worksheet.

But sorry, I just gave an example of column A and B. What are the
changes I have to do if the value is say in column H and Column L.

Also, I want the percent of contenets in H,I,J,and K against column L.
That is respective percents comment in column H,I,J,and K.
Thanks in advance.

JLatham (removethis) wrote:
Oops, just realized you want comment in column A, not B. Code for that is
he

Private Sub Worksheet_Change(ByVal Target As Range)
Dim myComment As String
If Target.Cells.Count 1 Then
'multiple cells selected
Exit Sub
End If
If Target.Column 2 Then
'not in column A or B
Exit Sub
End If
Application.EnableEvents = False
Range("A" & Target.Row).ClearComments
Range("A" & Target.Row).AddComment
Range("A" & Target.Row).Comment.Visible = False
Dim anyResult As Single
myComment = "n/a"
If Range("B" & Target.Row).Value 0 Then
On Error Resume Next
myComment = Format(Range("A" & Target.Row).Value _
/ Range("B" & Target.Row).Value, "0.00%")
On Error GoTo 0
End If
Range("A" & Target.Row).Comment.Text Text:=myComment
Application.EnableEvents = True
End Sub


"JLatham" wrote:

If you are interested in the VBA solution, it is below. This is very
specific to the conditions you set out: working with values in columns A and
B, comment to go into column B cell.

To put this code into the proper place (goes into the Worksheet module for
the sheet with the numbers you're working with) : right-click on the sheet's
tab and choose View Code. Cut and paste the code below into that module,
close the VB Editor and then start populating columns A and B on your sheet.

Since this only works 'per row' when a change is made in columns A or B
only, existing entries will not get a comment unless you manually change one
of the two values. It also works very quickly since it is specific in only
creating a single comment based on a change in a single row.

Private Sub Worksheet_Change(ByVal Target As Range)
Dim myComment As String
If Target.Cells.Count 1 Then
'multiple cells selected
Exit Sub
End If
If Target.Column 2 Then
'not in column A or B
Exit Sub
End If
Application.EnableEvents = False
Range("B" & Target.Row).ClearComments
Range("B" & Target.Row).AddComment
Range("B" & Target.Row).Comment.Visible = False
Dim anyResult As Single
myComment = "n/a"
If Range("B" & Target.Row).Value 0 Then
On Error Resume Next
myComment = Format(Range("A" & Target.Row).Value _
/ Range("B" & Target.Row).Value, "0.00%")
On Error GoTo 0
End If
Range("B" & Target.Row).Comment.Text Text:=myComment
Application.EnableEvents = True
End Sub

"Manju" wrote:

Is it possible to write a formula in comment
What I want is to display a 'value' (say percentage) in comment when I
move the mouse over that particular cell.
If A1 is having value 10 and A2 has a value of 200, when I move the
cursor on cell A1, the comment should display 5.00%.(I know I can add
another column A3 and write a formula over there A1/A2 * 100 but I need
to show it in the comments).
Regards





Manju

Formula in comments?
 
Excellent !
Thank you very much
I would recommend others to look into this solution. May come in handy
Is there a way to apply the code for already existing data?

Happy New Year
Regards
Manju


JLatham (removethis) wrote:
Use this to replace what you have to deal with columns H-L instead of A-B.
It's not as compact as it could be, but I think you'll find it easier to
modify if you should ever need to this way.

Private Sub Worksheet_Change(ByVal Target As Range)
Dim myComment As String
Dim columnID As String

If Target.Cells.Count 1 Then
'multiple cells selected
Exit Sub
End If
If Target.Column < Range("H1").Column Or _
Target.Column Range("L1").Column Then
'not in columns H through L
Exit Sub
End If
Application.EnableEvents = False

columnID = "H"
Range(columnID & Target.Row).ClearComments
Range(columnID & Target.Row).AddComment
Range(columnID & Target.Row).Comment.Visible = False
myComment = "n/a"
If Range("L" & Target.Row).Value 0 Then
On Error Resume Next
myComment = Format(Range(columnID & Target.Row).Value _
/ Range("L" & Target.Row).Value, "0.00%")
On Error GoTo 0
End If
Range(columnID & Target.Row).Comment.Text Text:=myComment

columnID = "I"
Range(columnID & Target.Row).ClearComments
Range(columnID & Target.Row).AddComment
Range(columnID & Target.Row).Comment.Visible = False
myComment = "n/a"
If Range("L" & Target.Row).Value 0 Then
On Error Resume Next
myComment = Format(Range(columnID & Target.Row).Value _
/ Range("L" & Target.Row).Value, "0.00%")
On Error GoTo 0
End If
Range(columnID & Target.Row).Comment.Text Text:=myComment

columnID = "J"
Range(columnID & Target.Row).ClearComments
Range(columnID & Target.Row).AddComment
Range(columnID & Target.Row).Comment.Visible = False
myComment = "n/a"
If Range("L" & Target.Row).Value 0 Then
On Error Resume Next
myComment = Format(Range(columnID & Target.Row).Value _
/ Range("L" & Target.Row).Value, "0.00%")
On Error GoTo 0
End If
Range(columnID & Target.Row).Comment.Text Text:=myComment

columnID = "K"
Range(columnID & Target.Row).ClearComments
Range(columnID & Target.Row).AddComment
Range(columnID & Target.Row).Comment.Visible = False
myComment = "n/a"
If Range("L" & Target.Row).Value 0 Then
On Error Resume Next
myComment = Format(Range(columnID & Target.Row).Value _
/ Range("L" & Target.Row).Value, "0.00%")
On Error GoTo 0
End If
Range(columnID & Target.Row).Comment.Text Text:=myComment

Application.EnableEvents = True
End Sub


"Manju" wrote:

Thanks. I was delighted to see the result in my worksheet.

But sorry, I just gave an example of column A and B. What are the
changes I have to do if the value is say in column H and Column L.

Also, I want the percent of contenets in H,I,J,and K against column L.
That is respective percents comment in column H,I,J,and K.
Thanks in advance.

JLatham (removethis) wrote:
Oops, just realized you want comment in column A, not B. Code for that is
he

Private Sub Worksheet_Change(ByVal Target As Range)
Dim myComment As String
If Target.Cells.Count 1 Then
'multiple cells selected
Exit Sub
End If
If Target.Column 2 Then
'not in column A or B
Exit Sub
End If
Application.EnableEvents = False
Range("A" & Target.Row).ClearComments
Range("A" & Target.Row).AddComment
Range("A" & Target.Row).Comment.Visible = False
Dim anyResult As Single
myComment = "n/a"
If Range("B" & Target.Row).Value 0 Then
On Error Resume Next
myComment = Format(Range("A" & Target.Row).Value _
/ Range("B" & Target.Row).Value, "0.00%")
On Error GoTo 0
End If
Range("A" & Target.Row).Comment.Text Text:=myComment
Application.EnableEvents = True
End Sub


"JLatham" wrote:

If you are interested in the VBA solution, it is below. This is very
specific to the conditions you set out: working with values in columns A and
B, comment to go into column B cell.

To put this code into the proper place (goes into the Worksheet module for
the sheet with the numbers you're working with) : right-click on the sheet's
tab and choose View Code. Cut and paste the code below into that module,
close the VB Editor and then start populating columns A and B on your sheet.

Since this only works 'per row' when a change is made in columns A or B
only, existing entries will not get a comment unless you manually change one
of the two values. It also works very quickly since it is specific in only
creating a single comment based on a change in a single row.

Private Sub Worksheet_Change(ByVal Target As Range)
Dim myComment As String
If Target.Cells.Count 1 Then
'multiple cells selected
Exit Sub
End If
If Target.Column 2 Then
'not in column A or B
Exit Sub
End If
Application.EnableEvents = False
Range("B" & Target.Row).ClearComments
Range("B" & Target.Row).AddComment
Range("B" & Target.Row).Comment.Visible = False
Dim anyResult As Single
myComment = "n/a"
If Range("B" & Target.Row).Value 0 Then
On Error Resume Next
myComment = Format(Range("A" & Target.Row).Value _
/ Range("B" & Target.Row).Value, "0.00%")
On Error GoTo 0
End If
Range("B" & Target.Row).Comment.Text Text:=myComment
Application.EnableEvents = True
End Sub

"Manju" wrote:

Is it possible to write a formula in comment
What I want is to display a 'value' (say percentage) in comment when I
move the mouse over that particular cell.
If A1 is having value 10 and A2 has a value of 200, when I move the
cursor on cell A1, the comment should display 5.00%.(I know I can add
another column A3 and write a formula over there A1/A2 * 100 but I need
to show it in the comments).
Regards







All times are GMT +1. The time now is 01:39 AM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com