Problem in VBA userforms - resolution totally changed

I built an excel file with several userforms in VBA, which contain buttons (form controls). Usually i work with dock-station to connect my laptop to big screen (im my office). sometimes i open the file with no docking station (only laptop). I opened today the file, and found out that the resolution is totally changed, the user forms become so big and in all buttons the text inside are wrapped to right. Please your support to know How can i correct the problem?

thanks

enter image description here

1 answer

  • answered 2018-10-17 07:50 Chronocidal

    This is an issue caused by the File being open while your native screen resolution changes (and possibly only when this also changes the Aspect Ratio) - the most common reason for that being connecting or disconnecting a Laptop to/from an external screen (in this case, via your Docking Station)

    There are 2 forms this issue takes: either the Button stays the same size, and but the contents (text, images, etc) are scaled up/down while anchored in the top-left - which is what has happened here - or the contents stay the same size, but the button itself gets larger/smaller until it either covers the whole sheet or is too small to click.

    In my experience, the only way to fix the buttons is adjust them, and force Excel to redraw the shape instead of "remembering" what it should look like. You can do this manually, but I will try to track down some code to "reset" buttons for you. here is some code to do things for you.

    (For a UserForm, you may just be able to call Me.Repaint to force a redraw without needing to bother with the resizing - but I haven't tested that as I can never get this issue to happen on-demand >_<)

    UserForm Button Fix

    Sub FixButtonFormat(ByRef Button As Control)
        Dim Top As Double, Left As Double, Width As Double, Height As Double, FontName As String, FontSize As Double
    
        Top = Button.Top
        Left = Button.Left
        Width = Button.Width
        Height = Button.Height
        FontName = Button.Object.Font.Name
        FontSize = Button.Object.Font.Size
    
        'Scale Button up slightly
        Button.Top = Top - 1
        Button.Left = Left + 1
        Button.Width = Width - 2
        Button.Height = Height + 2
        Button.Object.Font.Size = FontSize + 1
    
        DoEvents
        UserForm1.Repaint
        DoEvents
    
        'Reset button to original size
        Button.Top = Top
        Button.Left = Left
        Button.Width = Width
        Button.Height = Height
        Button.Object.Font.Name = FontName
        Button.Object.Font.Size = FontSize
    End Sub
    

    Worksheet Button Fix

    Sub FixButtonFormat(ByRef Button As Shape)
        If Button.Type <> msoFormControl And Button.Type <> msoOLEControlObject Then Exit Sub
        Dim Top As Double, Left As Double, Width As Double, Height As Double, FontName As String, FontSize As Double
        Dim Screen As Boolean
        Screen = Application.ScreenUpdating
    
        Top = Button.Top
        Left = Button.Left
        Width = Button.Width
        Height = Button.Height
        If Button.Type = msoFormControl Then 'Form Control
            FontName = Button.OLEFormat.Object.Font.Name
            FontSize = Button.OLEFormat.Object.Font.Size
        ElseIf Button.Type = msoOLEControlObject Then 'ActiveX Control
            FontName = Button.DrawingObject.Object.Font.Name
            FontSize = Button.DrawingObject.Object.Font.Size
        End If
    
        'Scale Button up slightly
        Button.Top = Top - 1
        Button.Left = Left + 1
        Button.Width = Width - 2
        Button.Height = Height + 2
        If Button.Type = msoFormControl Then 'Form Control
            Button.OLEFormat.Object.Font.Size = FontSize + 1
        ElseIf Button.Type = msoOLEControlObject Then 'ActiveX Control
            Button.DrawingObject.Object.Font.Size = FontSize + 1
        End If
    
        If Not Screen Then
            Application.ScreenUpdating = True
            DoEvents
            Application.ScreenUpdating = False
        Else
            DoEvents
        End If
    
        'Reset button to original size
        Button.Top = Top
        Button.Left = Left
        Button.Width = Width
        Button.Height = Height
        If Button.Type = msoFormControl Then 'Form Control
            Button.OLEFormat.Object.Font.Name = FontName
            Button.OLEFormat.Object.Font.Size = FontSize
        ElseIf Button.Type = msoOLEControlObject Then 'ActiveX Control
            Button.DrawingObject.Object.Font.Size = FontSize
            Button.DrawingObject.Object.Font.Name = FontName
        End If
    End Sub