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

Hi all, working in Access 2003, I was annoyed that I couldn't get my labels to align vertically unless I manually played with the .TopMargin property of each control. I whipped up this function that aligns the text vertically so I wouldn't need to do it manually anymore. Hopefully it can be of use to some of you :cool:

Code:

---

'Sets the vertical alignment of a label or textbox to "Center"
'NOTE:
'MinimumMargin: the value of the smallest margin Access allows in twips.  If you set the .TopMargin
'to "0", Access will actually place a gap equal to 1 point (or 20 twips) rather than actually
'placing the text at the very top border of the control.  FURTHER NOTE: Access sucks.
'
'BorderWidth: half the value of .BorderWidth in twips.  As you increase the border (Hairline, 1pt, 2pt, etc..)
'Access expands the border equally from the center.  We're only concerned with the "inner" section of the border.
'
'TwipsPerPoint: global constant set to 20

Public Sub VerticalAlignCenter(ByRef ctl As Control)
On Error GoTo ErrorCode
    Dim MinimumMargin As Integer
    Dim BorderWidth As Integer
   
    If Not ((TypeOf ctl Is TextBox) Or (TypeOf ctl Is Label)) Then Exit Sub
 
    MinimumMargin = 1 * TwipsPerPoint
    BorderWidth = (ctl.BorderWidth * TwipsPerPoint) / 2
   
    ctl.TopMargin = ((ctl.Height - (ctl.FontSize * TwipsPerPoint)) / 2) - MinimumMargin - BorderWidth
   
ErrorCode:
    Exit Sub
End Sub

---

Chop it up, modify it, use it however you want. If you improve it in any way, please post your changes so that I can learn..



Update: My code doesn't work for controls with multiple lines of text.. It sets the TopMargin as if the top line of text is the only line present in the control. Does anyone have any creative workarounds for this glitch?

Thread Moved

Thanks for the submission :)


You might want to note that it will require a MSForms Textbox or Label control if being used outside of VBA.

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.

vb Code:

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

Thanks for the fine-working code, Glendale. It works for me perfect. Not on the first try however..

I need a little more help (I tried for over two hours to make this work, still new to all this). What I don't know is how / were are you calling the VerticalAlignCenter function?

Kevin

OK I figured it out, sort of.
I'm trying to use it on a report. The labels work perfect. The textbox in the details do not.

Tracing it through it errors at "LenOfText = ctl.Text". Ruturns a length of zero.

How do I make it work with the details section of a report?

Kevin