[RESOLVED] Dynamic Height of Cell using Excel Automation

[Hassan Basri]

Hello,

I have data from a database and inserting it into Excel. Then I adjust the properties like this:

        With xlsApp.Selection
            'Position and size of the textbox
            .Left = xlsSheet.Cells(cPVCRRowFirstPts + lngNbPts + 4, 3).Left + 3
            .Top = xlsSheet.Cells(cPVCRRowFirstPts + lngNbPts + 4, 1).Top
            .Width = 420#
            .Height = 114.75
            .Font.Size = 11
        End With

I would like the height of the cell to be dynamic depending on the contents of the cell. Anybody know how to do this?

[RhinoBull]

I think you need to use the AutoFit functionality:

ExlApp.Selection.Columns.AutoFit

[Hassan Basri]

Thanks RhinoBull, this Property works to modify the whole column, is there a way of modifying just the selected cell?

[RhinoBull]

I'm afraid not - you cannot make "A1" and "A2" cells to have different width or "A1" and "B1" to have different height.
Unless I'm missing something...

It's like in the any other grid: when you resize one cell you're actually resizing entire column (width) or row (height).

[Hassan Basri]

Thanks RhinoBull again, I see what you mean.

I just noticed that it is not a cell, it is a textbox object that is over the grid, and that is why I can change the height or width in my code above.

I was thinking that if I count the number of lines that is in the textbox object, I could then multiply that by a factor (I will have to mesure the height of one line) and then adjust my height accordingly.

So my second question would be, how would I calculate the number of lines in my textbox?

Thanks for your assistance it is appreciated.

[Hassan Basri]

In the end I counted the number of lines and multiplyed that by a factor of 13. Here is the code if anybody else every needs this in the future:

I used the following API call. However this does not work with a TextBox you must use a Rich Edit Control for this to return the correct results.

Private Declare Function SendMessageAsLong Lib "user32" Alias "SendMessageA" (ByVal hWnd As Long, ByVal wMsg As Long, ByVal wParam As Long, ByVal lParam As Long) As Long

Private Const EM_GETLINECOUNT = 186

MsgBox "Height Of Excel TextBox = " & 13 * SendMessageAsLong(MyRichEditBox.hWnd, EM_GETLINECOUNT, 0, 0)

[RhinoBull]

In the end I counted the number of lines and multiplyed that by a factor of 13...

Why 13? Just curious.

[Hassan Basri]

I found that number by trial and error, 10 was too short, 15 too big, 13 was just right. I know it sounds like the modern IT version of the Golldilocks story.

Thanks for your help RhinoBull.

[RhinoBull]

I figured that much ... ... so you confirmed.

Anyway, you can set your Form's Font to whatever font you have in the yuor textbox so you can use TextHeight:

Dim iHeight As Integer

Set Me.Font = Text1.Font 'or you can go with Font.Name/Size/etc...

iHeight = TextHeight("A") '<<<--- here is your precise multiplyier - no guessing ;)
'if value of iHeight is not enough for some reason then increase it:
iHeight = iHeight * 1.1 'or whatever but do not use fixed (guessed) numbers like 13

[Hassan Basri]

Nice one, RhinoBull.

I finally did it like this:

Debug.Print MyForm.TextHeight("A") / Screen.TwipsPerPixelY

and guess what number it returned on my machine? The famous 13.

[RhinoBull]

...and guess what number it returned on my machine? The famous 13.

Hah... Coincident thou ... But seriously, it depends on the font Name/Size.