In Excel 2007, I inserted an ActiveX label onto my worksheet. I right-clicked on it and viewed properties and managed to change 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 1×1 pixel (here) and set the PictureAlignment property to PicturePositionLeftCenter.

5

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.

1

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.

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 controls properties to the following
Property Value
Name GIF
Picture (set to be the 1×1 transparent gif picture )
Visible False
  1. Now for each of the Label controls which you want to receive the special alignment change the tag property:
Property Value
Tag "LabelAlignmentTheme"
  1. 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. By storing the image in a shared hidden picture somewhere in the form, it is embedded in the file.

1

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