Determine Point of Cell Word Wrap of A String (max length of string permitted for a static sized cell)

[Ark68]

I am looking for some help to create a VBA solution to this concept. I hope someone can offer some direction.

I have a string variable, tfd, with a value of "The rich look of hand crafted granite". This string is 37 characters in length.
I need to put this value into a cell of a static height height and width, so autofit with word wrap isn't an option. At 37 characters, this value will not fit into the cell with the loss of many of it's characters.
I have included an input box requesting the user reduce the number of characters so that it will fit into the cell. However, the challenge I have found is I'm not sure what the maximum number of characters would be. I manually entered a series of capital M into the cell and counted 19 as the maximum. But when I adjusted the value of tfd to under 20 characters, there was still lots of room in the cell. So I don't think using the series of Ms provided an accurate account of how many characters could fit into that cell. We know it can only fit 19 Ms if the value of tfd was all Ms.

Is there a means that the point at which word wrap would wrap a string be calculated. One would figure then that the maximum number of characters of a particular string that could fit in that cell would be thoise between the start of the string, and the point at which it would initiate the text wrap. So in this case, if the string would word wrap at the "d" in "crafted" (position 29), then we can conclude the maximum length of that string to fit in that cell would be 29 characters.

 

[Micron]

You can only do this reliably with a fixed width font. Not using one means that WWW takes up far more room than III even though the count is the same. The characters are not 'lost', they are just not visible, yes? An alternative might be a double click on a cell that you can't read all of the text and present a message box or userform showing the complete text?

 

[Ark68]

Thanks Micron. Excel and VBA is so powerful I was optimistic there might be a way to do it. I suppose for now I'll just liove with it lol.

 

[Micron]

My motto is that if you can imagine it, it can be done. However, sometimes the effort far outweighs the reward. If you can't use a fixed width font for at least one column then I'd say you'd need at least a range of 53 characters (a-z and A-Z and space and maybe punctuation) and a point width value for each (fonts are measure in points IIRC). Before assigning the text to the cell you'd have to loop through every character in the string, look up its point value and add it to the running sum. Then don't allow the string to be entered if it exceeds the point value that you've calculated for the column width. As if that is not bad enough, if the user alters the font or font size applied to this column it all goes awry. Then add to that the point width value for numbers, assuming they're allowed. If not allowed, your loop would also have to test if each character is numeric. So doable? Likely. Worth it? Not so much. I take it that the zoom box or message box idea is not attractive either.

 

[Ark68]

Yes, far more effort than the benefit in the long run. I'm not ruling out your options, but in the essence of time, something to try later. I'm happy there are options though, thank you.

 

[Micron]

Ok, if you go with one of those ideas, could be as simple as (using msgbox on column L)

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)

If Target.Column <> 12 Then Exit Sub
MsgBox Target
Cancel = True

End Sub