In Part 1, I introduced an example where two or more forms present differing views of an underlying ‘Model’ object. In this case, the Model is a cursor-like object that represents a particular data record, and the forms display different columns from the currently cursored row. Okay, it’s a bit simplistic, but suggests how a proper multi-form interface might work.
Now the code. Let’s start with a class (module) for RecordCursor. This has Get properties for Position (i.e. row number), Value (indexed by column number) and column count (fixed). Here’s the initialization:
Private Sub Class_Initialize() rc_colcount = Range("A1").CurrentRegion.Columns.Count ReDim rc_value(rc_colcount - 1) rc_position = 1 Update End Sub
The Update function refreshes the RecordCursor’s value array:
Private Sub Update() Dim i As Integer Dim rng As Range For i = 1 To rc_colcount Set rng = Range("A1").Offset(rc_position - 1, i - 1) rc_value(i - 1) = rng.Value Next End Sub
Now the event-related parts. The class declares an event:
Public Event CursorMove()
This is raised by the Move operations:
Public Sub MoveNext() rc_position = rc_position + 1 Update RaiseEvent CursorMove End Sub Public Sub MovePrev() 'pre: Position > 1 rc_position = rc_position - 1 Update RaiseEvent CursorMove End Sub
Note the precondition on MovePrev: this will be ensured by the form disabling the Previous button when the cursor is on record #1. In contrast, there’s nothing to stop the cursor moving past the last record.
Now the forms (there are two identical, but differently named form modules). These have a reference to a RecordCursor, and use it as you would expect, calling (Get) Value and the Move operations. However, the reference is WithEvents:
Private WithEvents frm_record As RecordCursor
This allows the form to handle the CursorMove event:
Private Sub frm_record_CursorMove() Update End Sub
Update gets the appropriate value:
ValueText.Value = _ frm_record.Value(CInt(ColumnCombo.Value))
The point is that both forms handle the CursorMove event: the one that caused it and the passive one.
Finally, each form’s RecordCursor needs to be set on initialization:
Private Sub UserForm_Initialize() Set frm_record = current Dim i As Integer For i = 1 To frm_record.ColCount ColumnCombo.AddItem i Next ColumnCombo.ListIndex = 0 '--> Change event End Sub
current is a public variable in an ordinary module. It would be nicer if it could be in the Worksheet module, but this isn’t allowed. The object is created from a command button on the worksheet, but this could be done on Workbook_open or Worksheet_activate.
Private Sub ResetCommand_Click() Set current = New RecordCursor MsgBox "Reset Cursor" End Sub
In addition, there are a couple of command buttons to show the forms, but this could be done in different ways.
So the event mechanism allows synchronisation between our two (or more) forms. We could get more sophisticated. We could have two different forms using two different cursors on two different worksheets, synchronised via a shared ‘entity’ object (holding the current Id or Key that connects records on the two worksheets). There would then be two levels of event-handling: by the cursors and by the forms. I think I might try this …