The Problem.
There are many annoyances when designing a VBA project to run on both Windows and Mac computers. One of the most noticeable is related to the difference in screen resolution between the two platforms. On the Mac, it’s 96 dpi, where each dot represents a pixel. In Windows, screen resolution is 72 dpi (dots per inch) and each dot represents a “point”, while pixels are still 96 per inch. Confusing the issue is that VBA in Windows uses points as the measurement unit for designing UserForms, while VBA on the Mac uses pixels. The result is that without applying any correction, UserForms that come out just right in Windows are only 75% as large on the Mac, making them difficult to read.
For example, here is a UserForm designed in Windows, opened in Windows. The text might seem a bit small to me now that my eyes are getting older, but it’s still perfectly legible.
Here is that same UserForm opened on a Mac. The header text is fine, but the text in the dialog itself is too small to read comfortably.
In the past, I supported separate add-ins, one for Windows and the other for Mac. The dialogs for each were appropriately sized, and I took care of other coding idiosyncrasies in the separate files. But it’s a lot of extra work to lug around two separate versions of every file. Every adjustment you make has to be made twice, and it’s hard to remember what changes you’ve made across large projects.
The Solution: UserForms For Mac And Windows.
I have developed a simple routine that is called from each UserForm’s initialize event, which changes the size, position, and font of each control by a factor of 4/3. The text still may not always be perfect, since you can only specify whole number font sizes, so I suppose I should always round up the font size. I do make sure when I design the form that controls are more than large enough in case the text takes up extra space. Aside from this, it works pretty nicely.
Here is a UserForm designed in Windows, but which uses the resizing routine so it displays legibly on either computer. First, it is shown opened in Windows.
Below it’s shown opened on the Mac. The dialogs are equally legible, and the text came out pretty much the same. Sometimes a large block of text may not wrap the same, so if it really matters, you should hard code your line breaks by typing Ctrl+Return while entering the text.
Note: While Microsoft has substantially improved the VB editor on the Mac, you still can’t work with UserForms on the Mac. You have to build them into your file in Windows and them move the file to the Mac.
The Code.
This calls the routine from the UserForm’s code module:
Private Sub UserForm_Initialize()
#If Mac Then
ResizeUserForm Me
#End If
End SubThe #If Mac Then and #End If structure indicates a section of code which is conditionally compiled. When using a Mac, the constant Mac is True, so the code between #If and #End If is compiled and run, so the UserForm is resized. In Windows, this code is ignored, and the UserForm is displayed without rescaling.
The global resizing factor is placed in an appropriate place in the declarations section of a regular code module:
Public Const gUserFormResizeFactor As Double = 1.333333And this routine in a regular code module does the resizing:
Sub ResizeUserForm(frm As Object, Optional dResizeFactor As Double = 0#)
Dim ctrl As Control
Dim sColWidths As String
Dim vColWidths As Variant
Dim iCol As Long
If dResizeFactor = 0 Then dResizeFactor = gUserFormResizeFactor
With frm
.Height = .Height * dResizeFactor
.Width = .Width * dResizeFactor
For Each ctrl In frm.Controls
With ctrl
.Height = .Height * dResizeFactor
.Width = .Width * dResizeFactor
.Left = .Left * dResizeFactor
.Top = .Top * dResizeFactor
On Error Resume Next
.Font.Size = .Font.Size * dResizeFactor
On Error GoTo 0
' multi column listboxes, comboboxes
Select Case TypeName(ctrl)
Case "ListBox", "ComboBox"
If ctrl.ColumnCount > 1 Then
sColWidths = ctrl.ColumnWidths
vColWidths = Split(sColWidths, ";")
For iCol = LBound(vColWidths) To UBound(vColWidths)
vColWidths(iCol) = Val(vColWidths(iCol)) * dResizeFactor
Next
sColWidths = Join(vColWidths, ";")
ctrl.ColumnWidths = sColWidths
End If
End Select
End With
Next
End With
End SubExample Workbook.
I’ve posted a simple workbook that illustrates this technique. Download it by clicking the icon below.
When you open the workbook, you’ll see a blank worksheet with three buttons.
The first button opens the Windows-only UserForm shown in the first and second figures above, in the description of the problem.
The second button opens the Windows-and-Mac UserForm shown in the third and fourth figures, in the description of the solution.
The third button opens another Windows-and-Mac UserForm which also has a simple mechanism to call the ResizeUserForm procedure, and resize the UserForm on the fly. Here is it opened in Windows.
Enter a number in the New Form Magnification box (for example 2) and click Apply, and the form is resized accordingly.
Enter 1.333333, the ratio between pixels and points, and this is how it will be resized for the Mac, though it’s too large for Windows.
Enter 0.75, the inverse of the above, and you get a sense for how small it would be on the Mac without rescaling. Yeah, way too small.
Here is the same form opened on a Mac. Note that it’s already resized to 1.333333. Resized nicely.
Let’s see how it looks at 200%. Pretty large.
If you enter 1 in the box, you can see how the form would look without resizing.
In all of your real projects, you should apply this technique to all of your UserForms. Before deploying them to your users, you should make sure that the UserForms that look fine in Windows rescale properly on a Mac. Occasionally text wraps differently or certain other controls may need adjustments. But this eliminates the need for separate UserForms for Mac and Windows.
Dory Owen says
Very cool, Jon. Thanks for sharing this.
Now if we could get a Mac equivalent of MSXM DLL so I can query the Global Address Book…
Mark says
Great post.
I had always assumed it was just tough luck that Mac and PC didn’t render the same. It never occurred to me to do anything about it.
I’ll definitely be implementing this.
Jeff Weir says
Thanks Jon. Question: what % of your add-in purchases are on Mac?
Jon Peltier says
Jeff –
Good question. The earlier version of my software had separate files for Mac and Windows, so I could tell exactly how many of each there were. And it was about 11% Mac, 89% Windows.
Now, using techniques like the one in this article, I have only one file that runs in either platform, and I don’t collect user data, so I don’t have a hard percentage of Mac and Windows users, but I suspect it’s at least as many Mac users as before, if not more.
Eric says
This code worked great for me. I appreciate your clear explanation of the matter as well.
Good stuff.
Jeleel says
Thanks Jon. You are really amazing………
Enrico says
This is awesome. I’ve implemented it into my multi-purpose tool, letting each user decide which size is better for him… so now It’s not only more Mac-friendly, but it can also help users with visual impairment… or very small displays :-)
Thanks!!!
Martin says
In Office 2019 for Mac you can create UserForms in the VB Editor. But once you closed the DesignerWindow there is not Option to open it again. The Button for the Object window opens the Code window. Is there any way to reopen the
Jon Peltier says
Martin –
I’m not using Office 2019 for Mac, I’m using Office 365 for Mac, Insider Fast (currently version 16.23, build 190212) which is supposedly more advanced. I don’t see where I can create a UserForm. If my project already has a UserForm, I can see it listed in the Project Explorer, and I can view the code module, but I cannot access the designer window.
I’m sure Microsoft is working on UserForm design for Mac. Perhaps it’s partially done, and accidentally they activated it for a short time, then pulled it back.
Christina Hau says
I am building some google sheets for both mac and window user, and i am contemplating whether to get a pc or macbook for myself. Does it make any difference what machine i use?
Jon Peltier says
Christina –
That’s really up to your preferences. The vast majority of my clients use PCs (99%?), and most of my software customers also use PCs (80-90%). Therefore. it makes sense for me to concentrate on Windows. though I have a MacBook lying around for testing and debugging. The user experience for Microsoft Office is better in Windows than on Mac; more reliable and also more features. I actually know a number of Mac users that run Windows versions of Office in a virtual machine on their Mac.
Arie Hond says
@Jon, you’re a genius! I almost had a heart attack when opening my work in Excel for Mac. But your code works perfectly.
@Martin, I also use Excel 365. On the internet you can find tons of Mac users complaining about the missing Userform design option. I too assume that Microsoft is working on it. Let’s hope they add this soon!
Tim Murray says
I am using a recent model Mac running Safari with Excel. My UserForms work well as programed. I have also downloaded your code to resize the UserForms. I am not sure how to use your code. Do I run your code first and then run my UserForm in an open workbook. This does not seem likely. When I run my UserForm first, then all other open workbooks are dimmed and cannot be used until I close the running UserForm. What am i missing here?
This is in reference to your “Designing UserForms for Mac and Windows” Blog.
Jon Peltier says
Tim –
The code I wrote is called from each UserForm’s Initialize event procedure (from UserForm_Initialize). Inside thee Initialize procedure you insert these lines:
#If Mac ThenResizeUserForm Me
#End If
This means it runs for the UserForm as it is loaded, before it is displayed. It should have no effect on what’s visible or active when the UserForm is loaded.
If you design your forms on the Mac, then they will be clownishly large in Windows, and you’d have to divide by, not multiply by, the conversion factor.
Tim says
Hi Jon,
This works great, however, I have noticed that when I have a Multipage on the form, the page that is initially active shows up with a white background on Mac. After changing to other pages (which show up as expected with the default grey) then changing back to the initially active page, it displays as expected. This does not happen without the re-sizing.
I was just wondering if you have ever encountered similar behavior, and/or if you have any ideas about how I might go about fixing this?
Thanks!
Jon Peltier says
Hi Tim –
I started using this code on my UserForms before Excel 2016 was released (Excel 2016 was the first version in which Mac and Windows user interfaces and VBA components actually looked alike). The Mac version was Excel 2011, and many of the UserForm controls looked awful, especially the MultiPage. So I designed all of my UserForms to use other approaches besides MultiPages (such as regular buttons that ran code to show and hide other controls). I no longer support 2011, but I’m not going back to redesign my UserForms again to include MultiPages, though I may resume using them in new UserForms. Point is, I haven’t noticed the issue you mention.
I’ve also designed my UserForms to use a light blue background rather than the default muddy gray. Having a non-default background may help to avoid the problem.
Here’s a real suggestion: When you load the UserForm, before you Show it, select a different page of the MultiPage, then select the page you want to open with. This may be enough to get the expected background color. If not, do the page switch right after you show it, and the flash might not be too noticeable.
Tim says
Thanks for the response Jon, much appreciated!
I have found that calling DoEvents before showing the form seems to resolve the problem. Not entirely sure why through…