AlphaFrog, that worked, thank you for that. How do I format the comment at the same time?VBA Code:Sub Commentator() If ActiveCell.Comment Is Nothing Then ActiveCell.AddComment SendKeys "+{F2}", True End Sub
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.You're welcome.
No need to quote the code back unless you are referring to specific part of it.
VBA Code: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.
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.VBA Code: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