[RESOLVED] Dynamic Height of Cell using Excel Automation

Hello,

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

Code:

---

        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?

I think you need to use the AutoFit functionality:

Code:

---

ExlApp.Selection.Columns.AutoFit

---

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

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).

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.

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.

Code:

---

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

---

Code:

---

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

---

Quote:

---

Originally Posted by Hassan Basri

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

---

Why 13? Just curious.

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.

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:

Code:

---

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

---

Nice one, RhinoBull.

I finally did it like this:

Code:

---

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

---

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

Quote:

---

Originally Posted by Hassan Basri

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

---

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