Programming Excel with VBA and .NET

Respond to Form Events

Controls respond to user events such as mouse clicks. To add code for these events, simply double-click on the control in the Visual Basic Editor. Visual Basic adds a procedure for the event, as shown in Figure 20-10.

Be sure to name controls before you add event procedures. Event procedures are associated with controls by name--spnDays_Change runs when the spnDays control changes. If you rename the control later, that association is broken and you must rename the event procedure to match.

Figure 20-9. The Stock History form

Figure 20-10. Adding event procedures to a form

The following code shows a simple event procedure that links values of the spnDays and txtDays controls:

    Private Sub spnDays_Change(  )
        txtDays.Value = spnDays.Value
    End Sub
     
    Private Sub txtDays_Change(  )
        ' Ignore error if txtDays isn't between spnDays Min and Max.
        On Error Resume Next
        spnDays.Value = txtDays.Value
    End Sub

Why set the values both places? Doing that ensures that the text box value doesn’t change unexpectedly if you type a value in the text box then click up or down on the spin button. The two procedures don’t cause an infinite loop since the Change event occurs only when a value actually changes; it doesn’t occur if the new setting is equal to the existing setting.

Finally, the On ...