Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Formulas as comments
I had a great add in for Excel 2000 & 2003 that I could click on a cell and
press CTRL+SHIFT+A and the formula from the cell would get put into the comment for the cell, and the comment box would remain visible even when another cell was highlighted. Unfortunately the same add in does not work in 2007. As I have worksheets with dozens of formulas it is not convenient to manually cut & paste the formulas in. I am looking for a macro, add in, etc that would allow me to do the equivalent. Thank you in advance! |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Formulas as comments
If your goal is visible formulas, don't bother
Press CRTL + ~ (tilde) to display formulas "Jeff M" wrote: I had a great add in for Excel 2000 & 2003 that I could click on a cell and press CTRL+SHIFT+A and the formula from the cell would get put into the comment for the cell, and the comment box would remain visible even when another cell was highlighted. Unfortunately the same add in does not work in 2007. As I have worksheets with dozens of formulas it is not convenient to manually cut & paste the formulas in. I am looking for a macro, add in, etc that would allow me to do the equivalent. Thank you in advance! |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Formulas as comments
Tevuna:
CTRL+~ does not help. I need to take excerpt from my workbook and print them out (including the comments) so that people can understand both the numbers and the formulas that back them up. What I really need the formulas in the comments fields. I make extensive use of named fields so that the formulas make sense visually. "Tevuna" wrote: If your goal is visible formulas, don't bother Press CRTL + ~ (tilde) to display formulas "Jeff M" wrote: I had a great add in for Excel 2000 & 2003 that I could click on a cell and press CTRL+SHIFT+A and the formula from the cell would get put into the comment for the cell, and the comment box would remain visible even when another cell was highlighted. Unfortunately the same add in does not work in 2007. As I have worksheets with dozens of formulas it is not convenient to manually cut & paste the formulas in. I am looking for a macro, add in, etc that would allow me to do the equivalent. Thank you in advance! |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
Formulas as comments
I'm surprised that your existing addin didn't work with xl2007. Are you sure
you enabled macros when you opened that addin? But this may work ok for you: Option Explicit Sub PutFormulasInComments() Dim myCell As Range Dim myStartingRng As Range Dim myRngToFix As Range Dim lArea As Double Set myStartingRng = ActiveSheet.UsedRange 'or 'Set myStartingRng = Selection Set myRngToFix = Nothing On Error Resume Next Set myRngToFix = Intersect(myStartingRng, _ myStartingRng.Cells.SpecialCells(xlCellTypeFormula s)) On Error GoTo 0 If myRngToFix Is Nothing Then MsgBox "No formulas in Selection" Exit Sub End If myRngToFix.ClearComments For Each myCell In myRngToFix.Cells myCell.AddComment Text:=GetFormula(myCell) With myCell.Comment .Visible = True 'I'd use False .Shape.TextFrame.AutoSize = True If .Shape.Width 300 Then lArea = .Shape.Width * .Shape.Height .Shape.Width = 200 .Shape.Height = (lArea / 200) * 1.2 End If End With Next myCell End Sub Function GetFormula(Rng As Range) Dim myFormula As String GetFormula = "" With Rng.Cells(1) If .HasFormula Then If Application.ReferenceStyle = xlA1 Then myFormula = .Formula Else myFormula = .FormulaR1C1 End If If .HasArray Then GetFormula = "{=" & Mid(myFormula, 2, Len(myFormula)) & "}" Else GetFormula = myFormula End If End If End With End Function There are two lines that you may want to review: Set myStartingRng = ActiveSheet.UsedRange 'or 'Set myStartingRng = Selection If you really want all the cells on the worksheet processed, then use the top "Set" line. On the other hand, if you find that you like to select a particular cell, then just process those cells, you could use the second formula. In fact, if you select all the cells first, you could always use the second line. If you're new to macros, you may want to read David McRitchie's intro at: http://www.mvps.org/dmcritchie/excel/getstarted.htm Jeff M wrote: I had a great add in for Excel 2000 & 2003 that I could click on a cell and press CTRL+SHIFT+A and the formula from the cell would get put into the comment for the cell, and the comment box would remain visible even when another cell was highlighted. Unfortunately the same add in does not work in 2007. As I have worksheets with dozens of formulas it is not convenient to manually cut & paste the formulas in. I am looking for a macro, add in, etc that would allow me to do the equivalent. Thank you in advance! -- Dave Peterson |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
CELLS NOT CALC FORMULAS - VALUES STAY SME FORMULAS CORRECT?? HELP | Excel Worksheet Functions | |||
Having comments copied when using formulas | Excel Worksheet Functions | |||
in excel useing comments how do you add clip art to comments? | New Users to Excel | |||
formulas in comments in excel | Excel Discussion (Misc queries) | |||
Comments Only | Excel Discussion (Misc queries) |