AutoSize Comments?

Hi Adrian,

I'd like to add a comment to a cell and have it auto sized so all text is visible when the  mouse hovers over it.  Adding the comment seem easy but I cannot find a way to auto size the text.  Is it possible?




If you want to have Excel resize the comment box automatically, you could use:

  CommentProps: ICommentProperties;
  xls.NewFile(3, TExcelFileFormat.v2010);  //Create a new Excel file with 3 sheets.

  CommentProps := TCommentProperties_CreateStandard(1, 1, xls);
  CommentProps.AutoSize := true;
  xls.SetComment(1,1,'hello, this is a long comment and it will ' + #10 + ' overflow a normal comment box', 'adrian',

But note that Excel's autofit for comments isn't great. It will just put the full text into a single line (resizing the box only on the x direction), making it look weird. This is why I added a #10 in the code above, so the code is split into 2 lines, instead of a long silly line.

Excel also doesn't support setting automatic comment resizing through its interface, so while this works, it is kind of not supported. In excel itself, you can only create comments of fixed size.

The other option then, is to create a comment of a fixed size, big enough to contain the text you need. This could be a fixed size that you know should work for most comments, or you could use FlexCel to calculate the text size of the comment, and then use that value.

Now, I've just looked and sadly the methods you would need to have Excel calculate the width and height of a combobox aren't public. This is mostly because like Autofitting, resizing a combobox needs the rendering engine to calculate the dimensions (you might have many fonts inside a comment), so it "mixes" normal FlexCel code with UI code, and as XlsFile doesn't know about UI code, we need to do some hacks to do it. 

But I think the easiest is that we add a AutoFitCommentBox method, I'll see to add it today.
If you want to use this FlexCel resizing instead of Excel's, send me an email to and I'll send you the updated version.