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.
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.
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:
- Add a picture control somewhere on the userform (anywhere doesn’t matter). Change the controls properties to the following
|Picture||(set to be the 1×1 transparent gif picture )|
- Now for each of the Label controls which you want to receive the special alignment change the tag property:
- Finally add the following code to
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.
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