Results 1 to 8 of 8

How to make certain text in a comment bold

This is a discussion on How to make certain text in a comment bold within the Excel Questions forums, part of the Question Forums category; I need to figure out how to make certain text bold in a comment using vba . I'm making it ...

  1. #1
    New Member
    Join Date
    Apr 2009
    Posts
    34

    Exclamation How to make certain text in a comment bold

    I need to figure out how to make certain text bold in a comment using vba. I'm making it so it adds a comment that looks like this:


    I've searched other forums and none have helped. I need to do this in vba. Thanks in advance.

  2. #2
    MrExcel MVP
    Moderator
    Peter_SSs's Avatar
    Join Date
    May 2005
    Location
    Macksville, Australia
    Posts
    25,556

    Default Re: How to make certain text in a comment bold

    This thread looks like it might be useful for you, particularly from Tom's post at #10
    Hope this helps, good luck.
    Peter

    - Read: Posting Guidelines, Forum Rules & FAQs
    - Want to post a small screen shot? Try one of these Excel jeanie, MrExcel HTML Maker or Borders-Copy-Paste (To test: Test Here)
    - If posting vba code, please use Code Tags - like this [code]Paste code here[/code] - or use the VBHTML Maker

  3. #3
    New Member
    Join Date
    Apr 2009
    Posts
    34

    Exclamation Re: How to make certain text in a comment bold

    Thanks for the quick reply. What about when I'm adding the comment? Is there a way to format it at that point? Again, thanks.

  4. #4
    MrExcel MVP
    Moderator
    Peter_SSs's Avatar
    Join Date
    May 2005
    Location
    Macksville, Australia
    Posts
    25,556

    Default Re: How to make certain text in a comment bold

    Quote Originally Posted by Bravurian17 View Post
    Thanks for the quick reply. What about when I'm adding the comment? Is there a way to format it at that point? Again, thanks.
    Working with cell comments is not a strength of mine. However, I would have thought your code might enter the comment then have a loop that basically runs Tom's code for each of the 'special' words you have (Start:, End: etc)
    Hope this helps, good luck.
    Peter

    - Read: Posting Guidelines, Forum Rules & FAQs
    - Want to post a small screen shot? Try one of these Excel jeanie, MrExcel HTML Maker or Borders-Copy-Paste (To test: Test Here)
    - If posting vba code, please use Code Tags - like this [code]Paste code here[/code] - or use the VBHTML Maker

  5. #5
    New Member
    Join Date
    Apr 2009
    Posts
    34

    Thumbs up Solved It

    Figured it out, took alot more code than i thought it would. Thanks for the reply though.
    Here's what I did, hope it helps someone else.
    Code:
    Dim UserN As String
    Dim commt As String
    UserN = Application.UserName
    commt = UserN & Chr(10) & Chr(10) _
            & "Start: " & StartC & " - " & StartT & Chr(10) _
            & "End: " & EndC & " - " & EndT & Chr(10) _
            & "Where: " & WhereT
    With Selection
            With Cells(Selection.Row, Selection.Column)
            .ClearComments
            .AddComment
            .Comment.Visible = False
            .Comment.Shape.AutoShapeType = msoShapeRoundedRectangle
            .Comment.Text Text:=commt
                With .Comment.Shape.TextFrame
                    ' Username
                    With .Characters(1, Len(UserN)).Font
                        .Bold = True
                    End With
                    ' Start:
                    With .Characters(InStr(1, commt, "Start:"), 6).Font
                        .Bold = True
                    End With
                    ' End:
                    With .Characters(InStr(1, commt, "End:"), 4).Font
                        .Bold = True
                    End With
                    ' Where:
                    With .Characters(InStr(1, commt, "Where:"), 6).Font
                        .Bold = True
                    End With
                    With .Characters.Font
                        .Size = 12
                    End With
                End With
            .Comment.Shape.TextFrame.AutoSize = True
            End With
    End With

  6. #6
    MrExcel MVP
    Moderator
    Peter_SSs's Avatar
    Join Date
    May 2005
    Location
    Macksville, Australia
    Posts
    25,556

    Default Re: Solved It

    Quote Originally Posted by Bravurian17 View Post
    Figured it out,
    Great, more of a sense of achievement that way!


    ... took alot more code than i thought it would.
    Having a bit of extra code is not a problem if it does what you want and without undue slowness, and I think your code does that. However, it could be shortened a bit (just in the written amount of code, not the processing steps/time) by the sort of looping I was suggesting earlier.

    A couple of other comments:

    1. Your
    With Selection
    .
    .
    End With
    does nothing as far as I can see, so I have removed it.

    2. I'm also wondering about:
    With Cells(Selection.Row, Selection.Column)

    If you just have a single cell selected, the comment will be added to that cell. However, if you have a range selected the comment will be added to the top left cell in the selection, even if that cell is not the active cell. For example if you click in H20 and drag up and left to G10, your active cell is still H20, but the code will put the comment in H10. Is that what you intended?

    Anyway, here's a slighly amended code for you to consider:

    Dim UserN As String
    Dim commt As String
    Dim myBolds
    Dim i As Long

    UserN = Application.UserName
    myBolds = Array(UserN, "Start:", "End:", "Where:")
    commt = UserN & Chr(10) & Chr(10) _
            & "Start: " & StartC & " - " & StartT & Chr(10) _
            & "End: " & EndC & " - " & EndT & Chr(10) _
            & "Where: " & WhereT
    With Cells(Selection.Row, Selection.Column)
        .ClearComments
        .AddComment
        .Comment.Visible = False
        .Comment.Shape.AutoShapeType = msoShapeRoundedRectangle
        .Comment.Text Text:=commt
        With .Comment.Shape.TextFrame
            For i = 0 To UBound(myBolds)
                With .Characters(InStr(1, commt, myBolds(i)), Len(myBolds(i))).Font
                    .Bold = True
                End With
            Next i
            .Characters.Font.Size = 12
        End With
        .Comment.Shape.TextFrame.AutoSize = True
    End With
    Hope this helps, good luck.
    Peter

    - Read: Posting Guidelines, Forum Rules & FAQs
    - Want to post a small screen shot? Try one of these Excel jeanie, MrExcel HTML Maker or Borders-Copy-Paste (To test: Test Here)
    - If posting vba code, please use Code Tags - like this [code]Paste code here[/code] - or use the VBHTML Maker

  7. #7
    New Member
    Join Date
    Feb 2009
    Location
    Australia
    Posts
    47

    Default Re: Solved It

    Quote Originally Posted by Peter_SSs View Post
    ****With .Comment.Shape.TextFrame
    ********For i = 0 To UBound(myBolds)
    ************With .Characters(InStr(1, commt, myBolds(i)), Len(myBolds(i))).Font
    ****************.Bold = True
    ************End With
    ********Next i
    ********.Characters.Font.Size = 12
    ****End With
    ****.Comment.Shape.TextFrame.AutoSize = True
    End With
    I dont know why, but for some reason this makes all my comment Bold. The above method works hwoever.

  8. #8
    MrExcel MVP
    Moderator
    Peter_SSs's Avatar
    Join Date
    May 2005
    Location
    Macksville, Australia
    Posts
    25,556

    Default Re: Solved It

    The code in this thread was about bolding particular words in Comments. Are you trying to bold the same words?
    In addition, the codes are not complete codes. For example, the variables StartC, EndC and WhereT are not defined.

    If you need help you would need to provide specifics about what you have and what you are trying to achieve.
    Hope this helps, good luck.
    Peter

    - Read: Posting Guidelines, Forum Rules & FAQs
    - Want to post a small screen shot? Try one of these Excel jeanie, MrExcel HTML Maker or Borders-Copy-Paste (To test: Test Here)
    - If posting vba code, please use Code Tags - like this [code]Paste code here[/code] - or use the VBHTML Maker

Tags for this Thread

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  


DMCA.com