License Acceptance Form

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.

Private Sub UserForm_MouseMove(ByVal Button As Integer, _
    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.

Posted in Uncategorized

5 thoughts on “License Acceptance Form

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

  2. 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 m_lngTopIndex As Long
    Private Sub UserForm_Initialize()

        Dim lngIndex As Long
        With ListBox1
            For lngIndex = 1 To 50
                .AddItem lngIndex
            .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

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

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

    Option Explicit

    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

        Frame1.ScrollTop = m_Vertical
        If Frame1.ScrollTop <> m_Vertical Then Exit Do
        m_Vertical = m_Vertical + 1

        m_Vertical = m_Vertical – 1
        Frame1.ScrollTop = 0
        cmdAccept.Enabled = False
    End Sub

  5. Since OpenOffice is open source, you might try looking at the source code.

Posting code? Use <pre> tags for VBA and <code> tags for inline.

Leave a Reply

Your email address will not be published.