Scrolling etc a Userform

[Chris Watts]

I wish to make a complete UserForm minimisable, resizable and (as
appropriate) scrollable; the whole form is modeless.
I have used Windows APIs to make it minimisable and resizable. I can add
the scroll bars but they appear all the time and, although you can move
them, it does not scroll the contents of the Userform Window.

The code that I use (in Excel 2007)is:

Option Explicit
Sub MakeFormResizable()
Dim UFHWnd As Long ' HWnd of UserForm
Dim WinInfo As Long ' Values associated with the UserForm window
Dim R As Long
Const GWL_STYLE = -16
Const WS_SIZEBOX = &H40000
Const WS_VSCROLL = &H200000
Const WS_HSCROLL = &H100000

Load PreviewForm ' Load the form into memory but don't make it visible
UFHWnd = FindWindow("ThunderDFrame", PreviewForm.Caption) ' find the
HWnd of the UserForm
If UFHWnd = 0 Then
' cannot find form
Debug.Print "UserForm not found"
Exit Sub
End If

WinInfo = GetWindowLong(UFHWnd, GWL_STYLE) ' get the style word
(32-bit Long)
WinInfo = WinInfo Or WS_SIZEBOX ' set the WS_SIZEBOX
bit
WinInfo = WinInfo Or WS_HSCROLL Or WS_VSCROLL ' Adds scrollbars
R = SetWindowLong(UFHWnd, GWL_STYLE, WinInfo) ' set the style word to
the modified value.
PreviewForm.Show
End Sub

Can anybody suggest what I have missed? Do I need to add Event handlers?

TIA
Chris

 

[Dave Peterson]

You may want to play with something like:

Option Explicit
Private Sub UserForm_Initialize()
With Me
.ScrollBars = fmScrollBarsBoth
.KeepScrollBarsVisible = fmScrollBarsBoth
.Height = .Height / 2
.ScrollHeight = 2 * .Height
.ScrollWidth = 2 * .Width
End With
End Sub

 

[Chris Watts]

Thanks Dave. I was also starting to think along those lines and indeed have
now found an acceptabe solution down that avenue.

much appreciated.
Chris