Pseudo Scroll 2

Andrew comments:

A Userform with one set of controls, and in addition a multiple column wide list box.
As each set of complete entries is made to the controls, you then have an add button which
posts the entry as a 1 liner to the list box.
As the entry is posted to the listbox, the controls are then cleared for the next entry.

Good idea.

What do you think of using AfterUpdate rather than an explicit Add or Save button? I hate to add unnecessary keystrokes, but live changes without an undo might be dangerous.

Update You can download

Private mcolRecords As Collection
Private mbDisableEvents As Boolean
Private Sub CheckBox1_AfterUpdate()
    If Not mbDisableEvents Then
        mbDisableEvents = True
        With Me.ListBox1
            .Column(2, .ListIndex) = CStr(CBool(Me.CheckBox1.Value))
            mcolRecords(.ListIndex + 1).Current = Me.CheckBox1.Value
        End With
        mbDisableEvents = False
    End If
End Sub
Private Sub cmdFirst_Click()
    Me.ListBox1.ListIndex = 0
End Sub
Private Sub cmdLast_Click()
    With Me.ListBox1
        .ListIndex = .ListCount – 1
    End With
End Sub
Private Sub cmdNext_Click()
    With Me.ListBox1
        .ListIndex = .ListIndex + 1
    End With
End Sub
Private Sub cmdPrev_Click()
    With Me.ListBox1
        .ListIndex = .ListIndex – 1
    End With
End Sub
Private Sub ComboBox1_AfterUpdate()
    If Not mbDisableEvents Then
        mbDisableEvents = True
        With Me.ListBox1
            .Column(1, .ListIndex) = Me.ComboBox1.Value
            mcolRecords(.ListIndex + 1).Department = Me.ComboBox1.Value
        End With
        mbDisableEvents = False
    End If
End Sub
Private Sub CommandButton1_Click()
    Unload Me
End Sub
Private Sub ListBox1_Change()
    Dim lCurr As Long
    If Not mbDisableEvents Then
        lCurr = Me.ListBox1.ListIndex + 1
        Me.TextBox1.Text = mcolRecords(lCurr).Name
        Me.ComboBox1.Value = mcolRecords(lCurr).Department
        Me.CheckBox1.Value = mcolRecords(lCurr).Current
    End If
    Me.cmdFirst.Enabled = True
    Me.cmdPrev.Enabled = True
    Me.cmdNext.Enabled = True
    Me.cmdLast.Enabled = True
    Select Case Me.ListBox1.ListIndex
        Case -1
            Me.cmdPrev.Enabled = False
        Case 0
            Me.cmdPrev.Enabled = False
            Me.cmdFirst.Enabled = False
        Case Me.ListBox1.ListCount – 1
            Me.cmdNext.Enabled = False
            Me.cmdLast.Enabled = False
    End Select
End Sub
Private Sub TextBox1_AfterUpdate()
    If Not mbDisableEvents Then
        mbDisableEvents = True
        With Me.ListBox1
            .Column(0, .ListIndex) = Me.TextBox1.Text
            mcolRecords(.ListIndex + 1).Name = Me.TextBox1.Text
        End With
        mbDisableEvents = False
    End If
End Sub
Private Sub UserForm_Initialize()
    Dim clsRecord As CRecord
    Dim vaNames As Variant, vaDepts As Variant
    Dim i As Long, j As Long
    vaNames = Array(“Alpha”, “Bravo”, “Charlie”, “Delta”, “Echo”, “Foxtrot”, _
        “Golf”, “Hotel”, “India”, “Juliet”, “Kilo”, “Lima”, “Mike”, _
        “November”, “Oscar”, “Papa”, “Quebec”, “Romeo”, “Sierra”, “Tango”, _
        “Uniform”, “Victor”, “Whiskey”, “Xray”, “Yankee”, “Zulu”)
    vaDepts = Array(“Accounting”, “Marketing”, “Production”, “Information Technology”, “Shipping”)
    Set mcolRecords = New Collection
    For i = 1 To 26
        Set clsRecord = New CRecord
        clsRecord.Name = vaNames(i – 1)
        clsRecord.Department = vaDepts((i Mod 5))
        clsRecord.Current = (i Mod 2) = 1
        mcolRecords.Add clsRecord, CStr(i)
    Next i
    With Me.ListBox1
        For i = 1 To mcolRecords.Count
            .AddItem mcolRecords(i).Name
            .Column(1, .ListCount – 1) = mcolRecords(i).Department
            .Column(2, .ListCount – 1) = CStr(CBool(mcolRecords(i).Current))
        Next i
    End With
    For j = 0 To 4
        Me.ComboBox1.AddItem vaDepts(j)
    Next j
    Me.ListBox1.ListIndex = 0
End Sub
Posted in Uncategorized

11 thoughts on “Pseudo Scroll 2

  1. It must be obvious to the user that entries will be stored
    – immediately, or
    – when leaving the record (with or without a “save changes ?”), or
    – clicking Save, or
    – on closing of the session/form (with or without a “save changes ?”).
    choice depending on what we are dealing with, how much items and actions are connected to eachother.
    Undo is always good, users feel more confident using the app then. Can be a pain to program though. My worst moments start like “This is really good, Harald. All we need now is print functionality and undos” …

  2. Version 2.0 needs:

    Add button
    Delete button
    Sort by clicking on column header labels (make them look like buttons, sort ascending on first click, descending on second)
    Up/Down buttons for manual sorting (useful for some uses)
    Better alignment of header labels and controls with listbox columns

    For saving, since it writes to an array, you can give the user two buttons:

    Cancel: close without saving to sheet
    Apply and Close: saves array to sheet and closes form

    Optional third button:

    Apply: Save array to sheet now, leave form open for further revision
    (these changes are not undo-able using Cancel)

  3. Dick

    Whilst I note your suggestion, looking at the current screenshot, the current entry is duplicated in both the Controls and the Listbox, which is not as I had intended. It seems to me from a users point of view that if it has been added to the list, it shouldn’t be shown in the control entry section and vice versa.

    An Autoupdate function on the Chckbox is not feasible given that no action may be needed on a new entry, and an auto posting on either name and department if they both needed changing at the same time, could lead to an autoposting of an entry you didn’t want !

    I must admit to being slightly confused by the need for last, next, prev and end controls when a simple click on the offending entry could remove it from the list and bring it back into the controls for editing, re-enter or delete/clear with appropriate buttons.

    It really depends on what you are trying to achieve at the end of the day, but with the method shown, at what stage are all the entries cleared ready for the next entry.

    PS Excel 2000 does not support ‘Dim clsRecord As CRecord’ which version does ?

  4. Hi Andrew,

    CRecord is a class module which you need to create.
    This should be enough to get the code working.

    Public Name as Variant
    Public Department as Variant
    Public Current as Boolean

  5. My needs for this form do not include adding or deleting records, but I see the benefit of having that should the application dictate. If you want to add or delete records in my app, you have to go back to Quickbooks to do it. (Not that this form is used in my app, it’s just a proof of concept for myself).

    For saving and applying, I think I will go with a form-wide Save button and Apply button. So records are changed as the user types, but they are not saved until the user Saves or Applies. In code, I’m thinking I’ll create two collections instead of one: a working collection and a permanent collection. The working collection will change as the form changes. The permanent collection will be passed out of the form and back to the business logic layer. The working collection will overwrite the permanent collection whenever the user clicks Save or Apply. If Cancel is clicked, the original, unchanged permanent collection will be passed out. Am I missing any traps here?

    I must admit to being slightly confused by the need for last, next, prev and end controls…

    Now Andrew, I know you’ve visited this blog a time or two. I would never make a userform that wasn’t keyboard friendly. If only to avoid the public scrutiny. I’ve done some data entry in my day and I always appreciate a keyboard shortcut.

    Re CRecord: Andy is right. I meant to post a link to the newly updated Excel file, which I now have done.

  6. Gentlemen – Apologies for overlooking Class Modules which I have studiously tried to avoid getting involved with and together with the mouse didn’t even exist in Olivetti Scientific basic which I cut my teeth on in the early seventies.

    With regards to my suggestions for the form, I must admit that I was directing these more to the ‘Naming Userform Controls’ issue than the ‘Pseudo Scroll’ posting.

    I will resolve to ‘must try harder’ in future. Christmas Greetings to all.

  7. Dick, any good websites on getting keyboard shortcuts to work? I’ve tried this a few times using OnKey but never had success.

  8. Doh!! long day. I meant using KeyPress. like:
    Private Sub UserForm_KeyPress(ByVal KeyAscii As MSForms.ReturnInteger)

  9. Charles: I half-wrote a ten-key program in Excel once that used KeyPress. It was tedious and cumbersome, which is probably why I never finished it. Maybe I’ll dig it out this weekend for a post. If you have a specific problem, shoot me an email so I can be sure to incorporate it.

  10. Great Post – this sparked an idea that has saved tons of time. Been reading for awhile – thanks for the blog!

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

Leave a Reply

Your email address will not be published.