Resizing user form in excell

  • Thread starter Thread starter lmnorms1
  • Start date Start date
L

lmnorms1

I have created a form. The form is quite large - 60 fields to fill in
(The customer wanted it that way). The form is larger than the screen
were the resolution is low on the monitor. I can have the customer
adjust the resolution and then the whole form will show up on the
screen.
I was wondering if a scroll bar can be added so the customer can move
to the lower half of the form. How do I add it in?

I tried changing the form properties but that would not allow the form
to be moved or resized.

Any ideas would be greatly appreciated.

Thank you,
(e-mail address removed)
 
click on the userform in the vba editor, then view properties. change
the "Scrollbars" properties as desired.

HTH

Die_Another_Day
 
Hello,
I am sorry to report this had no effect.

I would be willing to try another idea.

Thank you,
lmnorms1
 
You also need to set the scroll height property. this can be done on
the fly like so:

userform1.scrollheight = 12321 'Random number
or if you know the farthest control down...
userform1.scrollheight = CommandButton1.Top + CommandButton1.Height

HTH

Die_Another_Day
 
Thank you that works for scrolling. Do you have the formula for
resizing the form to screen be proportioned to the veiwable screen?

Lmnorms1
 
Give this a try.
Option Explicit

Private Const SPI_GETWORKAREA = 48

Private Declare Function SystemParametersInfo Lib "user32" _
Alias "SystemParametersInfoA" (ByVal uAction As Long, _
ByVal uParam As Long, ByRef lpvParam As Any, _
ByVal fuWinIni As Long) As Long

Private Type RECT
Left As Long
Top As Long
Right As Long
Bottom As Long
End Type

Sub SizeForm()
Dim nRect As RECT

SystemParametersInfo SPI_GETWORKAREA, 0, nRect, 0
With UserForm1
.Top = nRect.Top
.Left = nRect.Left
.Width = (nRect.Right - nRect.Left) * 0.75
.Height = (nRect.Bottom - nRect.Top) * 0.75
End With
UserForm1.Show

End Sub

HTH

Die_Another_Day

P.S. Can someone explain to me why I have to multiply my height and
width by .75 to get it to work properly?
 
Thank you,
That works very nicely.

I very much appreciate it.

lmnorms1
Let me know if I can ever help you.
 
Back
Top