Fixed userform frame controlled by scroll in parent frame

[bradyboyy88]

Basically I have a parent frame that has a vertical scroll and a child frame inside of it. I need it such that when I scroll the parent frame scroll bar the parent frame does not actually scroll its body but instead scrolls the child frame. I am able to get this working except i use actualdy to keep the frame from moving but when i do that the scroll bar does not move. Is there a way to just use the scroll bar and cancel out the changes in the movement?

Here is my code so far:

Public Sub ParentFrame_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)
Debug.Print "ActionX:" & ActionX & " ActionY:" & ActionY & " RequestDx:" & RequestDx & " RequestDy:" & RequestDy & " ActualDx:" & ActualDx & " ActualDy:" & ActualDy
    If ActionY = fmScrollActionFocusRequest Or ActionY = fmScrollActionControlRequest Then
        ActualDy.value = 0
    End If
    
    MMCARMS.ChildFrame.ScrollTop = MMCARMS.ChildFrame.ScrollTop + RequestDy
    ActualDy.value = 0
    
  End Sub

 

[mikerickson]

In my test userform. Frame1 is the outer Frame (parent). Frame 2 is the inner.

At startup Frame1 holds a text box at top, Frame2 in the middle and Label3 at the bottom
At startup Frame2 hold Label1 at the top a textbox in the middle and Label2 at the bottom.

The intialize event sets up the position of Label2 and Lable3 so that scrolling makes sense.

In this routine, scrolling the outer frame will scroll the inner frame and will also scroll the outer frame and will move the controls in the outerframe so that they appear to be in the same place.

One would have to add code to adjust the .ScrollHeight and to guard against excess. But the concept of moving the controls so that scrolling the outer frame has no visual effect is a concept worth looking at.

' in userforms code module

Dim DisableMyEvents 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)
    Dim oneControl As MSForms.Control
    If DisableMyEvents Then Exit Sub
    
    Frame2.ScrollTop = Frame2.ScrollTop + RequestDy
    For Each oneControl In Frame1.Controls
        If oneControl.Parent.Name = "Frame1" Then
            ' is onecontrol in the Frame or in the sub-frame
            oneControl.Top = oneControl.Top + ActualDy
        End If
    Next oneControl
End Sub

Private Sub UserForm_Initialize()
    With Label3
        .BorderStyle = fmBorderStyleSingle
        .Height = 18
        .Caption = " out bottom"
        .Top = 900
    End With
    With Label2
        .BorderStyle = fmBorderStyleSingle
        .Height = 18
        .Caption = " in bottom"
        .Top = 400
    End With
    With Frame2
        .Top = 100
        .ScrollHeight = Label2.Top + Label2.Height + 3
        .ScrollBars = fmScrollBarsVertical
    End With
    With Frame1
        .ScrollHeight = Label3.Top + Label3.Height + 3
        .ScrollBars = fmScrollBarsVertical
        DisableMyEvents = True
        .ScrollTop = 50
        DisableMyEvents = False
    End With
End Sub

 

[bradyboyy88]

In my test userform. Frame1 is the outer Frame (parent). Frame 2 is the inner.

At startup Frame1 holds a text box at top, Frame2 in the middle and Label3 at the bottom
At startup Frame2 hold Label1 at the top a textbox in the middle and Label2 at the bottom.

The intialize event sets up the position of Label2 and Lable3 so that scrolling makes sense.

In this routine, scrolling the outer frame will scroll the inner frame and will also scroll the outer frame and will move the controls in the outerframe so that they appear to be in the same place.

One would have to add code to adjust the .ScrollHeight and to guard against excess. But the concept of moving the controls so that scrolling the outer frame has no visual effect is a concept worth looking at.

' in userforms code module

Dim DisableMyEvents 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)
    Dim oneControl As MSForms.Control
    If DisableMyEvents Then Exit Sub
   
    Frame2.ScrollTop = Frame2.ScrollTop + RequestDy
    For Each oneControl In Frame1.Controls
        If oneControl.Parent.Name = "Frame1" Then
            ' is onecontrol in the Frame or in the sub-frame
            oneControl.Top = oneControl.Top + ActualDy
        End If
    Next oneControl
End Sub

Private Sub UserForm_Initialize()
    With Label3
        .BorderStyle = fmBorderStyleSingle
        .Height = 18
        .Caption = " out bottom"
        .Top = 900
    End With
    With Label2
        .BorderStyle = fmBorderStyleSingle
        .Height = 18
        .Caption = " in bottom"
        .Top = 400
    End With
    With Frame2
        .Top = 100
        .ScrollHeight = Label2.Top + Label2.Height + 3
        .ScrollBars = fmScrollBarsVertical
    End With
    With Frame1
        .ScrollHeight = Label3.Top + Label3.Height + 3
        .ScrollBars = fmScrollBarsVertical
        DisableMyEvents = True
        .ScrollTop = 50
        DisableMyEvents = False
    End With
End Sub

Thanks for this. By the way is there a way to tell if a scrollbar has reached the bottom of its scroll?

 

[mikerickson]

Untested, but I'd look to see if Frame1.ScrollTop + Frame1.InsideHeight = Frame1.ScrollHeight