A grid event executes a specific VBA procedure (an event procedure) only for the current line in a grid. The current line is the line that has the focus. To view the grid events available, select a grid object in your project, then display the Visual Basic Code window. Select Grid in the Object list; window events will appear in the Procedure list.
There are four basic kinds of grid events you’ll work with in VBA: line got focus, line changed, line lost focus and line populate events. Events occur when the user displays a new line in a grid (a line populate event) moves the focus into a grid line (a line got focus event), when the value of a line changes (a line changed event), or when the user moves the focus out of a line (a line lost focus event).
If you use the Modifier to modify a window containing a grid (such as a lookup window), VBA grid events will occur only if you set both the window’s and the grid’s EventMode property to emModifiedOnly. Changing this property allows events to occur only for the modified version of the window and the grid. To change the EventMode property, use the Visual Basic Properties window.
VBA line got focus events occur when the user moves to a line in a grid. The VBA BeforeLineGotFocus and AfterLineGotFocus events execute event procedures before or after Microsoft Dynamics GP code runs for the got focus event. Microsoft Dynamics GP typically uses the got focus event to verify whether the user can enter the line. Since you cannot cancel the Microsoft Dynamics GP got focus event from VBA, the VBA got focus event you choose may be of little consequence.
Line got focus events are useful for checking the value of a field in the line gaining focus. In the following example, the BeforeLineGotFocus event checks the value of the Item Number field in the Invoice Entry grid. If the field is empty, the event procedure opens the Items lookup window:
Private Sub Grid_BeforeLineGotFocus() If ItemNumber.Empty = True Then 'Open the lookup window InvoiceEntry.LookupButton6 = 1 End If End Sub
VBA line change events occur when the user changes the contents of a line in an editable or adds-allowed grid, then moves the focus out of the line. The VBA BeforeLineChange and AfterLineChange events occur before or after Microsoft Dynamics GP code runs for the line change event.
Microsoft Dynamics GP uses the line change event to save data in the line to a table when the user leaves the line. An example of this is the Invoice Entry window, where each grid line represents a separate transaction record in the invoice. As the user moves to a new line, the line change event saves the invoice line item to a table.
The BeforeLineChange event occurs before Microsoft Dynamics GP code runs for the line change event. Use this event when you want to check the value of fields in a line, then cancel the Microsoft Dynamics GP line change event if specified criteria aren’t met. The KeepFocus parameter cancels the line change event that normally saves the contents of the line, and moves the focus to the field that last had focus in the line.
In the following example, the BeforeLineChange event checks the value of the Markdown Amount field in the Invoice Entry grid. If the user attempts to enter a markdown amount greater than 20%, the KeepFocus parameter stops the Microsoft Dynamics GP line change event and moves the focus back to the current line:
Private Sub Grid_BeforeLineChange(KeepFocus As Boolean) If CCur(MarkdownAmount) > CCur(UnitPrice) * 0.2 Then 'The markdown was greater than 20% KeepFocus = True MsgBox "You cannot enter a markdown greater than 20% " + _ "of the unit price." End If End Sub
The AfterLineChange event occurs after Microsoft Dynamics GP code runs for the line change event. Use this event to perform other actions in the window after the accounting system saves data in the line. For instance, when the user enters line item information in the Invoice Entry grid, then moves to the next line item, the following AfterLineChange event procedure checks the Subtotal field. If the invoice subtotal is greater than $1000, it assigns a specific batch ID to the transaction:
Private Sub Grid_AfterLineChange() If CCur(InvoiceEntry.Subtotal) > 1000 Then 'Set the batch ID to an existing batch InvoiceEntry.BatchID = "BIGSALES" InvoiceEntry.BatchID.Locked = True End If End Sub
VBA line lost focus events occur when the user moves the focus out of a line. The VBA BeforeLineLostFocus and AfterLineLostFocus events occur before or after the Microsoft Dynamics GP code runs for the lost focus event. Microsoft Dynamics GP typically uses the lost focus event to prepare the next line to accept data. Since you cannot cancel the got focus event from VBA, the VBA line lost focus event you choose may be of little consequence.
In the following example, an AfterLineLostFocus event “shrinks” the grid in the Invoice Entry window when the user moves to the next line.
Private Sub Grid_AfterLineLostFocus() InvoiceEntry.ScrollingWindowExpandButton = 1 End Sub
The VBA BeforeLinePolulate event occurs each time Microsoft Dynamics GP displays a new line in a grid. When the accounting system initially displays a grid, the BeforeLinePopulate event occurs repeatedly until the grid is full. It also occurs each time the user displays a new line in the grid, by scrolling up or down in the grid.
The primary reason to use the BeforeLinePopulate event is to filter out any lines you don’t want to display in a grid. For instance, the following event procedure displays only Illinois customers in the Customers and Prospects lookup window:
Private Sub Grid_BeforeLinePopulate(RejectLine As Boolean) If State <> "IL" Then RejectLine = True End If End Sub
The RejectLine parameter lets you selectively filter records from the grid when it’s filled. Limit use of the RejectLine parameter, since it can have an impact on application performance.
A more elaborate use of the BeforeLinePopulate event could make use of a new field added to a lookup window using the Modifier. For example, you could use the Modifer to add a “Fill By State” field to the Customers and Prospects lookup window. A VBA event procedure for the Fill By State field fills the window by “clicking” the Redisplay button in the lookup window:
Private Sub FillByState_BeforeUserChanged(KeepFocus As Boolean, _ CancelLogic As Boolean) Redisplay = 1 End Sub
As the fill occurs, the following procedure uses the BeforeLinePopulate event’s RejectLine parameter to reject records not indicated in the Fill By State field:
Private Sub Grid_BeforeLinePopulate(RejectLine As Boolean) If State <> CustomersandProspects.FillByState Then RejectLine = True End If CustomersandProspects.FillByState.Focus End Sub