Scrolling in a UserForm Frame by a Code

[rajsingh0621]

Is it possible to scroll down in a UserForm Frame with a VBA Code in excel. My frame in the userform has a scrollheight of 600 and a lot of option buttons. Based on a selection in a ComboBox(using If statement), I'd like to scroll down to a certain amount in the Frame. Does anybody have any idea on it.

 

[mikerickson]

Something like this?

Sub scrollFrame(topVisibleControl As Object)
    Frame1.ScrollTop = topVisibleControl.Top
End Sub

Private Sub CommandButton1_Click()
    Call scrollFrame(OptionButton8)
End Sub

 

[Norie]

Why not use a listbox?

 

[mikerickson]

To respond to the confusion that you mentioned in your thankful PM.

The .Top (and .Left) property of a control in a Frame (or MultiPage) is relative to that Frame, not the UserForm.

The .ScollTop property of a Frame is the .Top of the visible portion of a scrolled Frame (relative to the Frame).

 

[rajsingh0621]

So the code works, but I still can't fully understand it. So far I haven't learned any functions in vba, but I thought only in functions you put something in the () after sub. Over here you have topvisiblecontrol as Object in the parenthesis.. So tobvisiblecontrol is the variable. But how come Dim topvisiblecontrol as Object doesn't work if I write it in the next line.

Then you say Call ScrollFrame(OptionButton#), so I understand this part that it calls the ScrollFrame macro, but I still don't understand why we're putting the OptionButton# in the parenthesis.

 

[rajsingh0621]

I also just tried.
If ComboBox1 = 1 Then
Frame1.ScrollTop = OptionButton3.Top
End if

But I'd still like to understand the other macro fully.

 

[mikerickson]

"how come Dim topvisiblecontrol as Object doesn't work if I write it in the next line."
A variable has to be declared before it is used.
When a variable is the argument of a procedure, the procedure declaration statement also declares the variables that

Sub scrollFrame(topVisibleControl As Object)
' Declares sub named scrollFrame and (procedure scoped) variable named topVisibleControl

 

[MPW]

Sub scrollFrame(topVisibleControl As Object)
    Frame1.ScrollTop = topVisibleControl.Top
End Sub

Private Sub CommandButton1_Click()
    Call scrollFrame(OptionButton8)
End Sub

Thanks for this.
Not sure why this was not a built in control in the first place but your work around works fine.