Search code examples
vbaexcelcommentsexcel-2013

Copy a formula into a comment via macro

I am trying to create a macro to speed up a common process I need to do on a daily basis. On the spreadsheet I work with we sometimes need to overwrite formulas. To be helpful to anyone else looking at spreadsheet at a later date, we copy the original formula and enter it into a comment, then highlight the cell yellow.

(FYI - I can't write VBA from scratch, but I'm usually alright using the record macro button and then amending small parts of the VBA where required.)

My current attempts: Whenever I try to enter a comment it disregards whatever I have copied, so I cannot use the "show formulas" button to copy the formula.

I've tried using F2->Shift+Home->Ctrl C to copy the formula bar directly which I can then paste into the comment, but this copies the text itself rather than the actions to get the text... so isn't reusable on other cells. (Relative Reference is enabled).

I've also tried messing around with the clipboard but I can't get it to work.

The formula that gets spit out the current way I'm doing it via the record button:

Sub Formula_Comment()
'
' Formula_Comment Macro
'
' Keyboard Shortcut: Ctrl+q
'

    Range("AQ170").Select
    Range("AQ170").AddComment
    Range("AQ170").Comment.Visible = False
    Range("AQ170").Comment.Text Text:= _
        "Tom S:" & Chr(10) & "=IF('Visit Schedule (input)'!$X$3="""",0,$AW$60)"
    Range("AQ170").Select
    With Selection.Interior
        .Pattern = xlSolid
        .PatternColorIndex = xlAutomatic
        .Color = 65535
        .TintAndShade = 0
        .PatternTintAndShade = 0
    End With
End Sub

Does any one have any idea?

Solution

    1. The AddComment method of the Range class adds a comment. To add the comment containing the formula of the cell you would use this method like this:

      ActiveCell.AddComment ActiveCell.Formula

    2. If you also want to have your name in the comment you can add it by using UserName property of the Application class, like this:

      ActiveCell.AddComment Application.UserName & ":" & Chr(10) & ActiveCell.Formula

    3. If the cell already has a comment, this method will fail and return an error, so it is wise to delete the existant comment before creating a new one. You would do it with ClearComments method of the Range class:

      ActiveCell.ClearComments

    4. The cell can be colored using just this line:

      ActiveCell.Interior.Color = 65535

    Now, since we have all that we need we can create a sub:

    Sub Formula_Comment()
    With ActiveCell
        .ClearComments
        .AddComment Application.UserName & ":" & Chr(10) & .Formula
        .Interior.Color = 65535
    End With
End Sub

    Note that we removed the ActiveCell from most of the code, as we decided to use With block for better readability.

    Happy learning! ;)