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

Question

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.

Answer 1

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.

Answer 2

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.

Answer 3

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:

Property	Value
Name	GIF
Picture	(set to be the 1x1 transparent gif picture [link])
Visible	False

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

Property	Value
Tag	"LabelAlignmentTheme"

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.