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?
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.Repaintto 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