Measure height of textbox

[Nils001]

Hello,

I need to measure the height of a textbox in order to place other objects. The height of the textbox depends on the length of the text which is dynamically updated, and i have sat autosize=true.
It is placed on a worksheet. This is the code, which i have used to generate it:


ActiveSheet.Shapes.AddTextbox(msoTextOrientationHorizontal, chartAreaLeft, plotAreaTop + chartAreaHeight + AfstandChart + 2 * AfstandKategorier, ForklaringBredde, 1.35 * ForklaringHøjde).Name = "spgOverskriftT" & "," & p
ActiveSheet.Shapes.Range(Array("spgOverskriftT" & "," & p)).Select
Selection.ShapeRange(1).TextFrame2.TextRange.Characters.Text = "Tilpas"
Selection.ShapeRange.TextFrame2.TextRange.Font.Size = 11
Selection.ShapeRange.TextFrame2.TextRange.Font.Name = "Lucida Bright"
Selection.ShapeRange.Line.Visible = msoFalse
Selection.ShapeRange.TextFrame2.VerticalAnchor = msoAnchorMiddle
Selection.ShapeRange.Fill.Visible = msoFalse
Selection.ShapeRange.ZOrder msoBringToFront

I really hope that someone can help me

[SamT]

This is your code, I just edited it to make it faster and easier to understand. I am not saying that it even works.

With ActiveSheet.Shapes
.AddTextbox(msoTextOrientationHorizontal, _
chartAreaLeft, _
plotAreaTop + chartAreaHeight + AfstandChart + 2 * AfstandKategorier, _
ForklaringBredde, 1.35 * ForklaringHøjde).Name = "spgOverskriftT" & "," & p

With .Range(Array("spgOverskriftT" & "," & p))
With .ShapeRange(1).TextFrame2.TextRange
.Characters.Text = "Tilpas"
.Font.Size = 11
.Font.Name = "Lucida Bright"
End With 'ShapeRange(1).TextFrame2.TextRange

With .ShapeRange
.Line.Visible = msoFalse
.TextFrame2.VerticalAnchor = msoAnchorMiddle
.Fill.Visible = msoFalse
.ZOrder msoBringToFront
End With 'ShapeRange

End With 'Range(Array("spgOverskriftT" & "," & p))
End With 'ActiveSheet.Shapes

If it works, this is how I would change it to get the values you're looking for. And I am not saying this will work, either


Dim Lft As Double
Dim Wdth As Double
Dim Tp As Double
Dim Hght As Double

With ActiveSheet.Shapes
.AddTextbox(msoTextOrientationHorizontal, _
chartAreaLeft, _
plotAreaTop + chartAreaHeight + AfstandChart + 2 * AfstandKategorier, _
ForklaringBredde, 1.35 * ForklaringHøjde).Name = "spgOverskriftT" & "," & p

With .Range(Array("spgOverskriftT" & "," & p))
With .ShapeRange(1)
With .TextFrame2
Lft = .Left
Wdth = .Width
Tp = .Top
Hgth = .Height

With .TextRange
.Characters.Text = "Tilpas"
.Font.Size = 11
.Font.Name = "Lucida Bright"
End With 'TextRange
End With 'TextFrame2.

'The rest is with ShapeRange
.Line.Visible = msoFalse
.TextFrame2.VerticalAnchor = msoAnchorMiddle
.Fill.Visible = msoFalse
.ZOrder msoBringToFront

End With 'ShapeRange
End With 'Range(Array("spgOverskriftT" & "," & p))
End With 'ActiveSheet.Shapes

Now you have the TextFrame's Lft, Wdth, Tp, and Hght to use as you want.

[Nils001]

This is your code, I just edited it to make it faster and easier to understand. I am not saying that it even works.

With ActiveSheet.Shapes
.AddTextbox(msoTextOrientationHorizontal, _
chartAreaLeft, _
plotAreaTop + chartAreaHeight + AfstandChart + 2 * AfstandKategorier, _
ForklaringBredde, 1.35 * ForklaringHøjde).Name = "spgOverskriftT" & "," & p

With .Range(Array("spgOverskriftT" & "," & p))
With .ShapeRange(1).TextFrame2.TextRange
.Characters.Text = "Tilpas"
.Font.Size = 11
.Font.Name = "Lucida Bright"
End With 'ShapeRange(1).TextFrame2.TextRange

With .ShapeRange
.Line.Visible = msoFalse
.TextFrame2.VerticalAnchor = msoAnchorMiddle
.Fill.Visible = msoFalse
.ZOrder msoBringToFront
End With 'ShapeRange

End With 'Range(Array("spgOverskriftT" & "," & p))
End With 'ActiveSheet.Shapes

If it works, this is how I would change it to get the values you're looking for. And I am not saying this will work, either


Dim Lft As Double
Dim Wdth As Double
Dim Tp As Double
Dim Hght As Double

With ActiveSheet.Shapes
.AddTextbox(msoTextOrientationHorizontal, _
chartAreaLeft, _
plotAreaTop + chartAreaHeight + AfstandChart + 2 * AfstandKategorier, _
ForklaringBredde, 1.35 * ForklaringHøjde).Name = "spgOverskriftT" & "," & p

With .Range(Array("spgOverskriftT" & "," & p))
With .ShapeRange(1)
With .TextFrame2
Lft = .Left
Wdth = .Width
Tp = .Top
Hgth = .Height

With .TextRange
.Characters.Text = "Tilpas"
.Font.Size = 11
.Font.Name = "Lucida Bright"
End With 'TextRange
End With 'TextFrame2.

'The rest is with ShapeRange
.Line.Visible = msoFalse
.TextFrame2.VerticalAnchor = msoAnchorMiddle
.Fill.Visible = msoFalse
.ZOrder msoBringToFront

End With 'ShapeRange
End With 'Range(Array("spgOverskriftT" & "," & p))
End With 'ActiveSheet.Shapes

Now you have the TextFrame's Lft, Wdth, Tp, and Hght to use as you want.

Thank you very much! That worked!

[Aussiebear]

[Quote} And I'm not saying this will work, either[/Quote]

:thinking:

Not bad Sam.