| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
|
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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 |
|
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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 |
|
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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 |
|
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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 |
|
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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 |
|
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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 |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Reusing formula | Excel Discussion (Misc queries) | |||
| Dynamic Range with unused formula messing up x axis on dynamic graph | Charts and Charting in Excel | |||
| Match then lookup | Excel Worksheet Functions | |||
| Formula Problem - interrupted by #VALUE! in other cells!? | Excel Worksheet Functions | |||
| Formula checking multiple worksheets | Excel Worksheet Functions | |||
Linear Mode
