Stop MSForms.Frame Scrollbars From Resetting On Focus

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

AnnoyingScrollbar

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

Advertisements

About Colin Legg

RAD Developer Microsoft MVP - Excel 2009 - 2014
This entry was posted in Microsoft Excel and tagged , , , . Bookmark the permalink.

2 Responses to Stop MSForms.Frame Scrollbars From Resetting On Focus

  1. otto schuldt says:

    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.

    Like

    • Colin Legg says:

      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.

      Like

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s