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
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.