vba to automatically resize userform depending on screen size

Hi kirana2014,

You can use 'ActiveWindow.Height' or 'Application.Height' to determine the maximum visible UserForm height. If the UserForm is larger than the 'maximum visible height', you can add ScrollBars to the UserForm.

See the attached file which includes UserForm MouseWheel code (32 bit Excel only) at no extra charge. For additional UserForm MouseWheel code see post #23 associated with: http://www.excelforum.com/excel-prog...orm-frame.html

Sub DisplayUserForm1()
  
  Dim xScreenHeight As Double
  Dim xUserFormOriginalHeight As Double
  
  Dim sMessage As String
  
  'Override the Design 'UserForm Height' for Testing Purposes
  UserForm1.Height = ThisWorkbook.Sheets("Sheet1").Range("C14").Value
    
  'Get the UserForm Height
  'Get the Screen Height (using 'ActiveWindow.Height' - NOTE: 'Application.Height' can also be used)
  xUserFormOriginalHeight = UserForm1.Height
  xScreenHeight = ActiveWindow.Height
  
  'Adjust the UserForm 'Height' and add 'ScrollBars' if the 'UserForm Height' is larger than the 'Screen Height'
  If xUserFormOriginalHeight > xScreenHeight Then
    UserForm1.Height = xScreenHeight
    UserForm1.ScrollBars = fmScrollBarsVertical
    UserForm1.ScrollHeight = xUserFormOriginalHeight
  End If
  
  'Put a message in Label1
  sMessage = _
    "UserForm original height: " & Format(xUserFormOriginalHeight, "0.00") & vbCrLf & _
    "Active Window Height:      " & Format(xScreenHeight, "0.00") & vbCrLf & _
    ""
  UserForm1.Label1.Caption = sMessage

  'Display the UserForm as Modal (Locks out Excel)
  UserForm1.Show vbModal

End Sub

Lewis

Daer Lewis,Mike,


Thank you so much for your attention and time.

Dear Lewis,Mike,


Thank you so much for your attention and time.