UserForm is shorter and narrower than in earlier versions of Excel

Symptoms
Assume that you set the Height and Width properties of a UserForm from a program, and then you use Microsoft Excel 2016 to display this UserForm. In this situation, you notice that the height and width are shorter and narrower (respectively) than in earlier versions of Excel. The Inside height and Inside width is also shorter and narrower, and it's at the right side and lower end of the window where the UserForm is cut off or truncated. Whereas the control layout in the main area of the UserForm looks the same as in earlier versions of Excel. Therefore, some parts of the form or the controls may be truncated.
Cause
This issue occurs because of a difference in the border size calculations between Excel 2016 and earlier versions of Excel.
Workaround
To work around this issue, set specific height and width values for the UserForm based on the version of Excel that you're using. This means specifying larger height and width properties in Excel 2016.
Status
Microsoft has confirmed that this is a bug in the Microsoft products that are listed in the "Applies to" section.
More information
To reproduce this issue, follow these steps.
  1. Open a new workbook in a pre-Excel 2016 version of Excel, such as Excel 2013.
  2. On the File tab, click Save As, click Browse. Click Excel Macro-Enabled Workbook (*.xlsm) in the Save as type box, type a file name in the File name box, and then click Save.
  3. Click the Developer tab. If the Developer tab is not displayed, follow these steps:
    1. On the File tab, click Options.
    2. Click Customize Ribbon.
    3. Select the Developer check box in the Main Tabs area.
    4. Click OK to close the Excel Options dialog box.
  4. Click Visual Basic in the Code group to start the Visual Basic Editor.
  5. In the Project - VBAProject pane, right-click to open the shortcut menu, click Insert, and then click UserForm.
  6. In the Toolbox window, select a control, and then paste it near the right and lower borders of the UserForm.
  7. Click the UserForm object, and then select and drag the corner mark to expand the Userform size.
  8. In the Project - VBAProject pane, right-click UserForm1, and click View Code.
  9. Follow these steps:
    1. In the Object box, click UserForm.
    2. In the Procedure box, click Initialize.
    3. Add the following macro code to the Initialize event:
      UserForm1.Height = 180UserForm1.Width = 240
    4. In the Project - VBAProject pane, right-click to open the shortcut menu, click Insert, and then click Module.
    5. In Module1, type the following macro code:

      Public Sub Test()        UserForm1.ShowEnd Sub
  10. On the Developer tab, click Macros in the Code group.
  11. Click the Test macro, and then click Run. You can see the control that is pasted in step 6 is shown correctly. And see the size of window to compare later.
  12. In the workbook, click the File tab, and then click Save.
  13. Copy this workbook to the computer on which Excel 2016 is installed.
  14. Open this workbook.
  15. On the Developer tab, click Macros in the Code group.
  16. Click the Test macro, and then click Run.
Result:

You can see that some parts of the control that was pasted in step 6 are truncated. Also, the size of window is narrower and shorter than the size in step 11.
Properties

Article ID: 3156159 - Last Review: 05/09/2016 16:25:00 - Revision: 1.0

Excel 2016

  • kbtshoot kbbug kbexpertiseadvanced KB3156159
Feedback