Determine where text wrap will occur to determine number of lines in cell comment

[Shanthan]

Hi,

I have a userform where the user will be entering a string of text, which will then end up as a cell comment. I am trying to determine how many lines will be in the cell comment when the width of the cell comment box is fixed and Excel automatically wrap the string entered by the user based on Chr(10). Can someone help me with the VBA code?

Thank you!

Shanthan

 

[rjwebgraphix]

Sounds like you would like to specify how wide the text string will be and enter Chr(10) every so many characters to ensure it stays within the fixed width?

If that is the case.... Try this...

Wrap Text On Spaces Up To A Maximum Number Of Characters Per Line

It was written by MrExcel MVP Rick Rothstein.

 

[Shanthan]

This is awesome. This is exactly what I wanted. Thanks so much!

I actually wanted to determine the number of lines after word wrap, so that I can automatically re-size the cell comment box height, while keeping the width at a certain size. I used a counter to determine how the number of loops, which translated into the number of lines. I used an approximate height of each line and then multiplied it by the number of lines to determine the approximate height.