Results 1 to 6 of 6

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

  1. #1

    Thread Starter
    New Member
    Join Date
    Jan 2008
    Posts
    7

    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

    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?
    Last edited by Glendale; Feb 5th, 2008 at 04:31 PM.

  2. #2
    Ex-Super Mod RobDog888's Avatar
    Join Date
    Apr 2001
    Location
    LA, Calif. Raiders #1 AKA:Gangsta Yoda™
    Posts
    60,710

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

    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.
    VB/Office Guru™ (AKA: Gangsta Yoda®)
    I dont answer coding questions via PM. Please post a thread in the appropriate forum.

    Microsoft MVP 2006-2011
    Office Development FAQ (C#, VB.NET, VB 6, VBA)
    Senior Jedi Software Engineer MCP (VB 6 & .NET), BSEE, CET
    If a post has helped you then Please Rate it!
    Reps & Rating PostsVS.NET on Vista Multiple .NET Framework Versions Office Primary Interop AssembliesVB/Office Guru™ Word SpellChecker™.NETVB/Office Guru™ Word SpellChecker™ VB6VB.NET Attributes Ex.Outlook Global Address ListAPI Viewer utility.NET API Viewer Utility
    System: Intel i7 6850K, Geforce GTX1060, Samsung M.2 1 TB & SATA 500 GB, 32 GBs DDR4 3300 Quad Channel RAM, 2 Viewsonic 24" LCDs, Windows 10, Office 2016, VS 2019, VB6 SP6

  3. #3
    New Member
    Join Date
    Dec 2009
    Posts
    1

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

    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:
    1. Public Sub VerticalAlignCenter(ByRef ctl As Control)
    2. On Error GoTo ErrorCode
    3.     Dim MinimumMargin As Integer
    4.     Dim BorderWidth As Integer
    5.     Dim TwipsPerPoint
    6.     TwipsPerPoint = 20
    7.     If Not ((TypeOf ctl Is TextBox) Or (TypeOf ctl Is Label)) Then Exit Sub
    8.    'Figure out how many lines it is
    9.    Dim LenOfText, WidOfBox, NumberOfLines, HtOfText
    10.    If TypeOf ctl Is TextBox Then
    11.     LenOfText = ctl.Text
    12.     Else:
    13.     LenOfText = ctl.Caption
    14.     End If
    15.     'how wide is this puppy?
    16.     WidOfBox = ctl.Width
    17.     LenOfText = (Len(LenOfText) * TwipsPerPoint * ctl.FontSize) / 2
    18.     NumberOfLines = Int(LenOfText / WidOfBox) + 1
    19.     HtOfText = NumberOfLines * TwipsPerPoint * ctl.FontSize
    20.    
    21.    
    22.     MinimumMargin = 1 * TwipsPerPoint
    23.     BorderWidth = (ctl.BorderWidth * TwipsPerPoint) / 2
    24.    
    25.     ctl.TopMargin = ((ctl.Height - HtOfText) / 2) - MinimumMargin - BorderWidth    
    26.    
    27. ErrorCode:
    28.     Exit Sub
    29. End Sub
    Last edited by Hack; Sep 2nd, 2011 at 09:04 AM. Reason: Added Highlight Tags

  4. #4
    New Member
    Join Date
    Aug 2011
    Posts
    13

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

    Thanks for the fine-working code, Glendale. It works for me perfect. Not on the first try however..
    Last edited by Tobiasgar; Jun 22nd, 2012 at 02:24 AM.

  5. #5
    New Member
    Join Date
    Oct 2011
    Posts
    2

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

    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

  6. #6
    New Member
    Join Date
    Oct 2011
    Posts
    2

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

    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

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  



Click Here to Expand Forum to Full Width