How to retain scrollbar position while switching between two frames

[jptaz]

Hello,

Here's my problem. I have two side-by-side frames in my userform with scrollbar enabled (height 2450 for both). Tab index are set for each textboxes as for both frames (0 and 1).

If I'm entering data in 1st frame and at mid page I need to look something in 2nd frame, when I switch again to 1st frame, it restarts at the first textbox.

Is there a way to lock frames scrollbars position while switching frames of pages?

Thank you for your time

 

[DanteAmor]

Try this:

Dim sPositionFrm1 As Double, sPositionFrm2 As Double  '<-- at the beginning of all code
  
Private Sub Frame1_Click()
  Frame1.ScrollTop = sPositionFrm1
End Sub
Private Sub Frame2_Click()
  Frame2.ScrollTop = sPositionFrm2
End Sub

Private Sub Frame1_Exit(ByVal Cancel As MSForms.ReturnBoolean)
  sPositionFrm1 = Frame1.ScrollTop
End Sub
Private Sub Frame2_Exit(ByVal Cancel As MSForms.ReturnBoolean)
  sPositionFrm2 = Frame2.ScrollTop
End Sub

 

[jptaz]

Try this:

Dim sPositionFrm1 As Double, sPositionFrm2 As Double  '<-- at the beginning of all code
 
Private Sub Frame1_Click()
  Frame1.ScrollTop = sPositionFrm1
End Sub
Private Sub Frame2_Click()
  Frame2.ScrollTop = sPositionFrm2
End Sub

Private Sub Frame1_Exit(ByVal Cancel As MSForms.ReturnBoolean)
  sPositionFrm1 = Frame1.ScrollTop
End Sub
Private Sub Frame2_Exit(ByVal Cancel As MSForms.ReturnBoolean)
  sPositionFrm2 = Frame2.ScrollTop
End Sub

Hello DanteAmor,

Thank you for your answer. It does save scrollbar position between frame switch, but it doesn't quite work for my needs as when I click on a textbox (and not on the frame) it sends me back to another textbox higher in the frame...

Would it be possible to adapt this with the tab index of the last textbox used on the frame, so it returns to this textbox after switching frame?

thanks again

JP

 

[DanteAmor]

Is there a way to lock frames scrollbars position while switching frames of pages?

Let's say I only answered what you asked in your original post.

I am not satisfied with the following solution, but make your second request.

Dim sPositionFrm1 As Double, sPositionFrm2 As Double  '<-- at the beginning of all code
Dim mControl_1 As Object, mControl_2 As Object
Dim entrando As Boolean

'Frame1 Controls
Private Sub TextBox1_Enter()
  If entrando Then entrando = False Else Set mControl_1 = TextBox1
End Sub
Private Sub TextBox2_Enter()
  If entrando Then entrando = False Else Set mControl_1 = TextBox2
End Sub
Private Sub TextBox3_Enter()
  If entrando Then entrando = False Else Set mControl_1 = TextBox3
End Sub
Private Sub TextBox4_Enter()
  If entrando Then entrando = False Else Set mControl_1 = TextBox4
End Sub

'Frame2 Controls
Private Sub TextBox5_Enter()
  If entrando Then entrando = False Else Set mControl_2 = TextBox5
End Sub
Private Sub TextBox6_Enter()
  If entrando Then entrando = False Else Set mControl_2 = TextBox6
End Sub
Private Sub TextBox7_Enter()
  If entrando Then entrando = False Else Set mControl_2 = TextBox7
End Sub
Private Sub TextBox8_Enter()
  If entrando Then entrando = False Else Set mControl_2 = TextBox8
End Sub

'Frames click or Enter
Private Sub Frame1_Click()
  Call ReturnPosition(Frame1, mControl_1, sPositionFrm1)
End Sub
Private Sub Frame1_Enter()
  Call ReturnPosition(Frame1, mControl_1, sPositionFrm1)
End Sub
Private Sub Frame2_Click()
  Call ReturnPosition(Frame2, mControl_2, sPositionFrm2)
End Sub
Private Sub Frame2_Enter()
  Call ReturnPosition(Frame2, mControl_2, sPositionFrm2)
End Sub

Sub ReturnPosition(frm As MSForms.Frame, mCtrl, sPos)
  frm.ScrollTop = sPos
  If Not mCtrl Is Nothing Then
    mCtrl.SetFocus
  End If
End Sub

'Frames Exit
Private Sub Frame1_Exit(ByVal Cancel As MSForms.ReturnBoolean)
  sPositionFrm1 = Frame1.ScrollTop
  entrando = True
End Sub
Private Sub Frame2_Exit(ByVal Cancel As MSForms.ReturnBoolean)
  sPositionFrm2 = Frame2.ScrollTop
  entrando = True
End Sub

 

[jptaz]

Let's say I only answered what you asked in your original post.

I am not satisfied with the following solution, but make your second request.

Dim sPositionFrm1 As Double, sPositionFrm2 As Double  '<-- at the beginning of all code
Dim mControl_1 As Object, mControl_2 As Object
Dim entrando As Boolean

'Frame1 Controls
Private Sub TextBox1_Enter()
  If entrando Then entrando = False Else Set mControl_1 = TextBox1
End Sub
Private Sub TextBox2_Enter()
  If entrando Then entrando = False Else Set mControl_1 = TextBox2
End Sub
Private Sub TextBox3_Enter()
  If entrando Then entrando = False Else Set mControl_1 = TextBox3
End Sub
Private Sub TextBox4_Enter()
  If entrando Then entrando = False Else Set mControl_1 = TextBox4
End Sub

'Frame2 Controls
Private Sub TextBox5_Enter()
  If entrando Then entrando = False Else Set mControl_2 = TextBox5
End Sub
Private Sub TextBox6_Enter()
  If entrando Then entrando = False Else Set mControl_2 = TextBox6
End Sub
Private Sub TextBox7_Enter()
  If entrando Then entrando = False Else Set mControl_2 = TextBox7
End Sub
Private Sub TextBox8_Enter()
  If entrando Then entrando = False Else Set mControl_2 = TextBox8
End Sub

'Frames click or Enter
Private Sub Frame1_Click()
  Call ReturnPosition(Frame1, mControl_1, sPositionFrm1)
End Sub
Private Sub Frame1_Enter()
  Call ReturnPosition(Frame1, mControl_1, sPositionFrm1)
End Sub
Private Sub Frame2_Click()
  Call ReturnPosition(Frame2, mControl_2, sPositionFrm2)
End Sub
Private Sub Frame2_Enter()
  Call ReturnPosition(Frame2, mControl_2, sPositionFrm2)
End Sub

Sub ReturnPosition(frm As MSForms.Frame, mCtrl, sPos)
  frm.ScrollTop = sPos
  If Not mCtrl Is Nothing Then
    mCtrl.SetFocus
  End If
End Sub

'Frames Exit
Private Sub Frame1_Exit(ByVal Cancel As MSForms.ReturnBoolean)
  sPositionFrm1 = Frame1.ScrollTop
  entrando = True
End Sub
Private Sub Frame2_Exit(ByVal Cancel As MSForms.ReturnBoolean)
  sPositionFrm2 = Frame2.ScrollTop
  entrando = True
End Sub

Thank you very much, it works really well!!

Have a nice day

JP

 

[DanteAmor]

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