Insert Comment and Shift+F2 in macro

[DPARDY]

Seems easy, but...I need a macro that will check any cell selected for a comment and then go into edit mode. If no comment, insert and go into edit mode. Thanks as always.

 

[AlphaFrog]

Sub Commentator()
    If ActiveCell.Comment Is Nothing Then ActiveCell.AddComment
    SendKeys "+{F2}", True
End Sub

 

[DPARDY]

Sub Commentator()
    If ActiveCell.Comment Is Nothing Then ActiveCell.AddComment
    SendKeys "+{F2}", True
End Sub

AlphaFrog, that worked, thank you for that. How do I format the comment at the same time?

 

[AlphaFrog]

You're welcome.
No need to quote the code back unless you are referring to specific part of it.

Sub Commentator()
    If ActiveCell.Comment Is Nothing Then ActiveCell.AddComment
    With ActiveCell.Comment.Shape
        .AutoShapeType = msoShapeRoundedRectangle
        .TextFrame.Characters.Font.Name = "Tahoma"
        .TextFrame.Characters.Font.Size = 8
        .TextFrame.Characters.Font.ColorIndex = 2
        .Line.ForeColor.RGB = RGB(0, 0, 0)
        .Line.BackColor.RGB = RGB(255, 255, 255)
        .Fill.Visible = msoTrue
        .Fill.ForeColor.RGB = RGB(58, 82, 184)
        .Fill.OneColorGradient msoGradientDiagonalUp, 1, 0.23
    End With
    SendKeys "+{F2}", True
End Sub

 

[DPARDY]

You're welcome.
No need to quote the code back unless you are referring to specific part of it.

Sub Commentator()
    If ActiveCell.Comment Is Nothing Then ActiveCell.AddComment
    With ActiveCell.Comment.Shape
        .AutoShapeType = msoShapeRoundedRectangle
        .TextFrame.Characters.Font.Name = "Tahoma"
        .TextFrame.Characters.Font.Size = 8
        .TextFrame.Characters.Font.ColorIndex = 2
        .Line.ForeColor.RGB = RGB(0, 0, 0)
        .Line.BackColor.RGB = RGB(255, 255, 255)
        .Fill.Visible = msoTrue
        .Fill.ForeColor.RGB = RGB(58, 82, 184)
        .Fill.OneColorGradient msoGradientDiagonalUp, 1, 0.23
    End With
    SendKeys "+{F2}", True
End Sub

AlphaFrog, once again it worked perfect. Not sure the people I am presenting this to will think the format might be a little over the top. Would do I need to change for white background and a black 2pt line around??? Thanks again.

 

[DPARDY]

AlphaFrog, once again it worked perfect. Not sure the people I am presenting this to will think the format might be a little over the top. Would do I need to change for white background and a black 2pt line around??? Thanks again.

 

[AlphaFrog]

You can easily record a macro and make the formatting changes to one comment box. Then copy the relevant code from your recorded macro to the macro I provided.

 

[DPARDY]

I tried that a few times, but with no luck. I am new to this but am tring...


Sub COMMENT_ALL()
If ActiveCell.Comment Is Nothing Then ActiveCell.AddComment

With ActiveCell.Comment.Font

With Selection.Font
.Name = "Arial"
.FontStyle = "Regular"
.Size = 10
.Strikethrough = False
.Superscript = False
.Subscript = False
.OutlineFont = False
.Shadow = False
.Underline = xlUnderlineStyleNone
.ColorIndex = xlAutomatic
.TintAndShade = 0
.ThemeFont = xlThemeFontNone
End With
With Selection
.HorizontalAlignment = xlLeft
.VerticalAlignment = xlCenter
.ReadingOrder = xlContext
.Orientation = xlHorizontal
.AutoSize = False
End With

SendKeys "+{F2}", True

End Sub

 

[AlphaFrog]

Sub COMMENT_ALL()
   
    If ActiveCell.Comment Is Nothing Then ActiveCell.AddComment
   
    With ActiveCell.Comment.Shape
        .AutoShapeType = msoShapeRectangle  'Shape Type
        With .TextFrame
            .HorizontalAlignment = xlLeft
            .VerticalAlignment = xlCenter
            .ReadingOrder = xlContext
            .Orientation = msoTextOrientationHorizontal
            .AutoSize = False
       
            With .Characters.Font
                .Name = "Tahoma"                'Font name
                .Size = 10                      'Font size
                .ColorIndex = xlAutomatic       'Font color
            End With
        End With
       
        With .Line
            .ForeColor.RGB = RGB(0, 0, 0)
            .BackColor.RGB = RGB(255, 255, 255)
        End With
       
        With .Fill
            .Visible = msoTrue
            .ForeColor.RGB = vbWhite              'Background color
            '.OneColorGradient msoGradientDiagonalUp, 1, 0.23  'Background gradient
        End With
    End With
   
    'Comment Edit mode
    SendKeys "+{F2}", True
   
End Sub

 

[DPARDY]

Sub COMMENT_ALL()
  
    If ActiveCell.Comment Is Nothing Then ActiveCell.AddComment
  
    With ActiveCell.Comment.Shape
        .AutoShapeType = msoShapeRectangle  'Shape Type
        With .TextFrame
            .HorizontalAlignment = xlLeft
            .VerticalAlignment = xlCenter
            .ReadingOrder = xlContext
            .Orientation = msoTextOrientationHorizontal
            .AutoSize = False
      
            With .Characters.Font
                .Name = "Tahoma"                'Font name
                .Size = 10                      'Font size
                .ColorIndex = xlAutomatic       'Font color
            End With
        End With
      
        With .Line
            .ForeColor.RGB = RGB(0, 0, 0)
            .BackColor.RGB = RGB(255, 255, 255)
        End With
      
        With .Fill
            .Visible = msoTrue
            .ForeColor.RGB = vbWhite              'Background color
            '.OneColorGradient msoGradientDiagonalUp, 1, 0.23  'Background gradient
        End With
    End With
  
    'Comment Edit mode
    SendKeys "+{F2}", True
  
End Sub

Perfect, thanks AlphaFrog.