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]



Not bad Sam.