Results 1 to 3 of 3

Thread: OverFlow Error only on Apple M1 Pro When Resizing Userform

Threaded View

Previous Post Previous Post   Next Post Next Post
  1. #1
    VBAX Regular
    Mar 2021

    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.

    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
    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
    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#)
        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
                                sColWidths = Join(vColWidths, ";")
                                ctrl.ColumnWidths = sColWidths
                            End If
                    End Select
                End With
        End With
    End Sub
    Attached Files Attached Files

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts