The Add ButtonClick event

The event handler (Listing 10.5) works by checking to see if the value in the textbox called RepBox is empty. If it is, then the message "Enter a name for the rep to b e added" is delivered, followed by setting the focus to the RepBox object by using the statement .SetFocus. This will ensure that the focus will continue to be on the RepBox object, forcing the user to enter a value for this. When a non-empty value is input the focus then switches to getting a value from the user for the starting sales. The initial value of this variable is 0.

The next group of statements uses With Worksheets ("weeklysales").Range ("total") to access properties and methods of the object called total. If you study the worksheets in Figures 10.5 and 10.6, you will see that it is at cell address A42. newSumSales = salesBox.Value + .Offset (0, 1) will assign the newSumSales variable to the current value of the SalesBox, plus the .offset (0,1) v alue. Look at the w orksheet in Figure 10.5 and y ou will see that this offset contains the current total. The new total will be stored in newSumSales. An entire row is then inserted relative to the total object using .EntireRow.Insert.

Next, the data is entered using the next two statements, i.e. Offset (-1, 0) = RepBox.Value. Note: we use Offset (-1, 0) because the new rep name needs to be immediately above the total object. Similarly the position of the

Private Sub AddButton_Click () RepBox.SetFocus With RepBox

If .Value = "" Then MsgBox "Enter a name for the rep to be added" .SetFocus End If

If .Value = "" Then MsgBox "Enter a sales value for the rep given" salesBox.SetFocus End If End With

With Worksheets ("weeklysales").Range ("total") newSumSales = salesBox.Value + .Offset(0, 1) .EntireRow.Insert .Offset(-1, 0) = RepBox.Value .Offset(-1, 1) = salesBox.Value .Offset(0, 1) = newSumSales

Range ("sales_to_date", .Offset (-1, 1)).Name = "sales_to_date" End With

Replist.AddItem RepBox.Value Unload Me End Sub

Figure10.9 Screenshot of form during the program run

corresponding new rep sales to date would have to be at one column to the right of this reference, i.e. Offset (-1,1). Next, the value of the newSumSales needs to be inserted in the cell to the right of the position of the total object, i.e. Offset (0,1) and finally, we need to set the new sales_to_date range name to the old range plus the cell created with the new sales to date entry, since this is going to be the appended sales to date range, ready for any further additions. This is accomplished by using the statement: Range ("sales_to_date", .Offset (-1, 1)).Name = "sales_to_date".

The With block ends in the usual way with End With. Next, the RepList.AddItem.Cell.Value will append the list with the extra name on the form before Unload Me will unload the form.

The code for this event is very simple and requires only the statements Unload Me and End (Listing 10.6).

Listing 10.6 The Cancel event code

Private Sub CancelButton_Click () Unload Me End End Sub

When you create an event procedure, it will not be displayed in the macro list along with other sub procedures. This is because event procedures are stored 'behind' the objects that contain them, and would therefore not be written or stored in standard code modules. Event procedures are private in that their code is not visible in the standard modules as sub procedures are. This is why event procedure code begins with the keyword Private before the word Sub.

0 0

Post a comment