Solved: Scroll Bar in User Form

[wnazzaro]

Hello once again.

I've never created a scroll bar on a form, and I need help.

The form has six labels and an OK button. Three of the labels are actually labels, the other three are for holding text. The form resizes depending on how many lines of text there are, and sometimes it gets too big for the screen. I'm trying to use a vertical scroll bar to make viewing the bottom of the form (including the OK button) possible. I just am not sure how to approach this.

I attached the file so you can see what I'm doing. If you click on any cell in Column C on the USA Summary sheet with a value greater than 0, the form is shown. If you click C5, you will see my problem.

Since I am always interested in learning, if you have comments about any of my code, feel free to let me know of a better/more effiicent way.

[wnazzaro]

Here is what I did. I found a form with a scroll bar on vbCity http://www.vbcity.com/forums/topic.asp?tid=13088

Once I figured out what was going on there I added the following to my form code sheet.Public LSV As Single 'Last Value of Scrollbar
Public FrameChildren As New Collection 'All the boxes in the frame
Public NextPos As Integer 'Next starting Location

Private Sub ScrollBar1_Change() 'ScrollBar is Changed
For Each Ctl In FrameChildren
Ctl.Top = Ctl.Top + (LSV - ScrollBar1.Value)
Next Ctl
NextPos = NextPos + (LSV - ScrollBar1.Value)
LSV = ScrollBar1.Value
End Sub

Private Sub btnOK_Click()
Unload DSHE
End Sub

Private Sub UserForm_Initialize()
FrameChildren.Add PosArea
FrameChildren.Add hrsArea
FrameChildren.Add taskArea
FrameChildren.Add btnOK
End Sub
And added to the event code'Send the info to the user.
Load DSHE
With DSHE
'The size of the text areas.
.taskArea.Height = Count * 10
.taskArea.Width = taskLen * 4
.PosArea.Height = Count * 10

'In case the width is too small.
Dim PosWidth As Integer

PosWidth = posLen * 4
If PosWidth < 48 Then PosWidth = 48
.PosArea.Width = PosWidth

.hrsArea.Height = Count * 10

'The location of the form elements.
.btnOK.Top = Count * 10 + 52
.btnOK.Left = (((taskLen * 4) + PosWidth + 156) / 2) - 33
.lblHrs.Left = PosWidth + 58
.hrsArea.Left = PosWidth + 48
.lblTask.Left = PosWidth + (taskLen * 2) + 132 - 15
.taskArea.Left = PosWidth + 132
.lblPos.Left = (PosWidth / 2)
.PosArea.Left = 24

'The size of the form.
If (Count * 10) + 124 > 500 Then
.Height = 500
.ScrollBar1.Visible = True
.ScrollBar1.Left = (taskLen * 4) + PosWidth + 156
.Width = (taskLen * 4) + PosWidth + 174
.ScrollBar1.Height = 475
.ScrollBar1.Min = 0
.ScrollBar1.Max = 600
.ScrollBar1.Value = 0
'MsgBox CStr((Count * 10))
Else
.Height = (Count * 10) + 124
.Width = (taskLen * 4) + PosWidth + 156
.ScrollBars = fmScrollBarsNone
.KeepScrollBarsVisible = fmScrollBarsNone
.ScrollBar1.Max = 0
End If

'The data.
.Caption = theAct
.hrsArea = hrsOutput
.PosArea = posOutput
.taskArea = taskOutput

'Hi Everybody!
Beep
.Show
End With
The only thing I would like to change is to possibly speed up the scroll bar. However, it seems to work now, and I am too busy at work to keep playing with it. I'm going to mark this solved. Any ideas on how to make the scroll bar faster are welcomed. I've removed the attachment, so those that already downloaded it can add the above code and see what I'm doing now. As always, I welcome help on any of the code.

Best regards,
Bill

[wnazzaro]

Ok, I can't seem to find the "Mark Thread as Solved."

Something else I would like to fix: I want to keep the 3 labels at the top of the form from moving. That's something else I will play with later.

[debauch]

wow. thats a lot of code for a scroll bar.
Thanks for the post, I am sure I will find a use for this.

[wnazzaro]

So, I had some more time and mental energy to look at this again. In the VBcity example, I noticed .LargeChange and .SmallChange properties for the scroll bar, and that controls the speed of the bar, now the only thing left is to keep the labels at the top of the form. Wish me luck.

As far as all the code for the scroll bar - here's the condensed version.Public LSV As Single 'Last Value of Scrollbar
Public FrameChildren As New Collection 'All the labels and button on the form.

Private Sub ScrollBar1_Change() 'ScrollBar is Changed
For Each Ctl In FrameChildren
Ctl.Top = Ctl.Top + (LSV - ScrollBar1.Value)
myTop = Ctl.Top
Next Ctl
LSV = ScrollBar1.Value
End Sub

Private Sub btnOK_Click()
Unload DSHE
End Sub

Private Sub UserForm_Initialize()
FrameChildren.Add PosArea
FrameChildren.Add hrsArea
FrameChildren.Add taskArea
FrameChildren.Add btnOK
End Sub
'The size of the form.
If (Count * 10) + 124 > 500 Then
.Height = 500
.ScrollBar1.Visible = True
.ScrollBar1.Left = (taskLen * 4) + PosWidth + 156
.Width = (taskLen * 4) + PosWidth + 174
.ScrollBar1.Height = 475
.ScrollBar1.Min = 0
.ScrollBar1.Max = 600
.ScrollBar1.Value = 0
.ScrollBar1.LargeChange = 20
.ScrollBar1.SmallChange = 10
Else
.Height = (Count * 10) + 124
.Width = (taskLen * 4) + PosWidth + 156
.ScrollBars = fmScrollBarsNone
.KeepScrollBarsVisible = fmScrollBarsNone
.ScrollBar1.Max = 0
End If