WithEvents Revisited

Creating a SpinButtons Class

By Ken Getz

In the November 1999 issue of MOD, I wrote an article concerning the use of WithEvents to create a Text box/list box combination control that allowed you to search for text in the list box while typing into the text box. The point of the article (besides providing a useful user interface component) was to demonstrate how you can grab events from anyplace, and react to them in your own code. This makes it possible for you to separate UI objects and their event code - basically, providing a way to create your own groups of controls, much as you would if you were to create your own ActiveX control in Visual Basic.

I heard from a number of readers excited by the possibilities presented in that article. Because of the unusual amount of response, I decided to write another article using the same techniques. In this case, I'll show how to create spin buttons. Spin buttons provide one way to control the values your users input, and make it simpler for them to change numeric and date values. The basic concept should be familiar: The "spin button concept" includes two buttons, one pointing up and the other pointing down, normally attached to a text box. Pressing the up button increments the value in the text box; pressing the down button decrements the value. Although you could theoretically use this mechanism for text values, it's not often used that way. For the most part, spin buttons are restricted to date and integer entry.

Why bother creating your own spin buttons when there are controls that perform this function? First, most spin-button controls are somewhat unattractive, and you generally can't do much to change that. If you create your own, you control how they look. Also, if you're working in Access or VB, you would otherwise need to use an ActiveX control to perform this task, and ActiveX controls exact a somewhat hefty overhead in terms of resources, speed, and application size. Because spin buttons are so easy to create using built-in controls, it seems like overkill to use an ActiveX control for this. In addition, because you're writing the code, you can control the behavior. For example, the spin buttons you'll create here allow you to limit the range and, possibly, cycle back to the beginning or end once you reach an endpoint.

Reviewing the Concepts

When you place a control on a form, and then write code in reaction to events raised by that control, you're taking advantage of some "magic" provided by the host application. That is, how does the class you're writing code in know about events raised by the class provided in the control? (All controls started life as a class module.) The fact is, under the covers, when you place a control on a form, that form adds a WithEvents connection between the form and the control for you. When you place a command button named cmdTest on a form, internally, the host application effectively adds this line of code to the form's class module:

Private WithEvents cmdTest As 
      CommandButton

This adds cmdTest to the left-hand drop-down list at the top of the code editor window, showing all the objects for which you can write event procedures. It also adds the list of events available for this object to the right-hand drop-down list.

To react to events raised by any object besides controls placed on a form (or report, if you're programming in Access), you'll need to provide that linkage yourself, and you'll need to make your variable "point" to the control or other object that's raising the events. In this example, and the one provided in the earlier column, you'll see a technique for reacting to events of controls on forms outside of the form's modules, thereby making it possible to simulate many of the behaviors of ActiveX controls without having to actually create a new control.

Introducing the SpinButtons Class

I've provided a class, SpinButtons, that makes using the two buttons/text box combination simple. Once you create an instance of the class and set a few properties, it's ready to go. To see the SpinButtons class in action, see frmSpinTest, shown in FIGURE 1. (SpinButtons.cls, and all other source and databases referred to in this article, are available for download; see end of article for details.)

FIGURE 1: The SpinButtons class makes it easy to add spin-button functionality to any form.

This form uses all the properties of the SpinButtons class. FIGURE 2 lists all the properties and methods of the class.

Member	Description
AllowKeys	Boolean property. If True, allows the plus (+)and minus (-) keys to increment and decrement the value of the text box, respectively. If False, you must use the up and down buttons to increment the value of the text box.
AllowWrap	Boolean property. If True, causes the changing value to reset to the lowest value when spinning up, and the value becomes the Max property value; or it will reset to the highest value when spinning down, and the value becomes the Min property value. If either of the Min and Max properties haven't been specified, AllowWrap has no effect - wrapping will not occur.
Control	TextBox property. Reference to the text box to be used to display the changing value. This property is not optional.
Delay	Long property. If set to a non-zero value, forces the spinning to delay the specified number of milliseconds between each value. Because Access, by default, can cause the values to go by too fast, you may want to set this value to 100 or more, to slow down the changes.
DownButton	CommandButton property. Reference to the command button that causes the value of the text box to decrement. This property is optional, but you won't be able to use the mouse to decrement the value in the text box unless you specify a command button for this property.
Interval	Long property. Sets or retrieves the amount to increment and decrement on each click of the up and down buttons. The default value is 1.
Max	Variant property. If AllowWrapping is True, specifies the maximum value the text box can reach before wrapping back to the Min value. Unless both Min and Max have been set and AllowWrap is True, this property has no effect.
Min	Variant property. If AllowWrapping is True, specifies the minimum value the text box can reach before wrapping back to the Max value. Unless both Min and Max have been set, and AllowWrap is True, this property has no effect.
UpButton	CommandButton property. Reference to the command button that causes the value of the text box to increment. This property is optional, but you won't be able to use the mouse to increment the value in the text box unless you specify a command button for this property.
Value	Variant property. Sets and retrieves the value of the text box associated with the SpinButtons class. Although you could retrieve the value yourself from the form, this property helps encapsulate the class so you needn't worry about the name of the control if you don't want to.
Init	Method. Rather than setting the properties individually, you can call the Init method, passing in the properties you want to set. This method accepts as its parameters each of the possible class properties. For more information, see the Parameter Tip provided by VBA, or peruse the source code.
SpinDown	Event. Raised when the value of the text box is about to be decremented. The event passes to you the current value and a Boolean Cancel parameter. Set the parameter to True if you want to cancel the change. See the sample form for an example.
SpinUp	Event. Raised when the value of the text box is about to be incremented. The event passes to you the current value and a Boolean Cancel parameter. Set the parameter to True if you want to cancel the change. See the sample form for an example.
Change	Event. Raised when the value of the text box has been changed. If you don't care whether the value is going up or down, and you care only that the value has changed, use this event. By the time this event is raised, the value has already changed, and it's too late to cancel the change. The event passes to you the current value of the text box, which has already been changed.

FIGURE 2: Properties and methods of the SpinButtons class.

Using SpinButtons

To use the SpinButtons class, you'll need to import the class module into your project. In addition, you'll need a form that contains at least the controls necessary for the spin buttons: a text box and two command buttons. (You can copy and paste the control group I've created on frmSpinTest in the sample Access 2000 project, if you like. If you do that, you'll probably want to adjust the size of the text box and its font.) If you create the buttons yourself in Access, you'll want to set their AutoRepeat property to Yes so that holding the buttons down will repeat the change to the value.

Although you can use this technique (using WithEvents to trap events raised by other classes) in an environment that hosts Microsoft Forms (Excel, Word, PowerPoint, and so on), you probably won't want to for this example. Because there's no way to cause command buttons to auto-repeat in that environment, there's no way to trigger the event repeatedly. This example works best in Microsoft Access, and it works in either Access 97 or Access 2000. (If you want to use this code in Access 97, however, you'll need to remove all the code dealing with raising events from the SpinButtons class; that is, remove the Event declarations, and the calls to the RaiseEvent method.)

Once you have the form set up, you'll need to write code to instantiate the SpinButtons object and hook it up with the form. To do that, add code such as that shown in FIGURE 3 to your form. (Of course, replace the sample control names with your control names, if you've created new ones.)

Private sb 
      As SpinButtons

Private Sub Form_Load()

   
      Set sb = New SpinButtons

   ' Set the 
      required properties individually. 

   
      Set sb.Control = Me.txtValue

   
      Set sb.DownButton = Me.cmdDown

   
      Set sb.UpButton = Me.cmdUp

End Sub

Private Sub Form_Unload(Cancel As Integer)

   
      Set sb = Nothing

End Sub

FIGURE 3: Code to instantiate the SpinButtons object and hook it up with the form.

If you like, you can set all those properties at once using the Init method:

sb.Init Control:=Me.txtValue, 
      UpButton:=Me.cmdUp, _

  DownButton:=Me.cmdDown

How Does It Work?

Just like the class presented in the earlier article, the SpinButtons class does its work by grabbing WithEvents connections to controls on your form. Rather than forcing you to write event procedure code for each of the command buttons, each time you want to use the SpinButtons class, you simply specify the control references, and the SpinButtons class does the rest of the work. Using this technique, you can have as many instances of the SpinButtons class on your form as you need. The class contains declarations for its internal representations for the three controls:

Private WithEvents mtxt As 
      TextBox

Private WithEvents mcmdUp As 
      CommandButton

Private WithEvents mcmdDown As 
      CommandButton

You need to link those variables with the actual controls. You can either set the Control, UpButton, and DownButton properties individually, or you can call the Init method. However you set the Control, UpButton, and DownButton properties of the form, the SpinButtons class stores away the reference in variables declared with the WithEvents keyword, and sets the appropriate event properties to be [Event Procedure]. For example, FIGURE 4 shows the Property Set procedure for the UpButton property.

Public Property Set 
      UpButton(cmd As CommandButton) 

   On 
      Error GoTo 
      HandleErrors

   
      Set mcmdUp = cmd

  mcmdUp.OnClick = "[Event 
      Procedure]" 

  mcmdUp.OnKeyPress = "[Event 
      Procedure]" 

ExitHere: 

   
      Exit Property

HandleErrors: 

   
      Select Case Err.Number

     Case Else

      Err.Raise Err.Number, 
      _

        "SpinButtons.UpButton", 
      Err.Description

   
      End Select

   
      Resume ExitHere

End Property

FIGURE 4: The Property Set procedure for the UpButton property.

Once you've set the control properties, the SpinButtons class can react to the Click and KeyPress events of the command buttons and can change the value of the associated text box. (If you remember from the previous article - or if you weren't there - Access simply will not raise any of an object's events unless it finds the " [Event Procedure]" text in the associated event property. This optimization makes forms run faster than they otherwise would, in Access. If there's no need to raise an event, they simply don't. This means that your code must always set the event property correctly, as this class does. Access is the only VBA host that exhibits this behavior/oddity, as far as I know.)

Reacting to Host Events

Once you've set up the WithEvents connection, you can react to events raised by the controls whose activities are being "managed" by the SpinButtons object. For example, when you click on the up button on your form, the button raises its Click event. The code in the SpinButtons class reacts to that event (because you've told it to do so, using the WithEvents keyword and associating the mcmdUp variable with the actual control), running the code in FIGURE 5.

Private Sub mcmdUp_Click()

   On 
      Error GoTo 
      HandleErrors

   
      Call SpinUp

ExitHere: 

   
      Exit Sub

HandleErrors: 

   
      Select Case Err.Number

     Case Else

      Err.Raise Err.Number, 
      _

        "SpinButtons.mcmdUp_Click", 
      Err.Description

   
      End Select

   
      Resume ExitHere

End Sub

FIGURE 5: When you click on the up button on your form, the button raises its Click event, and the code in the SpinButtons class reacts to that event.

The SpinUp procedure calls the internal Spin procedure, telling the code to increment the value in the associated text box. If you're interested, you can dig into the Spin procedure in the sample code - how it works isn't the issue here. (The Spin procedure does all sorts of ugly stuff, like figuring out the kind of data in the text box, deciding what to do based on the data type, and taking into account whether it should bump the value up, or wrap back to an endpoint value, based on property values you've set.)

Raising Events

To make the group of controls act more like a real ActiveX control, the SpinButtons class raises three events as you work with it: SpinUp, SpinDown, and Change. The SpinButtons class raises the SpinUp and SpinDown events before it changes the text in the text box control and it allows you to cancel the change. It raises the Change event after changing the value (that is, after it's too late to allow you to cancel the event).

To raise events from a class, you must first declare the events, like this:

Public Event Change(Value As 
      Variant)

Public Event SpinUp(Value As 
      Variant, Cancel As 
      Boolean)

Public Event SpinDown(Value As 
      Variant, Cancel As 
      Boolean)

In this example, the Change event has been declared so it sends the current value of its associated control to the listener. The SpinUp and SpinDown events pass the current value, and a parameter allowing you to cancel the event. If you place True into the Cancel parameter from within the event procedures for the SpinUp or SpinDown event, the change simply won't take place.

Then, at the moment you want to raise the events, use the RaiseEvent statement. For example, the SpinButtons class uses the following code fragment, from its private Spin procedure, to raise the SpinUp and SpinDown events:

Select Case Sgn(intInterval) 

   
      Case 1

     RaiseEvent SpinUp(varData, fCancel) 

   
      Case -1

     RaiseEvent SpinDown(varData, fCancel) 

   
      Case 0

     ' Do nothing. 

End Select

If fCancel 
      Then

   
      GoTo ExitHere

End If

This code first determines the sign of the interval (+1 or -1), and raises the SpinUp or SpinDown event accordingly. If it finds that the value in fCancel, after raising the event, is True, the code jumps out without changing the value in the associated control.

Using WithEvents provides a synchronous connection between the event "raiser" and all the event "listeners." That is, when you run the code shown in the previous fragment, the calling code effectively stops dead waiting for a response from every class that happens to be listening for the event; the code in the SpinButtons class cannot proceed until all event procedures hooked up with the WithEvents keyword finish responding to the event. In that way, you're guaranteed that the code looking at the return value in fCancel won't run until the event procedure in the form's module has finished running.

This means, of course, that you shouldn't put any time-consuming statements into your event procedures, run in reaction to events raised by the SpinButtons (or any other) class. Imagine what would happen if you had time-sensitive code in the SpinButtons class, but some event procedure, reacting to an event raised by the SpinButtons class, put up a message box during the event procedure. SpinButtons would have to wait until that message box was dismissed before it could proceed. It's important that you know about this behavior, so you know what's going on when things come to a halt in any class that raises events.

Reacting to SpinButtons Events

In your form, using the SpinButtons class allows you to add event procedures for any of the SpinUp, SpinDown, and Change events. For example, the sample form reacts to the SpinDown event and cancels the event if the value of the text box control is less than or equal to 10. Normally, you'd use the Min property of the SpinButtons class to effect the same behavior:

' Sample SpinDown event 
      procedure. 

' You generally won't 
      use this code. 

Private Sub sb_SpinDown( _

  Value As Variant, Cancel As Boolean)

   If 
      Value <= 10 Then

    Cancel = True

   
      End If

End Sub

Conclusion

It's easy and useful to separate the user interface and its behavior using WithEvents. This allows you to create class modules that you can easily move from one project to another, providing the functionality you need without having to add more than one or two lines of code to each form that needs to take advantage of the behavior. This gives you the benefits of creating your own ActiveX controls, and it's simple to implement. Access 2000 Developer's Handbook, Desktop Edition (Volume I), from which this code was excerpted, has several more examples using this technique - once I got going, I simply couldn't stop. If you want to create a form with two list boxes and four buttons, allowing you to move items back and forth between the two list boxes; if you want to grab onto the Resize event of a form and scale all the controls on the form to match the new size of the form; or if you want to allow users to select the starting location for a sheet of printed labels, this technique makes it simple.

As you create forms, think about places where you place all the code you need right into the form itself, and how much effort it would be to reuse that code elsewhere. If you need to reuse the behavior on a different form, you'll want to consider extracting all the code, hooking up the appropriate events, and creating a class that separates the user interface from the code. It will make your development life easier, faster, and more fun. How many promises like that do you get when designing forms?

This article, and its code, was excerpted and modified from a section of Access 2000 Developer's Handbook, Desktop Edition (Volume I) [SYBEX, 1999], by Ken Getz, Paul Litwin, and Mike Gilbert, with the permission of the publisher. For more information, visit http://www.developershandbook.com/, or http://www.sybex.com/.

The files referenced in this article are available for download.

Ken Getz, a senior consultant with MCW Technologies, splits his time between programming, writing, and training. Ken is co-author of several books for developers, including Access 2000 Developer's Handbook [SYBEX, 1999] (with Paul Litwin and Mike Gilbert) and VBA Developer's Handbook [SYBEX, 1997] (with Mike Gilbert). He also co-wrote the training materials, travels around the United States teaching, and recorded training videos on Access 97 and VB6 for Application Developers Training Company. Ken is currently at work on Visual Basic Language Developer's Handbook from SYBEX. In addition, Ken is a Contributing Editor for Microsoft Office & Visual Basic for Applications Developer magazine.