How to make certain text in a comment bold

Thanks Thanks:  0
Likes Likes:  0
Results 1 to 8 of 8

Thread: How to make certain text in a comment bold

  1. #1
    New Member
    Join Date
    Apr 2009
    Posts
    34
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    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
    35,599
    Post Thanks / Like
    Mentioned
    25 Post(s)
    Tagged
    1 Thread(s)

    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
    Excel 365 + Excel 2010, 2007 - Windows 10, 7
    - Want to help your helpers by posting a small, copyable, screen shot directly in your post? Look here
    - If posting vba code, please use Code Tags - like this [code]Paste code here[/code] - or use the VBHTML Maker
    - Read: Forum Rules & Forum Use Guidelines

  3. #3
    New Member
    Join Date
    Apr 2009
    Posts
    34
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    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
    35,599
    Post Thanks / Like
    Mentioned
    25 Post(s)
    Tagged
    1 Thread(s)

    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
    Excel 365 + Excel 2010, 2007 - Windows 10, 7
    - Want to help your helpers by posting a small, copyable, screen shot directly in your post? Look here
    - If posting vba code, please use Code Tags - like this [code]Paste code here[/code] - or use the VBHTML Maker
    - Read: Forum Rules & Forum Use Guidelines

  5. #5
    New Member
    Join Date
    Apr 2009
    Posts
    34
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    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
    35,599
    Post Thanks / Like
    Mentioned
    25 Post(s)
    Tagged
    1 Thread(s)

    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
    Excel 365 + Excel 2010, 2007 - Windows 10, 7
    - Want to help your helpers by posting a small, copyable, screen shot directly in your post? Look here
    - If posting vba code, please use Code Tags - like this [code]Paste code here[/code] - or use the VBHTML Maker
    - Read: Forum Rules & Forum Use Guidelines

  7. #7
    New Member
    Join Date
    Feb 2009
    Location
    Australia
    Posts
    47
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    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
    35,599
    Post Thanks / Like
    Mentioned
    25 Post(s)
    Tagged
    1 Thread(s)

    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
    Excel 365 + Excel 2010, 2007 - Windows 10, 7
    - Want to help your helpers by posting a small, copyable, screen shot directly in your post? Look here
    - If posting vba code, please use Code Tags - like this [code]Paste code here[/code] - or use the VBHTML Maker
    - Read: Forum Rules & Forum Use Guidelines

User Tag List

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