I’ve been doing some more work on my dynamic grid control – which mostly comprises of MSForms Comboboxes in a Frame. A feature of a Frame control with scrollbars is that when it gets the focus the scrollbars’ positions reset themselves. Here’s some quick and dirty code you can put in a userform’s code module to see what I mean:
Option Explicit
Private Sub UserForm_Initialize()
Const sngFRAME_SPACING As Single = 5
Dim objFrame1 As MSForms.Frame
Dim objFrame2 As MSForms.Frame
Width = 400
Height = 200
Set objFrame1 = Controls.Add("Forms.Frame.1")
With objFrame1
.Width = (InsideWidth - sngFRAME_SPACING * 3) / 2
.Left = sngFRAME_SPACING
.ScrollBars = fmScrollBarsVertical
AddTextBoxes objFrame1
With .Controls(.Controls.Count - 1)
objFrame1.ScrollHeight = .Top + .Height
End With
End With
Set objFrame2 = Controls.Add("Forms.Frame.1")
With objFrame2
.Width = (InsideWidth - sngFRAME_SPACING * 3) / 2
.Left = sngFRAME_SPACING * 2 + .Width
.ScrollBars = fmScrollBarsVertical
AddTextBoxes objFrame2
With .Controls(.Controls.Count - 1)
objFrame2.ScrollHeight = .Top + .Height
End With
End With
End Sub
Private Sub AddTextBoxes(ByRef objFrame As MSForms.Frame)
Const sngTEXTBOX_SPACING As Single = 10
Dim objTextBox As MSForms.TextBox
Dim t As Long
For t = 0 To 20
Set objTextBox = objFrame.Controls.Add("Forms.TextBox.1")
With objTextBox
.Left = sngTEXTBOX_SPACING
.Width = objFrame.InsideWidth - sngTEXTBOX_SPACING * 2
.Top = sngTEXTBOX_SPACING + t * (.Height + sngTEXTBOX_SPACING)
.Text = CStr(t)
End With
Next t
End Sub
I couldn’t have this annoying behaviour happening with my grid control so it was something that had to be dealt with. I couldn’t find any documentation on how to stop it but, since one can intuitively expect a Frame.Scroll() event to be raised when the scrollbars are reset, the first thing I did was refactor the code so it subscribed to that event:
Option Explicit
Private WithEvents mobjFrame1 As MSForms.Frame
Private WithEvents mobjFrame2 As MSForms.Frame
Private Sub mobjFrame1_Scroll( _
ByVal ActionX As MSForms.fmScrollAction, _
ByVal ActionY As MSForms.fmScrollAction, _
ByVal RequestDx As Single, _
ByVal RequestDy As Single, _
ByVal ActualDx As MSForms.ReturnSingle, _
ByVal ActualDy As MSForms.ReturnSingle)
End Sub
Private Sub mobjFrame2_Scroll( _
ByVal ActionX As MSForms.fmScrollAction, _
ByVal ActionY As MSForms.fmScrollAction, _
ByVal RequestDx As Single, _
ByVal RequestDy As Single, _
ByVal ActualDx As MSForms.ReturnSingle, _
ByVal ActualDy As MSForms.ReturnSingle)
End Sub
Private Sub UserForm_Initialize()
Const sngFRAME_SPACING As Single = 5
Width = 400
Height = 200
Set mobjFrame1 = Controls.Add("Forms.Frame.1")
With mobjFrame1
.Width = (InsideWidth - sngFRAME_SPACING * 3) / 2
.Left = sngFRAME_SPACING
.ScrollBars = fmScrollBarsVertical
AddTextBoxes mobjFrame1
With .Controls(.Controls.Count - 1)
mobjFrame1.ScrollHeight = .Top + .Height
End With
End With
Set mobjFrame2 = Controls.Add("Forms.Frame.1")
With mobjFrame2
.Width = (InsideWidth - sngFRAME_SPACING * 3) / 2
.Left = sngFRAME_SPACING * 2 + .Width
.ScrollBars = fmScrollBarsVertical
AddTextBoxes mobjFrame2
With .Controls(.Controls.Count - 1)
mobjFrame2.ScrollHeight = .Top + .Height
End With
End With
End Sub
Private Sub AddTextBoxes(ByRef objFrame As MSForms.Frame)
Const sngTEXTBOX_SPACING As Single = 10
Dim objTextBox As MSForms.TextBox
Dim t As Long
For t = 0 To 20
Set objTextBox = objFrame.Controls.Add("Forms.TextBox.1")
With objTextBox
.Left = sngTEXTBOX_SPACING
.Width = objFrame.InsideWidth - sngTEXTBOX_SPACING * 2
.Top = sngTEXTBOX_SPACING + t * (.Height + sngTEXTBOX_SPACING)
.Text = CStr(t)
End With
Next t
End Sub
In this case I wanted to override the behaviour of the vertical scrollbar so I was interested in the Scroll() event’s "Y" parameters. As it turns out, the ActionY parameter will have a value of fmScrollActionFocusRequest when the Frame is given the focus, so it let me easily pick out the “auto reset” I wanted to block. The ActualDy.Value is how much the vertical scrollbar is going to be scrolled. ActualDy is passed ByVal but, because it is an object, I could just set its Value to 0 and that revised value would be recognised from where the event was raised from. If you run the below code you’ll find that the scrollbars don’t reset when each frame gets the focus:
Option Explicit
Private WithEvents mobjFrame1 As MSForms.Frame
Private WithEvents mobjFrame2 As MSForms.Frame
Private Sub mobjFrame1_Scroll( _
ByVal ActionX As MSForms.fmScrollAction, _
ByVal ActionY As MSForms.fmScrollAction, _
ByVal RequestDx As Single, _
ByVal RequestDy As Single, _
ByVal ActualDx As MSForms.ReturnSingle, _
ByVal ActualDy As MSForms.ReturnSingle)
If ActionY = fmScrollActionFocusRequest Then
ActualDy.Value = 0
End If
End Sub
Private Sub mobjFrame2_Scroll( _
ByVal ActionX As MSForms.fmScrollAction, _
ByVal ActionY As MSForms.fmScrollAction, _
ByVal RequestDx As Single, _
ByVal RequestDy As Single, _
ByVal ActualDx As MSForms.ReturnSingle, _
ByVal ActualDy As MSForms.ReturnSingle)
If ActionY = fmScrollActionFocusRequest Then
ActualDy.Value = 0
End If
End Sub
Private Sub UserForm_Initialize()
Const sngFRAME_SPACING As Single = 5
Width = 400
Height = 200
Set mobjFrame1 = Controls.Add("Forms.Frame.1")
With mobjFrame1
.Width = (InsideWidth - sngFRAME_SPACING * 3) / 2
.Left = sngFRAME_SPACING
.ScrollBars = fmScrollBarsVertical
AddTextBoxes mobjFrame1
With .Controls(.Controls.Count - 1)
mobjFrame1.ScrollHeight = .Top + .Height
End With
End With
Set mobjFrame2 = Controls.Add("Forms.Frame.1")
With mobjFrame2
.Width = (InsideWidth - sngFRAME_SPACING * 3) / 2
.Left = sngFRAME_SPACING * 2 + .Width
.ScrollBars = fmScrollBarsVertical
AddTextBoxes mobjFrame2
With .Controls(.Controls.Count - 1)
mobjFrame2.ScrollHeight = .Top + .Height
End With
End With
End Sub
Private Sub AddTextBoxes(ByRef objFrame As MSForms.Frame)
Const sngTEXTBOX_SPACING As Single = 10
Dim objTextBox As MSForms.TextBox
Dim t As Long
For t = 0 To 20
Set objTextBox = objFrame.Controls.Add("Forms.TextBox.1")
With objTextBox
.Left = sngTEXTBOX_SPACING
.Width = objFrame.InsideWidth - sngTEXTBOX_SPACING * 2
.Top = sngTEXTBOX_SPACING + t * (.Height + sngTEXTBOX_SPACING)
.Text = CStr(t)
End With
Next t
End Sub
RAD Excel 
Very helpful this explanation. But I have another problem, what if I have many controls within a frame. Using your code will not let the user to scroll down the frame with the keyboard’s arrows or if the user clicks the tab button. Is there any way to block the “auto reset” behavior without disabling what I just mentioned?
I would appreciate your help.
Many thanks.
LikeLike
Hi,
No, I don’t think there’s a way to differentiate them. The way I handled this in my grid control was to trap the KeyDown event on the controls embedded within the frame and to check the KeyCode/Shift arguments. I would then scroll the frame to show the row above or below if needed. I also enabled mouse wheel scrolling on it. It’s not ideal and quite complicated to implement but it was the best I could come up with.
LikeLike
Thanks for posting. It’s the only place I saw this code solution for stopping the frame’s vertical scrollbar from resetting when your return to the frame.
LikeLike
Hi Colin. I have incorporated this code into a userform. I have a number of Command Buttons on the form but not within the frame, When I click on any of these and not even run any code then the frame will reset on focus, It is fine if I do not click on any of these buttons which is all I have outside of the form. Any ideas / feedback appreciated. Thanks.
LikeLike