Resize Userform for Mac or Windows

When it comes to compatibility between Mac and Windows Userforms in Excel (as long as your version of Mac Excel is 2011 or newer) there are a number of anomalies that exist and require a little bit of creative programming in VBA but one issue that we are always asked about when developing for a Mac user is the size of the Userform and the textboxes, comboboxes etc on the screen.

The Mac has a far superior resolution to Windows in most cases and when a Userform system is developed on Windows to be used on a Mac or is required to be used on both platforms, a quick procedure needs to be considered at Userform Initialisation. The VBA below needs to be added to the actual userform that is being opened, or initialised. It makes no odds if it is at the top, bottom or in the middle but the code needs to be there and the call procedure needs to be in the Userform Initialise module.

You may note the value of 1.5 in the code. This creates the userform at 1.5 (or 150%) the size of the designed Userform which makes it far more user friendly for a Mac user. This 1.5 value can be increased or decreased accordingly. As the Userform opens, it will detect the operating system and set the size that best suits the operating environment.

Full credit for this code needs to go to Ron DeBruin, a great source for Mac Excel codes and snippets. Whilst we reproduce this code here for your ease of reference, you can see Ron’s pages by visiting: http://www.rondebruin.nl

Feel free to ask us a question or leave a comment below.



'This part goes into the Userform_Initialise module.
Call AdjustSizeForMac

'This code goes in each userform that is opened in your project.
Sub AdjustSizeForMac()

Dim ControlOnForm As Object

#If Win32 Or Win64 Then

'No mac so not run the code

Exit Sub

#End If

'Change size coefficient

Const SizeCoefForMac = 1.5

With Me

'Change Userform size

.Width = .Width * SizeCoefForMac

.Height = .Height * SizeCoefForMac

'Change controls/font on the userform

For Each ControlOnForm In .Controls

With ControlOnForm

.Top = .Top * SizeCoefForMac

.Left = .Left * SizeCoefForMac

.Width = .Width * SizeCoefForMac

.Height = .Height * SizeCoefForMac

On Error Resume Next

.Font.Size = .Font.Size * SizeCoefForMac

On Error GoTo 0

End With

Next

End With

End Sub


Contact us for some advice and guidance on how your Excel development could be created and start helping your business straight away. Contact Us

Datanology

Leave a Reply

Your email address will not be published. Required fields are marked *