VBA Scroll Bar

[Awaiz]

Hi All,

Can any one explain how a simple scroll bar can be used in a userform?
I have a frame on the from which I want to scroll vertically (its contents).
Just the VBA code to link a frame and a scroll bar..

Thanks,,

 

[Aaron Blood]

Sure... but actually there is no code to share.

The Scrollbar properties of both UserForms and Frames objects on UserForms function pretty much the same way.

In your case, you would select the Frame in the VBE and then look at the frame object properties window... scroll down to the Scrollbars property and set it to both vertical and horizontal then you just play with the various "Scroll" properties to set "ScrollHeight", "ScrollWidth", etc. Setting the scrollbars property for the UserForm would work exactly the same way.

Experiment with the Scroll property settings and you should be able to figure it out.

 

[Awaiz]

Hi Aaron,
Thnx for the quick reply..
I figured that out but the reason I posted this was that I wanted to:
1. Change the color of the Scroll bar and wanted the frame color to be normal VBA form color.
2. On some click event wanted to hide the frame but the scroll bar needs to be still there.

But these won't be possible as the frame and the scroll bar is merged in this case..
Hence wanted to put a separate scroll bar and link that with the frame to work as normal.

Don't know if I am clear enough wht I actually need.. Let me know in case of any queries.
Thnks

 

[Awaiz]

Can any 1 help out pls......

 

[mikerickson]

I don't like the scrollbar that comes with the Frame, so I add a scrollbar control and link it to the frame.

The properties of the Frame that need to be looked at are the .Height, .ScrollHeight and .ScrollTop properties (similar for .Width, .ScrollWidth and .ScrollLeft)

Private Sub ScrollBar1_Change()
    Frame1.ScrollTop = ScrollBar1.Value
End Sub

Private Sub UserForm_Initialize()
    Dim oneControl As MSForms.Control
    Dim maxHeight As Single
    Dim bottomBorder As Single
    
    bottomBorder = 5: Rem adjust to taste
    
    For Each oneControl In Frame1.Controls
        If maxHeight < oneControl.Top + oneControl.Height Then
            maxHeight = oneControl.Top + oneControl.Height
        End If
    Next oneControl
    
    Frame1.ScrollHeight = maxHeight + (Frame1.Height - Frame1.InsideHeight) + bottomBorder
    Me.ScrollBar1.Max = Frame1.ScrollHeight - Frame1.InsideHeight
End Sub

 

[Awaiz]

Thanks mike..
Will test this and let u knw for any queries..
?

 

[Awaiz]

I don't like the scrollbar that comes with the Frame, so I add a scrollbar control and link it to the frame.

The properties of the Frame that need to be looked at are the .Height, .ScrollHeight and .ScrollTop properties (similar for .Width, .ScrollWidth and .ScrollLeft)

Private Sub ScrollBar1_Change()
    Frame1.ScrollTop = ScrollBar1.Value
End Sub

Private Sub UserForm_Initialize()
    Dim oneControl As MSForms.Control
    Dim maxHeight As Single
    Dim bottomBorder As Single
    
    bottomBorder = 5: Rem adjust to taste
    
    For Each oneControl In Frame1.Controls
        If maxHeight < oneControl.Top + oneControl.Height Then
            maxHeight = oneControl.Top + oneControl.Height
        End If
    Next oneControl
    
    Frame1.ScrollHeight = maxHeight + (Frame1.Height - Frame1.InsideHeight) + bottomBorder
    Me.ScrollBar1.Max = Frame1.ScrollHeight - Frame1.InsideHeight
End Sub

Hi mike,

M able to scroll the frame now thnks...
but the scroll is too small and the slider in the scroll bar is too small..
I would like to scroll a bit quicker and smooth...
thnks

 

[mikerickson]

...
I would like to scroll a bit quicker and smooth...
thnks

So would I, but this is what Excel provides.