Vertical Alignment

[bharatvly]

Hello Folks,

I was able to find the below code to align text vertically in Labels and TextBoxes. However; I haven't been able to figure out where do I place this code and how do I use it? Do I have to make any changes to this code?

Please help!

'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

 

[Kevin456789]

I was searching for just such a function for my MS Access report, so I thought I'd reply to your question.

You would call this from within the OnFormat event of the section the text or label is within:

Private Sub PageHeaderSection_Format(Cancel As Integer, FormatCount As Integer)

VerticalAlignCenter Me.Label123​

End Sub



I made a lot of changes to make it more functional and accurate and bulletproof, and this is as good of a place as any to post it:

VerticalAlignText Me.Label123, "B"

Public Sub VerticalAlignText(ByRef oCtrl As Control, sCenter_or_Bottom As String)

On Error GoTo ErrH
Const nTwipsPerPoint As Integer = 20 'an internal constant within Access
Dim nBottomMargin As Integer, nBorderWidth As Integer

If (TypeOf oCtrl Is TextBox) Or (TypeOf oCtrl Is Label) Then
​

Dim nLenOfText As Long, nNumberOfLines As Long, nHeightOfText As Long, nTopMargin As Long
If TypeOf oCtrl Is TextBox Then​

nLenOfText = (Len(oCtrl.Text) * nTwipsPerPoint * oCtrl.FontSize) / 2​

Else:​

nLenOfText = (Len(oCtrl.Caption) * nTwipsPerPoint * oCtrl.FontSize) / 2​

End If

nNumberOfLines = Int(nLenOfText / oCtrl.Width) + 1
nHeightOfText = nNumberOfLines * nTwipsPerPoint * oCtrl.FontSize * 1.2
' * 1.2 to account for space between lines - this may not be the exact number to use, but it is working so far.

nBottomMargin = 3 * nTwipsPerPoint
'if you have a line at the bottom of a textbox, 3 * gives optimal appearance but 1* gets your text closer to the bottom of the textbox

nBorderWidth = (oCtrl.BorderWidth * nTwipsPerPoint) / 2

If Left(sCenter_or_Bottom, 1) = "C" Then​

nTopMargin = ((oCtrl.Height - nHeightOfText) / 2) - nBottomMargin - nBorderWidth​

Else​

nTopMargin = ((oCtrl.Height - nHeightOfText)) - nBottomMargin - nBorderWidth​

End If

If nTopMargin < 0 Then​

oCtrl.TopMargin = 0​

Else​

oCtrl.TopMargin = nTopMargin​

End If​

End If
Exit Sub​

ErrH:

MsgBox Err.Description
Stop
Resume​

End Sub

 

[bharatvly]

Appreciate it Kevin for your kindness! I settled with a less sophisticated code rather an alternative to do my work. I will surely try this in future.

Thanks again!