Consulting

Results 1 to 3 of 3

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

  1. #1
    VBAX Regular
    Joined
    Mar 2021
    Posts
    6
    Location

    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
    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#)
    '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
    Attached Files Attached Files

  2. #2
    VBAX Regular
    Joined
    Mar 2021
    Posts
    6
    Location
    Problem has been solved by changing
    Public Sub ResizeUserForm(frm As Object, Optional dResizeFactor As Double = 0#)
    to
    Public Sub ResizeUserForm(frm As Object, Optional dResizeFactor As Variant = 0#)
    This is quite irritating as I can see no reason why the double type should have thrown the error, or why the error should have occurred in only in such specific circumstances.

  3. #3
    Moderator VBAX Wizard Aussiebear's Avatar
    Joined
    Dec 2005
    Location
    Queensland
    Posts
    5,232
    Location
    Thank you for posting the solution
    Remember To Do the Following....
    Use [Code].... [/Code] tags when posting code to the thread.
    Mark your thread as Solved if satisfied by using the Thread Tools options.
    If posting the same issue to another forum please show the link

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
  •