1 Attachment(s)
OverFlow Error only on Apple M1 Pro When Resizing Userform
I have a excel project that has been running successfully on Windows and Mac for sometime.
One of the steps includes resizing a userform if the user is on a Mac. The user is prompted, and can choose 'yes' or 'no' to resize forms.
This was fine until a couple of users bought a new 14 inch M1 Macbook Pro (2021) running Monterey
These users and ONLY these users reported getting an overflow error (Runtime error 6) if they allow the resizing.
Interestingly...
The error DOES NOT occur if the user is displaying the workbook on an external screen.
The error DOES NOT occur if the code is manually stepped through.
The error DOES NOT occur on older mac models (has functioned on High Sierra through to Big Sur)
Any suggestions would be greatly appreciated.
Calling code in the user form
Code:
Private Sub UserForm_Initialize()
#If Mac Then
If MsgBox("Use MAC form resizing?", vbYesNo) = vbYes Then
ResizeUserForm Me
End If
#End If
'other code
End Sub
Resizing sub in a standard module
Code:
Option Explicit
Public Const gUserFormResizeFactor As Double = 1.4
'Global variable used as plan is to allow users to manually adjust resizing in some circumstances. This is not yet implemented.
'intermittant error on calling this sub
Public Sub ResizeUserForm(frm As Object, Optional dResizeFactor As Double = 0#)
'https://peltiertech.com/userforms-for-mac-and-windows/
Dim ctrl As Control
Dim sColWidths As String
Dim vColWidths As Variant
Dim iCol As Long
If dResizeFactor = 0 Then
dResizeFactor = gUserFormResizeFactor
End If
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 Sub
