VB/VBA: Set TextBox and Label text to Vertically Align Center

[gkoliver]

That was such a useful piece of code, the easiest to find on google. I tweaked it to allow multiple lines of code... cheesy, probably, but it worked for me. Thanks for the super code, and I hope someone finds my tweak useful/functional.

Public Sub VerticalAlignCenter(ByRef ctl As Control)
On Error GoTo ErrorCode
    Dim MinimumMargin As Integer
    Dim BorderWidth As Integer
    Dim TwipsPerPoint
    TwipsPerPoint = 20
    If Not ((TypeOf ctl Is TextBox) Or (TypeOf ctl Is Label)) Then Exit Sub
   'Figure out how many lines it is
   Dim LenOfText, WidOfBox, NumberOfLines, HtOfText
   If TypeOf ctl Is TextBox Then
    LenOfText = ctl.Text
    Else:
    LenOfText = ctl.Caption
    End If
    'how wide is this puppy?
    WidOfBox = ctl.Width
    LenOfText = (Len(LenOfText) * TwipsPerPoint * ctl.FontSize) / 2
    NumberOfLines = Int(LenOfText / WidOfBox) + 1
    HtOfText = NumberOfLines * TwipsPerPoint * ctl.FontSize
    
   
    MinimumMargin = 1 * TwipsPerPoint
    BorderWidth = (ctl.BorderWidth * TwipsPerPoint) / 2
    
    ctl.TopMargin = ((ctl.Height - HtOfText) / 2) - MinimumMargin - BorderWidth    
    
ErrorCode:
    Exit Sub
End Sub