How to make certain text in a comment bold

Bravurian17

New Member
Joined
Apr 16, 2009
Messages
34
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:

47924397.jpg

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

Excel Facts

Formula for Yesterday
Name Manager, New Name. Yesterday =TODAY()-1. OK. Then, use =YESTERDAY in any cell. Tomorrow could be =TODAY()+1.
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. :cool:
 
Upvote 0
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. :cool:
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)
 
Upvote 0
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
 
Upvote 0
Re: Solved It

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:

<font face=Courier New><SPAN style="color:#00007F">Dim</SPAN> UserN <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">String</SPAN><br><SPAN style="color:#00007F">Dim</SPAN> commt <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">String</SPAN><br><SPAN style="color:#00007F">Dim</SPAN> myBolds<br><SPAN style="color:#00007F">Dim</SPAN> i <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Long</SPAN><br><br>UserN = Application.UserName<br>myBolds = Array(UserN, "Start:", "End:", "Where:")<br>commt = UserN & Chr(10) & Chr(10) _<br>        & "Start: " & StartC & " - " & StartT & Chr(10) _<br>        & "End: " & <SPAN style="color:#00007F">End</SPAN>C & " - " & <SPAN style="color:#00007F">End</SPAN>T & Chr(10) _<br>        & "Where: " & WhereT<br><SPAN style="color:#00007F">With</SPAN> Cells(Selection.Row, Selection.Column)<br>    .ClearComments<br>    .AddComment<br>    .Comment.Visible = <SPAN style="color:#00007F">False</SPAN><br>    .Comment.Shape.AutoShapeType = msoShapeRoundedRectangle<br>    .Comment.Text Text:=commt<br>    <SPAN style="color:#00007F">With</SPAN> .Comment.Shape.TextFrame<br>        <SPAN style="color:#00007F">For</SPAN> i = 0 <SPAN style="color:#00007F">To</SPAN> <SPAN style="color:#00007F">UBound</SPAN>(myBolds)<br>            <SPAN style="color:#00007F">With</SPAN> .Characters(InStr(1, commt, myBolds(i)), Len(myBolds(i))).Font<br>                .Bold = <SPAN style="color:#00007F">True</SPAN><br>            <SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">With</SPAN><br>        <SPAN style="color:#00007F">Next</SPAN> i<br>        .Characters.Font.Size = 12<br>    End <SPAN style="color:#00007F">With</SPAN><br>    .Comment.Shape.TextFrame.AutoSize = <SPAN style="color:#00007F">True</SPAN><br>End <SPAN style="color:#00007F">With</SPAN></FONT>
 
Upvote 0
Re: Solved It

****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.
 
Upvote 0
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.
 
Upvote 0

Forum statistics

Threads
1,213,479
Messages
6,113,894
Members
448,530
Latest member
yatong2008

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top