• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 279
  • Last Modified:

Comment formating

Is there a way to set a default format for  Comments.
0
mikecox_
Asked:
mikecox_
  • 5
  • 5
1 Solution
 
Martin LissRetired ProgrammerCommented:
The answer is probably no, but can you elaborate a bit more?
0
 
mikecox_Author Commented:
The comment font is too small, I can change the font using the Format drop down so I thought there must be a way to set a default format.

I forgot to ask... can you search comments?  Like, enter a search keyword that looks in all comments?
0
 
Martin LissRetired ProgrammerCommented:
Here's a macro you can use that sets the font size of a comment to 16 which you can of course change to any size you like. Edit the macro and assign a shortcut letter to it like m and them you can just select the cell where you'd like the comment to be and then do ctrl+m.

Sub AddComment()
'
' AddComment Macro
'
' Keyboard Shortcut: Ctrl+m
'
    Dim com As String
    com = InputBox("Please enter the comment")
    ActiveCell.AddComment
    ActiveCell.Comment.Visible = False
    ActiveCell.Comment.Shape.TextFrame.Characters.Font.Size = 16
    ActiveCell.Comment.Text Text:=com & Chr(10) & ""
    
End Sub

Open in new window


I believe I have the answer to your other question too, but please create a new question. And in case I miss it please post the URL here.
0
Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

 
mikecox_Author Commented:
Great answer!

I've been creating macros in another worksheet without a problem, when I tried to create this one in my Person macro file; so I could use it in all my worksheets I got this error.

 Unhide  

I haven't seen this before and don't have a clue where the  Unhide command is?
0
 
Rory ArchibaldCommented:
It's on the View tab in the Window section.
0
 
Martin LissRetired ProgrammerCommented:
I've never tried to share macros between workbooks, but shouldn't you put them in a module rather than a sheet?
0
 
mikecox_Author Commented:
As I understand it macros can be stored in a single workbook or stored personal macro workbook where it can be used regardless of what workbook you in.

personal macro book
But I heard that in a Lyndo.com video, so I'll just go back and review that video.
0
 
mikecox_Author Commented:
Very cool.  Thanks
0
 
Martin LissRetired ProgrammerCommented:
You're welcome and I'm glad I was able to help. Are you still interested in searching?

Marty - MVP 2009 to 2012
0
 
mikecox_Author Commented:
No, too busy; I'll figure it out later.
0
 
Martin LissRetired ProgrammerCommented:
Okay. I've already done it so here's a gift.

The first macro finds all the comments that contain the text string input by the user and places a red border around each comment's parent cell. The second macro removes the red borders.

Sub FindComment()
 Dim comComment As Comment
 Dim strText As String
 Dim rngCmt As Range
 Dim intCount As Integer
 Dim strMessage As String
 Const QUOTE = """"
 
 strText = InputBox("Please enter the text you want to find")
 
 For Each comComment In ActiveSheet.Comments
    If InStr(1, comComment.Text, strText) Then
        intCount = intCount + 1
        Set rngCmt = comComment.Parent
        With rngCmt.Borders(xlEdgeLeft)
            .LineStyle = xlContinuous
            .Color = -16776961
            .TintAndShade = 0
            .Weight = xlThick
        End With
        With rngCmt.Borders(xlEdgeTop)
            .LineStyle = xlContinuous
            .Color = -16776961
            .TintAndShade = 0
            .Weight = xlThick
        End With
        With rngCmt.Borders(xlEdgeBottom)
            .LineStyle = xlContinuous
            .Color = -16776961
            .TintAndShade = 0
            .Weight = xlThick
        End With
        With rngCmt.Borders(xlEdgeRight)
            .LineStyle = xlContinuous
            .Color = -16776961
            .TintAndShade = 0
            .Weight = xlThick
        End With
    End If
Next
strMessage = "Found " & intCount & " comments containing " & QUOTE & strText & QUOTE
If intCount > 0 Then
    MsgBox strMessage & vbCrLf & vbCrLf & "Press Ctrl+Shift+R to remove the red borders"
Else
    MsgBox strMessage
End If
End Sub

Sub RemoveRedBorder()
 Dim comComment As Comment
 Dim rngCmt As Range
 
 For Each comComment In ActiveSheet.Comments
    Set rngCmt = comComment.Parent
    rngCmt.Borders(xlEdgeLeft).LineStyle = xlNone
    rngCmt.Borders(xlEdgeTop).LineStyle = xlNone
    rngCmt.Borders(xlEdgeBottom).LineStyle = xlNone
    rngCmt.Borders(xlEdgeRight).LineStyle = xlNone
Next
End Sub

Open in new window

0

Featured Post

Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

  • 5
  • 5
Tackle projects and never again get stuck behind a technical roadblock.
Join Now