It cannot get more simpler than this
This piece of code is applicable for forms and controls like Frame
1) Forms
Insert a new form and change it's height to say 420 (see pic1)
Place a few controls on the form.
Once you are done, change the height to say 255 (See pic2)
Now you don't need to add a scroll bar. Simply paste this code in the UserForm Activate event as shown below and your userform with scrollbars is ready. (see pic3)
Code:
Private Sub UserForm_Activate()
With Me
'This will create a vertical scrollbar
.ScrollBars = fmScrollBarsVertical
'Change the values of 2 as Per your requirements
.ScrollHeight = .InsideHeight * 2
.ScrollWidth = .InsideWidth * 9
End With
End Sub
and run the form. you will get the desired result.
2) Frames
Add a frame to the userform and name it say Frame1 (See pic4). In the userform activate event simply paste this code and your frame with a scroll bar is ready (See pic5). Again you don't need to add scrollbars control to the frame.
Code:
Private Sub UserForm_Activate()
'Name of the frame
With Me.Frame1
'This will create a vertical scrollbar
.ScrollBars = fmScrollBarsVertical
'Change the values of 2 as Per your requirements
.ScrollHeight = .InsideHeight * 2
.ScrollWidth = .InsideWidth * 9
End With
End Sub
Like I said, it cannot be more simpler than this