Excel 2011 for Mac - creating userforms

Question

I am using Excel 2011 for Mac as it is the last version of VBA to allow userforms to be created. 

There are one or two issues with inserting controls into the userform which are annoying and may have solutions I am not aware of...

1. If you insert a text (input) box or a label they come with a default text setting which is 8pt Lucider Grande.  Can this be changed by default to a more reasonable point size (12pt ?) and typeface (Helvetica or similar).  Changing each and every control to a new font is non-productive (and boring).
2. Command buttons seem to come only with a background set to near white (&H00AAAAAA&) and do not show a raised border.  See attached screen shots.

I have changed the font on this one to Calibri.

Am I stuck with these settings or is there a simple fix???

Answer 1

This is a very old issue. Back in the days when there was a Macintosh Business Unit (MacBU) they were focused on making Microsoft Office as "Mac-like" as possible, especially when it came to interface design. Someone in MacBU decided that forcing gray button backgrounds is more "Mac-Like" than allowing developers to choose the background color. You can set the color in the VB Editor, and it will display using your setting in Windows, but not on the Mac. I don't like it like this either.

You can post a feature suggestion to have this odd behavior changed back to the original when it used to work. I don't see an existing suggestion about this. If you post a suggestion, please reply here with the link so I can vote for it. Thanks.

Excel for Mac

Answer 2

It can't be changed by default, but it can be changed programmatically. The VBE itself is programmable. You could make a macro that sets the font and other parameters to your liking.

Answer 3

Thanks for making the suggestion. I voted for it!

Actually, there's a small chance that they will eventually make this change because a major goal is to reduce inconsistency between Mac and Windows Office.

However, there are some very big projects currently underway that involve VBA, and I would expect Microsoft will spend their resources on them. First, they have to build the UI for userforms in current versions of Office for Mac (2019 and 365). This entire piece is completely missing from the VB Editor.

Enable VBA Support for creating and editing UserForms (not the Data Form)

Answer 4

If only they done it properly!  The default is not even “mac compatible” - the background for command buttons is almost, but not quite, white!

i shall suggest a change.

thanks

.... just remembered this is the 2011 version i’m using (so that i can design userforms - later versions are not able to do this) so its VERY unlikely they will ever issue a revision to it!!!

..... just issued a suggestion here’s the link....

https://excel.uservoice.com/forums/304933-excel-for-mac/suggestions/38686102-control-of-command-button-colour-in-excel-for-mac

Answer 5

What I didn't mention is that trying to change the background colour for command buttons doesn't work.  Is this a residual bug not sorted when the software was abandonned by Microsoft?

How do do you fix these issues programmatically?  Presumably with a statement in the activate userform subroutine - but when - before or after issuing the "userform show"?  And what statement(s) does this - something like "CommandButton1.color = H00AAAAAA" (for mid grey)?