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 UFScroll.zip.
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
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
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” …
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)
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 ?
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
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?
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.
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.
Dick, any good websites on getting keyboard shortcuts to work? I’ve tried this a few times using OnKey but never had success.
Doh!! long day. I meant using KeyPress. like:
Private Sub UserForm_KeyPress(ByVal KeyAscii As MSForms.ReturnInteger)
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.
Great Post – this sparked an idea that has saved tons of time. Been reading for awhile – thanks for the blog!
Dick,
Thats pretty much what i did here (with add and remove, and then a send to DB button too)!!!!
http://img151.imageshack.us/my.php?image=trsufxc4.jpg