0

I had just been assigned to maintain Excel VBA Script. I created a UI on the worksheet but I found that the resolution of the PC would affect the UI of the Excel documents such as causing the button size to enlarge and the button text size to reduce while the user clicked. Did anyone know how to solve it?

XDfox
  • 49
  • 7
  • 1
    This is a very old issue. Do not use ActiveX controls on Worksheets. Use Form Controls. Besides the UI on the worksheet, these worksheet which have ActiveX controls on Worksheets will not work on MAC. – Siddharth Rout Jan 04 '23 at 05:09
  • This post looks useful. https://stackoverflow.com/questions/50144020/differences-between-excels-form-controls-activex-controls/50144021#50144021 – user10186832 Jan 04 '23 at 07:24

1 Answers1

0

I had tried this solution and it is totally workable.

Reference from: VBA Excel Button resizes after clicking on it (Command Button)

Sub Shared_ObjectReset()

Dim MyShapes As OLEObjects
Dim ObjectSelected As OLEObject

Dim ObjectSelected_Height As Double
Dim ObjectSelected_Top As Double
Dim ObjectSelected_Left As Double
Dim ObjectSelected_Width As Double
Dim ObjectSelected_FontSize As Single

ActiveWindow.Zoom = 100

'OLE Programmatic Identifiers for Commandbuttons = Forms.CommandButton.1
Set MyShapes = ActiveSheet.OLEObjects
For Each ObjectSelected In MyShapes
    'Remove this line if fixing active object other than buttons
    If ObjectSelected.progID = "Forms.CommandButton.1" Then
        ObjectSelected_Height = ObjectSelected.Height
        ObjectSelected_Top = ObjectSelected.Top
        ObjectSelected_Left = ObjectSelected.Left
        ObjectSelected_Width = ObjectSelected.Width
        ObjectSelected_FontSize = ObjectSelected.Object.FontSize

        ObjectSelected.Placement = 3

        ObjectSelected.Height = ObjectSelected_Height + 1
        ObjectSelected.Top = ObjectSelected_Top + 1
        ObjectSelected.Left = ObjectSelected_Left + 1
        ObjectSelected.Width = ObjectSelected_Width + 1
        ObjectSelected.Object.FontSize = ObjectSelected_FontSize + 1

        ObjectSelected.Height = ObjectSelected_Height
        ObjectSelected.Top = ObjectSelected_Top
        ObjectSelected.Left = ObjectSelected_Left
        ObjectSelected.Width = ObjectSelected_Width
        ObjectSelected.Object.FontSize = ObjectSelected_FontSize

    End If
Next

End Sub

XDfox
  • 49
  • 7