In Naming Userform Controls, we discussed putting a bunch of controls on a userform knowing that most would be used only rarely. For example, putting enough controls on the form to allow for 25 records when 95% of the applications will use less than 5 records.
The downsides to that are 1) increased design time organizing all those controls and 2) setting a hard limit. I’m not so sure the first downside is all that critical. Just suck it up and lay out the controls. The second one always bothers me, though. It only takes one case of 26 records to blow up the form.
I wanted to try to create a form that had only five sets of controls but could handle 25, or so, records.
I start with a custom class module, CRecord, which has Name, Department, and Current properties. Twenty-six instances of this class module are created and stored in a collection. The collection is module-level, so it’s available to all of the procedures in the module.
The scrollbar’s minimum is set at 1 and its maximum is set at 22. When the 22nd record is in the first position, the last record is in the last position. When the scrollbar changes, the records shown are adjusted so that the record whose value is equal to the scrollbar’s value is in the first position.
Private Sub CommandButton1_Click()
Unload Me
End Sub
Private Sub ScrollBar1_Change()
Dim i As Long, j As Long
j = 1
For i = Me.ScrollBar1.Value To Me.ScrollBar1.Value + 4
Me.Controls(“Textbox” & j).Text = mcolRecords(i).Name
Me.Controls(“Combobox” & j).Value = mcolRecords(i).Department
Me.Controls(“Checkbox” & j).Value = mcolRecords(i).Current
j = j + 1
Next i
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.ScrollBar1
.Min = 1
.Max = 22
.SmallChange = 1
.LargeChange = 5
End With
For i = 1 To 5
For j = 0 To 4
Me.Controls(“Combobox” & i).AddItem vaDepts(j)
Next j
Me.Controls(“TextBox” & i).Text = mcolRecords(i).Name
Me.Controls(“Combobox” & i).Value = mcolRecords(i).Department
Me.Controls(“Checkbox” & i).Value = mcolRecords(i).Current
Next i
End Sub
You can download UFScroll.zip
This is how I’ve dealt with too many records with enough room for fewer controls. It’s all just recordkeeping.
Dick
You may find the following a usefull alternative to the problems of Userforms with multiple entries which I use quite succesfully
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.
If a mistake has been made, simply select the listbox entry and a recall/alter button will repopulates the controls with the entry and remove it from the list.
You then have a choice of correcting it and returning it to the list or clearing it as required.
You can further refine this by having Textboxes totalling the number of items, value etc for extra checks before final posting.
When complete, the retained listbox entries can then be dropped onto a worksheet as required.
Did you hook the wheel on your mouse so that it actually scrolls? Or do you know a better way of getting the wheel to work?
Charles
I use a technique similar to what Andrew suggests.
In a dialog box, I have a multicolumn list box tied to a hidden worksheet, with Add/Edit/Delete buttons below the listbox.
The Add/Edit buttons “enlarge” the dialog box vertically revealing the text boxes to the entries in the listbox along with Save/cancel buttons that will restore the dialog box back to it’s original size thus hiding the text boxes (and in turn disabling them).
Now that I think of it, I’ve used the multiple column listbox approach an awful lot over the past couple of years. A lot easier to set up than the pseudo-scrollable controls, the code is more readily applied to other projects, and the form is almost as nice to use. It’s connected to a VB array, and the user can easily edit, add, or remove items, sort or filter by any column, what have you, depending on how we choose to embellish the form.
I use a read-only listview control at the top of the form.
When a row is selected, a group of controls beneath becomes activated allowing edit of a single record.
Using change events, the data is pushed back into the form’s data collection and listview / subitems.
-Rob
Rob: I put a listview on a userform to check it out. Device I/O errors abound, even after I removed the userform and closed the file. Yikes.
Which listview control do you use?
I use the ListView control: ‘Microsoft ListView Control 6.0 (SP4)
After I use it, I get an extra tick in Tools | References: ‘Microsoft Windows Common Controls 6.0 (SP6)
Which is C:WindowsSystem32Mscomctl.ocx
I beleive it’s installed with the Visual Basic 6 Runtime, which comes standard with Windows.
I’ve yet to see a computer without it.
The Query Editor add-in I wrote some time ago uses it.
I can do funky things like column header clicking for row sorting, resize columns, tick box on rows, etc..
-Rob
I’ve been using the listview Rob describes in a VB program, for some of the reasons Rob describes. It’s definitely a different control than you get by just clicking Listview in Excel’s additional controls. When I added that one to a form and tried to run it, I got a message about “unsafe controls,” so based on Dick’s experience, I cancelled it and deleted the workbook.
Mine says ‘Microsoft ListView Control 6.0 (SP6) and whenever I add it to a form, I get “Device I/O Error” when I close the form. When the ListView contains data, no error.
Dick:
A strange error indeed. I’ve never encountered it.
What version of Excel are you running? Could you reinstall the service pack?
Running Google Desktop? (unlikely that it causes the error, though a comment elsewhere on this site suggests it’s a problem)
Using code signing?
-Rob
humm, I get the “device I/O Error” message sometimes, I wonder if it has anything to do with what else you have installed on your PC? – VB.net?/Multi office?
I don’t think i have found a way to overcome it short of quitting Excel and restating it, they seem to be “random”?