How to stop Frame Scroll Bar from jumping in vba

[De Bruce]

Good day all,
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.
Thanks in advance

 

[DanteAmor]

Good day all,
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.
Thanks in advance

I'm not sure. Do you have the listbox inside the frame?
Can you put the listbox out of the frame?
Or do you have 40 listbox, one for each textbox?


With this property you can move the frame scroll.

For example, you select the textbox7, then click on the listbox, the scroll of the frame is updated at the same height of the selected textbox.

Dim PosTop


Private Sub ListBox1_Click()
[COLOR=#0000ff]    Frame1.ScrollTop = PosTop[/COLOR]
End Sub


Private Sub TextBox1_Enter()
    PosTop = TextBox1.Top
End Sub


Private Sub TextBox6_Enter()
    PosTop = TextBox6.Top
End Sub


Private Sub TextBox7_Enter()
    PosTop = TextBox7.Top
End Sub

I understand that you have 40 textbox, but you can create a class and add all the textbox, so you don't have to create an Enter event for each textbox.

I hope that helps.

 

[De Bruce]

Dante Amor, thanks for the suggestion. Actually each of the 40 text box have a listbox. The 40 textboxes are arranged in vertical order, starting from textbox1 to textbox40.
When I click on the textbox, the listbox becomes visible (using textbox_change event) and I double click on an item to select from the listbox. The problem is when I double click to select an item to be filled in the corresponding textbox, the scroll bar immediately jumps up and I have to scroll down back to continue from where I stopped. Once again, thanks for your concern.
Least I forget, I do have all the listbox and textbox in a frame. If I bring them out of the frame, it won't fit into the userform that houses the frame.

 

[DanteAmor]

Then you must put the PosTop variable, in each change event.

If you like, I check it and make the adaptation.

You could upload a copy of your file to a free site such www.dropbox.com. Once you do that, mark it for 'Sharing' and you will be given a link to the file that you can post here. If the workbook contains confidential information, you could replace it with generic data.

 

[De Bruce]

Thanks Dante Amor, I tried the PosTop and it worked well. You're a genius. Thanks

 

[DanteAmor]

Thanks Dante Amor, I tried the PosTop and it worked well. You're a genius. Thanks

I'm glad to help you. Thanks for the feedback.