How do I vertically center the text in an Excel label's caption?

In Excel 2007, I inserted an ActiveX label onto my worksheet. I right-clicked on it and viewed Properties and managed to chan开发者_如何学编程ge the TextAlign property to 2 (frmTextAlignCenter).

This aligns the label caption's text to the center of the label (horizontally), but the text remains at the TOP of the label. How do I center the caption's text VERTICALLY so that it is in the smack middle of the label?

I've searched "vertical alignment" in SO but nothing comes up for how to do this for an Excel label's caption.

There is a trick to do it with a single label. Add an transparent gif image of 1x1 pixel (here) and set the PictureAlignment property to PicturePositionLeftCenter.

There's no way to do it directly. This post has a clever way to accomplish it, though. Make 2 boxes, with the inner one autosized around the text, and position that inner box at the midpoint of the outer box.

I just tried the approach outlined in the top voted answer and it worked perfectly. To add a little to the approach though - if you have many labels for example, I did the following:

1. Add a picture control somewhere on the userform (anywhere doesn't matter). Change the control's properties to the following:

2. Now for each of the Label controls which you want to receive the special alignment change the tag property:

3. Finally add the following code to UserForm_Initialize

Private Sub UserForm_Initialize()
    'Apply the fix in https://stackoverflow.com/questions/6859127/how-do-i-vertically-center-the-text-in-an-excel-labels-caption
    'To all labels with the matching Tag
    Dim ctrl As MSForms.control
    For Each ctrl In Me.controls
        If TypeOf ctrl Is MSForms.label And ctrl.Tag = "LabelAlignmentTheme" Then
            Dim label As MSForms.label
            Set label = ctrl
            Set label.Picture = Me.GIF.Picture 'read the picture from the picture control
            label.PicturePosition = fmPicturePositionLeftCenter
        End If
    Next
End Sub

I like this use of Tag, it feels like a css style. Obviously you can skip the check for the tag (remove the second half of the And statement) and align absolutely everything but I think this is a more realistic scenario where you only want some aligned.

By storing the image in a shared hidden picture somewhere in the form, it is embedded in the file.

You will have to use 2 Labels.

For Example, Call them LabelBack, LabelFront. The LabelFront should be set to Opaque and No-Border Make the height of LabelFront smaller than that of LabelBack and put it over it more or less.

Then add the following code:

LabelFront.Top = (LabelBack.Top + (LabelBack.Height - LabelFront.Height) / 2) - 1

Notice, I subtracted 1 to compensate the 1 extra pixel within the LabelFront.

This look like (in class): author TRUNG SON

Public Enum VERTYCIAL_ALIGNMENTS
    ALIGN_TOP = 0
    ALIGN_MIDDLE = 1
    ALIGN_BOTTOM = 2
End Enum

Public Enum HORIZONTAL_ALIGNMENTS
    ALIGN_LEFT = 0
    ALIGN_CENTER = 1
    ALIGN_RIGHT = 2
End Enum

Public Enum BACK_STYLES
    TRANSPARENT = 0
    OPAQUE = 1
End Enum

'khai bao cac thuoc tinh can thay doi
Private text_ As String
Private top_ As Double
Private left_ As Double
Private width_ As Double
Private height_ As Double
Private font_name As String
Private font_size As Double
Private horizontal_align As Double
Private vertical_align As Double
Private font_bold As Boolean
Private font_italic As Boolean
Private back_style As Byte
Private back_color As Long
Private fore_color As Long
Private border_color As Long
Private align_hor_type As Double
Private align_ver_type As Double
'------------------------------------

'khai bao cac controls
Private labelText As MSForms.label
Private labelBackground As MSForms.label
'---------------------

'ham khoi tao cua class
Private Sub Class_Initialize()
End Sub

Public Sub Add(Parent As Object) 'them control vao control cha, frame hoac userform (ve len mac dinh)
    Set labelBackground = Parent.Controls.Add("Forms.Label.1")
    Set labelText = Parent.Controls.Add("Forms.Label.1")
    
    'khoi tao gia tri cho bien
    text_ = ""
    top_ = 0
    left_ = 0
    width_ = 50
    height_ = 20
    font_name = "Times New Roman"
    font_size = 12
    horizontal_align = SetTextHorizaontal(HORIZONTAL_ALIGNMENTS.ALIGN_CENTER)
    vertical_align = SetTextVertical(VERTYCIAL_ALIGNMENTS.ALIGN_MIDDLE)
    font_bold = False
    font_italic = False
    back_style = fmBackStyleTransparent
    back_color = vbWhite
    fore_color = vbBlack
    border_color = vbBlack
    '-------------------------
    
    'khoi tao gia tri cho label background
    labelBackground.Top = top_
    labelBackground.Left = left_
    labelBackground.Width = width_
    labelBackground.Height = height_
    labelBackground.BorderStyle = fmBorderStyleSingle
    labelBackground.BorderColor = border_color
    labelBackground.BackStyle = back_style
    labelBackground.BackColor = back_color
    '--------------------------------------
    
    'khoi tao gia tri cho label text
    labelText.Caption = text_
    labelText.font.Name = font_name
    labelText.font.Size = font_size
    labelText.font.Bold = font_bold
    labelText.font.Italic = font_italic
    labelText.WordWrap = False
    labelText.AutoSize = True
    labelText.Top = vertical_align
    labelText.Left = horizontal_align
    labelText.ForeColor = fore_color
    labelText.BackStyle = 0
End Sub

Sub Draw() 'Customize label, ve len frame hoac userform sau khi co thay doi cac thuoc tinh
    'gan gia tri cho label background
    labelBackground.Top = top_
    labelBackground.Left = left_
    labelBackground.Width = width_
    labelBackground.Height = height_
    labelBackground.BorderStyle = fmBorderStyleSingle
    labelBackground.BorderColor = border_color
    labelBackground.BackStyle = back_style
    labelBackground.BackColor = back_color
    '--------------------------------------
    
    'gan gia tri cho label text
    labelText.Caption = text_
    labelText.font.Name = font_name
    labelText.font.Size = font_size
    labelText.font.Bold = font_bold
    labelText.font.Italic = font_italic
    If align_ver_type = VERTYCIAL_ALIGNMENTS.ALIGN_TOP Then
        vertical_align = SetTextVertical(VERTYCIAL_ALIGNMENTS.ALIGN_TOP)
    ElseIf align_ver_type = VERTYCIAL_ALIGNMENTS.ALIGN_MIDDLE Then
        vertical_align = SetTextVertical(VERTYCIAL_ALIGNMENTS.ALIGN_MIDDLE)
    Else
        vertical_align = SetTextVertical(VERTYCIAL_ALIGNMENTS.ALIGN_BOTTOM)
    End If
    labelText.Top = vertical_align
    If align_hor_type = HORIZONTAL_ALIGNMENTS.ALIGN_LEFT Then
        horizontal_align = SetTextHorizaontal(HORIZONTAL_ALIGNMENTS.ALIGN_LEFT)
    ElseIf align_hor_type = HORIZONTAL_ALIGNMENTS.ALIGN_CENTER Then
        horizontal_align = SetTextHorizaontal(HORIZONTAL_ALIGNMENTS.ALIGN_CENTER)
    Else
        horizontal_align = SetTextHorizaontal(HORIZONTAL_ALIGNMENTS.ALIGN_RIGHT)
    End If
    labelText.Left = horizontal_align
    labelText.ForeColor = fore_color
    labelText.BackStyle = 0
End Sub

'ham huy cua class
Private Sub Class_Terminate()
    Clear
End Sub

'cai dat cho cac thuoctinh cua class (begin)
Public Property Get Text() As String
    Text = text_
End Property

Public Property Let Text(ByVal Caption As String)
    text_ = Caption
End Property

Public Property Get Top() As Double
    Top = top_
End Property

Public Property Let Top(ByVal Position As Double)
    top_ = Position
End Property

Public Property Get Left() As Double
    Left = left_
End Property

Public Property Let Left(ByVal Position As Double)
    left_ = Position
End Property

Public Property Get Width() As Double
    Width = width_
End Property

Public Property Let Width(ByVal Dimension As Double)
    width_ = Dimension
End Property

Public Property Get Height() As Double
    Height = height_
End Property

Public Property Let Height(ByVal Dimension As Double)
    If Dimension <= labelText.Height + 6 Then
        height_ = labelText.Height + 6
        labelBackground.Height = height_
    Else
        height_ = Dimension
    End If
End Property

Public Property Let FontName(ByVal Style As String)
    font_name = Style
End Property

Public Property Let FontSize(ByVal Size As Double)
    font_size = Size
End Property

Public Property Let Horizontal_Alignment(ByVal Align As Double)
    horizontal_align = SetTextHorizaontal(Align)
End Property

Public Property Let Vertical_Alignment(ByVal Align As Double)
    vertical_align = SetTextVertical(Align)
End Property

Public Property Let FontBold(ByVal Bold As Boolean)
    font_bold = Bold
End Property

Public Property Let FontItalic(ByVal Italic As Boolean)
    font_italic = Italic
End Property

Public Property Let BackStyle(ByVal Style As Byte)
    If Style = BACK_STYLES.OPAQUE Then
        back_style = fmBackStyleOpaque
    Else
        back_style = fmBackStyleTransparent
    End If
End Property

Public Property Let BackColor(ByVal Color As Long)
    back_color = Color
End Property

Public Property Let ForeColor(ByVal Color As Long)
    fore_color = Color
End Property

Public Property Let BorderColor(ByVal Color As Long)
    border_color = Color
End Property
'-----------------------------------------------(end)

'cac ham xu ly khac
Private Function SetTextHorizaontal(Align As Double) As Double
    On Error Resume Next
    align_hor_type = Align
    
    If Align = HORIZONTAL_ALIGNMENTS.ALIGN_LEFT Then
        labelText.TextAlign = fmTextAlignLeft
        SetTextHorizaontal = left_ + 3
    ElseIf Align = HORIZONTAL_ALIGNMENTS.ALIGN_CENTER Then
        labelText.TextAlign = fmTextAlignCenter
        SetTextHorizaontal = left_ + (width_ - labelText.Width) / 2
    Else
        labelText.TextAlign = fmTextAlignRight
        SetTextHorizaontal = left_ + labelBackground.Width - labelText.Width - 3
    End If
End Function

Private Function SetTextVertical(Align As Double) As Double
    On Error Resume Next
    align_ver_type = Align
    
    If Align = VERTYCIAL_ALIGNMENTS.ALIGN_TOP Then
        SetTextVertical = top_ + 3 'cn top, cach top 3 don vi
    ElseIf Align = VERTYCIAL_ALIGNMENTS.ALIGN_MIDDLE Then
        SetTextVertical = top_ + (height_ - labelText.Height) / 2
    Else
        SetTextVertical = top_ + (height_ - labelText.Height) - 3
    End If
End Function

Public Sub Clear()
    Set labelBackground = Nothing
    Set labelText = Nothing
End Sub
'--------------------------------------------------

ter code here

This look like (in module): author TRUNG SON

Public Enum VERTYCIAL_ALIGNMENT
    TOP = -1
    MIDDLE = 0
    BOTTOM = 1
End Enum

Public Enum BACK_STYLE
    OPAQUE = 1
    TRANSPARENT = 0
End Enum

Function CreateCenterText(CtlParent As Object, _
                        text As String, _
                        TOP As Double, _
                        Left As Double, _
                        Width As Double, _
                        Height As Double, _
                        Optional text_Align_Type As Integer = VERTYCIAL_ALIGNMENT.MIDDLE, _
                        Optional fontName As String = "Times New Roman", _
                        Optional fontSize As Double = 12, _
                        Optional fontBold As Boolean = False, _
                        Optional fontItalic As Boolean = False, _
                        Optional foreColor As Long = vbBlack, _
                        Optional backColor As Long = vbWhite, _
                        Optional backStyle As Long = BACK_STYLE.TRANSPARENT, _
                        Optional BorderColor As Long = vbBlack) As MSForms.label 'Customize label
    Dim lblBG As MSForms.label
    Dim lblText As MSForms.label
    
    Set lblBG = CtlParent.controls.Add("Forms.Label.1")
    Set lblText = CtlParent.controls.Add("Forms.Label.1")
    
    lblBG.TOP = TOP
    lblBG.Left = Left
    lblBG.Width = Width
    lblBG.Height = Height
    lblBG.TextAlign = 2
    lblBG.BorderStyle = fmBorderStyleSingle
    lblBG.BorderColor = BorderColor
    If backStyle = BACK_STYLE.OPAQUE Then
        lblBG.backStyle = fmBackStyleOpaque
    Else
        lblBG.backStyle = fmBackStyleTransparent
    End If
    lblBG.backColor = backColor
    
    lblText.Width = 500
    lblText.Height = 50
    lblText.caption = text
    lblText.font.Name = fontName
    lblText.font.SIZE = fontSize
    lblText.font.Bold = fontBold
    lblText.font.Italic = fontItalic
    lblText.foreColor = foreColor
    lblText.AutoSize = True
    Dim align As Double
    If text_Align_Type = VERTYCIAL_ALIGNMENT.TOP Then
        align = -((Height - lblText.Height) / 2) + 3 ''=TOP + 3
    ElseIf text_Align_Type = VERTYCIAL_ALIGNMENT.MIDDLE Then
        align = 0 ''=TOP + ((Height - lblText.Height) / 2)
    Else
        align = (Height - lblText.Height) / 2 - 3 ''=TOP + HEIGHT - lblText.Height
    End If
    lblText.TOP = TOP + ((Height - lblText.Height) / 2) + align
    lblText.Left = Left + (Width - lblText.Width) / 2
    lblText.TextAlign = 2
    lblText.WordWrap = False
    lblText.backStyle = 0
    
    Set CreateCenterText = lblBG
    
    Set lblBG = Nothing
    Set lblText = Nothing
End Function