Yesterday I installed OpenOffice on a couple of laptops. In order to accept the license agreement, I had to scroll down to the bottom of the textbox. The “Accept” button was disabled until I’d reached the bottom. I don’t know why they care if I read their EULA. I could go on and on about how EULAs are too complicated and probably unenforceable when you really need them, but I won’t.
Instead, I was interested in replicating the form in VBA. (Perhaps a violation of the EULA? How delicious.) I started with a Textbox control on a userform. I set Multiline to TRUE, WordWrap to TRUE and ScrollBars to fmScrollBarsVertical. It looked like the OpenOffice form, but I couldn’t figure out a way to tell where the scroll bar was in the Textbox. That is, I didn’t know when the scrollbar was all the way to the bottom.
Next I went to the Listbox. I knew Listboxes had a TopIndex property that shows the top-most item that’s visible. With the size of my Listbox and 100 lines of an EULA, I hardcoded that when TopIndex is 78, the user has scrolled all the way down.
Knowing that TopIndex is 78 and doing something about it are two different things. I discovered, or rediscovered, that clicking on a scroll bar does not fire the Click event of a Listbox. Nor does it fire the MouseUp event. Apparently, the scrollbar of a control is in the domain of the userform rather than the control. I settled on the MouseMove event of the Userform object. It’s not perfect, but it’s close.
ByVal Shift As Integer, ByVal X As Single, ByVal Y As Single)
If Me.ListBox1.TopIndex = 78 Then
Me.CommandButton1.Enabled = True
End If
End Sub
One nice thing is that I don’t have to manage the Enabled state of the commandbutton. Once the user hits the bottom, it’s enabled and that’s that. Scrolling back up doesn’t negate the fact the user was at the bottom and presumably read the agreement (yeah, right!). One obvious downside is that Listboxes aren’t really meant to hold text. Editing the text would be a huge chore because one wayward line break affects all that come after it.
I would be nice if ’78’ wasn’t hard coded. I’m sure I could figure out the appropriate TopIndex based on the Height and ListCount properties. I also may need to know the Font property. As you can see, my interest dried up before I got to that point.
As usual, your improvement are welcome and appreciated.
Dick, I spent way too much time trying to do this last night with a frame and its scroll event. It really seemed like it should work, there’s quite a bit of info with that event, but none that I could figure that would let me know when I’d hit bottom.
Get the listbox to tell you the information you need.
Force the last item in the listbox to be the topindex. Then when you check the property it will return the actual topindex needed for the test.
Private Sub UserForm_Initialize()
Dim lngIndex As Long
With ListBox1
For lngIndex = 1 To 50
.AddItem lngIndex
Next
.TopIndex = .ListCount – 1
m_lngTopIndex = .TopIndex
.TopIndex = 0
End With
CommandButton1.Enabled = False
End Sub
Private Sub UserForm_MouseMove(ByVal Button As Integer, _
ByVal Shift As Integer, ByVal X As Single, ByVal Y As Single)
If Me.ListBox1.TopIndex = m_lngTopIndex Then
Me.CommandButton1.Enabled = True
End If
End Sub
Andy –
Clever. That’s conceptually related to your textbox to measure the height of a chart’s text element by moving it down to the bottom of the chart, then subtracting its .Top property from the chart area’s .Height property.
Doug
Using a Frame would work
Assumes Frame1, commandbutton Named cmdAccept
Frame settings;
ScrollBars = 2 – frmScrollBarsVertical
ScrollHeight = set to show scroll eg larger then Height of Frame (mine @ 500)
Dim m_Vertical As Single
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)
If ActualDy + Frame1.ScrollTop = m_Vertical Then cmdAccept.Enabled = True
End Sub
Private Sub UserForm_Initialize()
m_Vertical = 0
Do
Frame1.ScrollTop = m_Vertical
If Frame1.ScrollTop <> m_Vertical Then Exit Do
m_Vertical = m_Vertical + 1
Loop
m_Vertical = m_Vertical – 1
Frame1.ScrollTop = 0
cmdAccept.Enabled = False
End Sub
Since OpenOffice is open source, you might try looking at the source code.