'How to stop scroll bar auto jump in vba

I have a userform with a frame (Frame1) consisting a vertical scroll bar of height set to 1000 , the Frame houses 160 textboxes arranged in 40 rows and 4 columns. The 40 textboxes on the first column each have a list box. The problem is when I double click on the list box to select an item to the textbox, the scroll bar jumps down or jumps up by it self. This can be quite frustrating for the user. Please I need some help on how to keep the scroll bar static when double clicking a list box in the frame. Sub listbox1_Dbclick() textbox1.Value = Me.listbox.text End Sub

excel vba

Solution 1:^[1]

you can change the property of scrollbars and check.

This may the problem of setting property. Bcz i never get this kind of error.

Please share your code first then forum can help you better.

Solution 2:^[2]

After some research, found this way to fix the Excel bug

    Private Sub MainFrame_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)
'    Stop
'    Debug.Print ActionX
'    Debug.Print ActionY
'    Debug.Print RequestDx
'    Debug.Print RequestDy
'    Debug.Print ActualDx
'    Debug.Print ActualDy
    If RequestDy = 299.45 Then
'        Stop
        ActualDy = 0
    End If

End Sub

When VBA makes scroll bar jump, the RequestDy property has particular value (299.45 in my case, evaluated by commented Debug.Print commands)
In this cases, I have cancel the jump with ActualDy = 0 command

Solution 3:^[3]

I come across the same issue and whilst I have been unable to determine the precise cause of the scrolldown when the frame is selected, I do have a solution. It is based upon the solution offered by @vova, but whereas that solution only works if the frame height is fixed (e.g. at 299.45), not dynamic, the solution below should work for any frame height.

Private Frame1_Opened As Boolean

Private Sub Frame1_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 Frame1_Opened = False Then
    ActualDy = 0
    Frame1_Opened = True
End If
End Sub

Sources

This article follows the attribution requirements of Stack Overflow and is licensed under CC BY-SA 3.0.

Source: Stack Overflow

Solution	Source
Solution 1	Shyam Pandey
Solution 2	Vova
Solution 3	Peter

'How to stop scroll bar auto jump in vba

Solution 1:[1]

Solution 2:[2]

Solution 3:[3]

Sources

Related Questions

Solution 1:^[1]

Solution 2:^[2]

Solution 3:^[3]