In my world, when a form has a spinbutton, it’s got a textbox. Together they make a little thing I like to call a Number Selector. This handy control lets you pick digits by clicking the spinbutton, typing in the textbox, and even using the up and down keys. Whichever you do, the code keeps the textbox and spinbutton in sync. If you try to type anything non-numeric in the textbox it’s ignored. If you type a number outside the Min or Max of the spinbutton, it reverts to the minimum or the maximum. I find this all quite cool, and fun to code.

A Basic Number Selector

There’s lots of ways to combine a spinbutton and textbox into a number selector. Here’s a basic version of how I code it:

Spinning the button or typing in the textbox selects a row between 1 and the sheet’s last row:

A Number Selector Class

Continuing the recent theme of of userform control classes, this post is about a number selector class. “Classifying” the control (sorry) eliminates a ton of duplicated code in your form, similar to what was done in the UserForm Event Handler Class – Multiple Controls post.

Besides the basic features discussed above, my number selector has:

• Accelerator keys. Clicking the Shift, Ctrl and Alt keys increases the spinbutton’s SmallChange property. The accelators are cumulative and don’t care which of these keys were pressed, just how many. For example, if you set accelerators of 2, 3 and 5 and press Shift and Ctrl (or Alt and Ctrl), the increment is increased by a factor of six.
• A Change event. You capture this event in the calling form, just like you would for Worksheet_Change and other built-in events. One limitation is these only fire for the first class instance in an array or collection of instances. Depending on what you’re doing that might not matter anyways, like in the form below.

Here’s the code for the class. Hopefully the comments get at the tricky stuff. I’ll say more about it below:

Because this class has quite a few properties, the logic for the order in which they must be assigned is a bit clunky. For instance, if you try to assign the Value, Min or Max before you’ve passed the controls to the class, it won’t work. So I created an Execute method – a Sub within the query – that assigns the values from the class-level private variables to the spinbutton and textbox.

There’s also some Err.Raise code try to deal with bad initialization. But I don’t try to handle form-specific issues – I want it to be portable – so if you assign a min of zero and use it to pick row numbers, you’ll get a runtime error.

As mentioned in the comments, the Increment property has three accelerator parameters. I’d never used parameters with a property before. You have to list the “real” one last, and the preceding ones can be optional. If you have a matching Get property, it must include the same parameters, or you’ll get a compile error.

I also added some KeyDown logic for the textbox part of the control. Normally, if you’re in a textbox the down arrow takes you to the next control. I wanted it to act the same as the up and down keys do when the spinbutton is active, so I cancel the KeyDown event if the down or up arrows are pressed, and instead add or subtract the correct amount to the value in the textbox.

A Form With Three Class Instances

The UserForm for this utilization of the class has three number selectors. The first selects rows, the next selects columns. The third increments the seconds from midnight last night. Each number selector is framed by a … frame. They’re named grpRowSelector, grpColSelector and grpSecondsFromNow respectively (I think of them as Groups, hence the “grp”). It doesn’t matter what the spinbutton and textbox are called, as the class just checks for those controls inside the frame, which is passed to the class in UserForm_Activate.

You can see what I mean about simple code in the form. There’s just the initialization of the classes and the Change event for each one> Note that the this number selector has accelerators of 60, 60 and 24, so you can increment by seconds, minutes, hours or days:

Private WithEvents cRowSelector As clsNumberSelector
Private WithEvents cColumnSelector As clsNumberSelector
Private WithEvents cSecondsFromNow As clsNumberSelector

Private Sub UserForm_Activate()
Dim MaxSeconds As Long

Set cRowSelector = New clsNumberSelector
With cRowSelector
.Group = Me.grpRowSelector
.Min = 1
.Max = ActiveSheet.Rows.Count
.Value = 1
‘properties can have parameters
.Increment(acc1:=5, acc2:=10, acc3:=100) = 1
‘Needed to confirm that properties entered correctly
.Execute
End With

‘see comments for class instance above
Set cColumnSelector = New clsNumberSelector
With cColumnSelector
.Group = Me.grpColumnSelector
.Min = 1
.Max = ActiveSheet.Columns.Count
.Value = 1
.Increment(2, 5, 10) = 1
.Execute
End With

‘see comments for class instance above
Set cSecondsFromNow = New clsNumberSelector
With cSecondsFromNow
MaxSeconds = (10# * 24 * 60 * 60) ‘# to make it a Long
.Group = Me.grpSecondsFromNow
.Min = -MaxSeconds
.Max = MaxSeconds
.Value = 0
.Increment(60, 60, 24) = 1
.Execute
End With
End Sub

‘custom events raised in clsNumberSelector
Private Sub cRowSelector_NumberSelectorChanged()
ActiveSheet.Rows(cRowSelector.Value).Select
Me.txtIncrementValue = cRowSelector.Increment
End Sub

Private Sub cColumnSelector_NumberSelectorChanged()
ActiveSheet.Columns(cColumnSelector.Value).Select
Me.txtIncrementValue = cColumnSelector.Increment
End Sub

Private Sub cSecondsFromNow_NumberSelectorChanged()
Me.txtDateAndTime.Value = Format(Date + cSecondsFromNow.Value / (24# * 60 * 60), “yyyy-mm-dd hh:mm:ss”)
Me.txtIncrementValue = cSecondsFromNow.Increment
End Sub

Download

What you’d like to try this code, but don’t feel like copying and pasting and dragging controls around? I understand. Check this out.

P.S. If you want to read a great post about class properties, you can do no better than this one by Tushar Mehta on DDOE.