如何使 Excel 标签标题中的文本垂直居中？

Question

在 Excel 2007 中，我将 ActiveX 标签插入到工作表中。我右键单击它并查看“属性”，并设法将 TextAlign 属性更改为 2 (frmTextAlignCenter)。

这会将标签标题的文本与标签的中心（水平）对齐，但文本仍保留在标签的顶部。如何将标题文本垂直居中，使其位于标签的正中间？

我在 SO 中搜索了“垂直对齐”，但没有找到如何对 Excel 标签的标题执行此操作的信息。

Answer 1

有一个技巧可以用单个标签来做到这一点。
添加 1x1 像素的透明 gif 图像（此处）并将 PictureAlignment 属性设置为 PicturePositionLeftCenter。

Answer 2

没有办法直接做到这一点。 这篇文章有一个聪明的方法来完成它， 尽管。制作 2 个框，其中内部框围绕文本自动调整大小，并将内部框放置在外部框的中点。

Answer 3

我刚刚尝试了投票最高的答案中概述的方法，效果非常好。不过，要在方法中添加一些内容 - 例如，如果您有很多标签，我会执行以下操作：

1. 在用户窗体上的某处添加图片控件（任何位置都没关系）。将控件的属性更改为以下内容：

属性	值
名称	GIF
图片	（设置为 1x1 透明 gif 图片 [link])
可见	False

2. 现在，对于要接收特殊对齐方式的每个 Label 控件，更改标签属性：

属性	值
标签	"LabelAlignmentTheme"

3. 最后在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

---

我很喜欢Tag的这种用法，感觉就像是css样式。显然，您可以跳过对标签的检查（删除 And 语句的后半部分）并对齐所有内容，但我认为这是一个更现实的场景，您只需要一些对齐。

通过将图像存储在表单中某处的共享隐藏图片中，它将嵌入到文件中。

Answer 4

您必须使用 2 个标签。

例如，将它们称为 LabelBack、LabelFront。 LabelFront应设置为不透明和无边框，使LabelFront的高度小于LabelBack的高度，并将其或多或少地放在它上面。

然后添加以下代码：

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

注意，我减去了 1 以补偿 LabelFront 内的 1 额外像素。

Answer 5

这看起来像（在课堂上）：作者 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
'--------------------------------------------------

代码在这里

Answer 6

这看起来像（在模块中）：作者 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