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
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