Change Font Bug in UserForm?

Question

It appears an issue occurs when changing the font in a UserForm Label through VBA while it is active or through the UserForm_Initialize() Event.

When using the below code:

Private Sub CommandButton1_Click()

    With Me

        .Label1.BackColor = RGB(0, 204, 0) 

        .Label1.Caption = "û"

        .Label1.Font.Size = 16

        .Label1.ForeColor = RGB(255, 255, 255) 'white

        '.Label1.Font.Name = "Arial"

        .Label1.Font.Name = "WingDings"

    End With

End Sub

Private Sub UserForm_Initialize()

   With Me

        .Label1.BackColor = RGB(0, 204, 0) 

        .Label1.Caption = "û"

        .Label1.Font.Size = 16

        .Label1.ForeColor = RGB(255, 255, 255) 'white

        '.Label1.Font.Name = "Arial"

        .Label1.Font.Name = "WingDings"

    End With

End Sub

If UserForm Label1 is set originally to Tahoma the VBA code does not perform as expected.

Here is a discussion on the Mr. Excel Forum that further documents the UserForm behavior:

Mr Excel Discussion

While I found a workaround that will coerce the result (Uncomment this line of code:   '.Label1.Font.Name = "Arial"). I would think it should work as intended with the one line of code...  Unless, I'm missing something.

Can someone please provide feedback on this issue?

Thank you.

This issue occurred in Excel 2010 (Version: 14.0.7165.5000) 32- Bit, Windows 7 Enterprise 64-Bit Service Pack 1

Answer 1

        .Label1.Caption = "û"

        .Label1.Font.Size = 16

        .Label1.Font.Name = "WingDings"

Why not use an icon?

Private Sub UserForm_Initialize()

  Const Size = 16 'Can be 8, 16, 32, 64, 128

  With Me

    ' .Label1.Picture = Application.CommandBars.GetImageMso("AcceptTask", Size, Size)

    .Label1.Picture = Application.CommandBars.GetImageMso("DeclineTask", Size, Size)

  End With

End Sub

Andreas.

Answer 2

I've used this method before in order to create nice looking export icons on my forms:

.imgExportToExcel.Picture = Application.CommandBars.GetImageMso("FileSaveAsExcelXlsx", 32, 32)

While, I agree this is a good method to accomplish the task at hand, it's still just a workaround that avoids the root cause of the problem.

Answer 3

While, I agree this is a good method to accomplish the task at hand, it's still just a workaround that avoids the root cause of the problem.

Yes, you are right.

It's obvious that the behavior of the Label is not as expected, you can call it a bug.

But you already found a simple and easy workaround to fix it, so what is your real question?

I can reproduce the issue and your solution works, in all versions of Office up to 2016.

Andreas.

Answer 4

It makes very little difference to me.  As we were able to find a work around for a solution that I don't really require, however if it can easily be fixed then it should be fixed at the source....

I posted the question for the benefit of the community, not for me.

Answer 5

For some reason that doesn't work.

I just used 2 differently formated labels on top of each other and used .visible property