comment text property.

[SIXTH SENSE]

hi gurus!!!

Is there a way to change the font size and style of a comment of a certain cell?
I mean is it possible to have the sizes of comments varies in one worksheet?

thanks!!!

 

[PA HS Teacher]

Thanks Tom!
I had tried to do something similar, I knew I'd have to use character, but I couldn't get the syntax. Thank you very much. Your algorithm very cleanly formats each instance of TextToMatch. It works beautifully. You understood exactly what I was looking for. I modified it slightly for my code:

Sub HighlightSearchTerms(iRow As Integer, iCol As Integer, cText As String, TextToFormat As String)
  'Format words within comment that match the search term
If InStr(cText, TextToFormat) > 0 Then

With Cells(iRow, iCol).Comment.Shape.TextFrame
For i = 1 To Len(cText) - Len(TextToFormat) Step 1
If Mid(cText, i, Len(TextToFormat)) = TextToFormat Then
.Characters(i, Len(TextToFormat)).Font.Bold = True
.Characters(i, Len(TextToFormat)).Font.ColorIndex = 3
End If
Next i
End With

If Right(cText, Len(TextToFormat)) = TextToFormat Then
With Cells(iRow, iCol).Comment.Shape.TextFrame.Characters((Len(cText) - Len(TextToFormat)), Len(cText)).Font
.ColorIndex = 3
.Bold = True
End With
End If

End If
End Sub

Now I can "query" my database of problems, and the search terms appear in bright red. You've made my night.

 

[PA HS Teacher]

For future reference:
I also added:
cText = UCase(cText)
TextToFormat = UCase(TexToFormat)

This way the code will not distinguish between upper and lower case.

e.g. If TextToFormat is "word"
and cText is "This Word"

It will format "Word" where as without this change it would only have formatted "word"

 

[Tom Urtis]

Thanks for the follow-ups.

FYI, I intentionally coded it the way I did because many words in the English language have more than one meaning. Using your example of "Excel" yesterday, in the context you used it, it was meant to imply the Microsoft spreadsheet program. In the case of a comment whose text is "Hershey students excel at athletics and computer programs such as Microsoft Excel.", the third word "excel" would not have been intended for inclusion of the special formatting. Such is the world of coding I guess, where design meets intention, separated by nuance.

 

[mgade]

I hope none of you mind me carrying this a little further? If so I will start a new thread instead, so please object if this threadjacking is provoking.

I need exactly the functionallity in the above subs - but I need to be able to call it from a UDF. I have made a spreadsheet that delivers a lot of explanations in comments to cells, and I would very much like to make a few titles/headlines to help the user find the relevant part of a longer comment. Something like:

*Strong title1*
Babble babble 1

*Strong title2*
Babble babble 2

It would be easy to format the titles when they get successively created, rather than afterwards, by calling the above subs. But that depends on whether it is at all possible of course! And calling the subs from the UDF does not work either.

I have tried both strategies to no avail of course.

Any help much appreciated!

 

[PA HS Teacher]

I know that UDF's are not supposed to be able to change the enviroment. I've played around a bit with UDF's that will add a comment to a cell. This this works.
However, I have been unsucessful at creating a UDF that will change the formatting of the text within a comment, like the routines above. Why do you need to do this through a UDF?

Can you run code? You could loop throuh all of the cells in question, and run the sub above.

 

[mgade]

I need it to run as a UDF because this particular spreadsheet is part of several "monolitic" models. I tried to run the sub (callimng it from the function), but it failed. Hmm are you sure it is possible?

 

[PA HS Teacher]

I'm not sure if you can call a sub from a function or not.

I am sure it's possible to add a comment to a cell using a function.

I do not believe it is possible to format a cell from a fuction. (but not 100% sure)

I do not know, and have not had any luck formatting the text of a comment from a UDF.

 

[mgade]

I agree that it is possible to add and delete comments - I do that big time

This thread had me experiment a bit, and have been able to call a sub from a function. It didn't do anything meaningful but just fired a MsgBox. It worked though.

The sub above (HighlightSearchTerms) thus should fire, but the output is questionable (#VALUE). I will poke it around for a while as I think it is a wierd restriction not to be able to format comments from the same UDF that created it.

 

[mgade]

Allright. I give up

Conclusion: The sub can do exactly what the function ca do. HighlightSearchTerms does not give any errors now - but the comments are indifferent/unchanged!

Thanks for helping PA HS Teacher. I I stumble across anything I too will post it for the record.

 

[PA HS Teacher]

What if you ran the highlight code on some sort of worksheet event?