‘Apply’ logic for UserForm dialog

A recent comment on UserForm1.Show asked about how to extend that logic to a dialog that would have an “Apply” button. This article walks you through the process – and this time, there’s a download link!

The dialog is a simple UserForm with two textboxes and 3 buttons:


The Model for this dialog is a simple class exposing properties that the two textboxes manipulate – I’ve named the class ExampleModel:

Option Explicit

Private Type TModel
    field1 As String
    field2 As String
End Type

Private this As TModel

Public Property Get field1() As String
    field1 = this.field1
End Property

Public Property Let field1(ByVal value As String)
    this.field1 = value
End Property

Public Property Get field2() As String
    field2 = this.field2
End Property

Public Property Let field2(ByVal value As String)
    this.field2 = value
End Property

I also defined a simple IDialogView interface, which can be implemented by any other dialog, since it passes the model as an Object (i.e. it’s not tightly coupled with the ExampleModel class in any way); the contract is simply “here’s your model, now show me a dialog and tell me if I can proceed to consume the model” – in other words, the caller provides an instance of the model, and the implementation returns True unless the user cancelled the form.

Option Explicit

Public Function ShowDialog(ByVal viewModel As Object) As Boolean
End Function

The form’s code-behind therefore needs to implement the IDialogView interface, and somehow store a reference to the ExampleModel. And since we have cancellation logic but we’re not exposing it (we don’t need to – the IDialogView.ShowDialog interface handles that concern, by returning False if the dialog is cancelled), the IsCancelled flag is just internal state.

As far as the “apply” logic is concerned, the thing to note here is the Public Event ApplyChanges event, which we raise when the user clicks the “apply” button:

Option Explicit

Public Event ApplyChanges(ByVal viewModel As ExampleModel)

Private Type TView
    IsCancelled As Boolean
    Model As ExampleModel
End Type
Private this As TView

Implements IDialogView

Private Sub AcceptButton_Click()
End Sub

Private Sub ApplyButton_Click()
    RaiseEvent ApplyChanges(this.Model)
End Sub

Private Sub CancelButton_Click()
End Sub

Private Sub Field1Box_Change()
    this.Model.field1 = Field1Box.value
End Sub

Private Sub Field2Box_Change()
    this.Model.field2 = Field2Box.value
End Sub

Private Sub OnCancel()
    this.IsCancelled = True
End Sub

Private Function IDialogView_ShowDialog(ByVal viewModel As Object) As Boolean
    Set this.Model = viewModel
    Me.Show vbModal
    IDialogView_ShowDialog = Not this.IsCancelled
End Function

Private Sub UserForm_Activate()
    Field1Box.value = this.Model.field1
    Field2Box.value = this.Model.field2
End Sub

Private Sub UserForm_QueryClose(Cancel As Integer, CloseMode As Integer)
    If CloseMode = VbQueryClose.vbFormControlMenu Then
        Cancel = True
    End If
End Sub

The Presenter class does all the fun stuff. Here I’ve decided to allow the model’s data to be optionally supplied as parameters to the Show method; the form handles its Activate event to make sure the form controls reflect the model’s initial values when the form is displayed:

Option Explicit
Private WithEvents view As ExampleDialog

Private Property Get Dialog() As IDialogView
    Set Dialog = view
End Property

Public Sub Show(Optional ByVal field1 As String, Optional ByVal field2 As String)

    Set view = New ExampleDialog

    Dim viewModel As ExampleModel
    Set viewModel = New ExampleModel
    viewModel.field1 = field1
    viewModel.field2 = field2

    If Dialog.ShowDialog(viewModel) Then ApplyChanges viewModel
    Set view = Nothing

End Sub

Private Sub view_ApplyChanges(ByVal viewModel As ExampleModel)
    ApplyChanges viewModel
End Sub

Private Sub ApplyChanges(ByVal viewModel As ExampleModel)
    Sheet1.Range("A1").value = viewModel.field1
    Sheet1.Range("A2").value = viewModel.field2
End Sub

So we have a Private WithEvents field that gets assigned in the Show method, and we handle the form’s ApplyChanges event by invoking the ApplyChanges logic, which, for the sake of this example, takes the two fields and writes them to A1 and A2 on Sheet1; if you’ve read There is no worksheet then you know how you can introduce an interface there to decouple the worksheet from the presenter, and then it doesn’t matter if you’re writing to a worksheet, a text file, or a database: the presenter doesn’t need to know all the details.

The calling code in Module1 might look like this:

Option Explicit

Public Sub ExampleMacro()
    With New ExamplePresenter
        .Show "test"
    End With
End Sub

One problem here, is that the View implementation is coupled with the presenter (i.e. the presenter is creating the view): we need the concrete UserForm type in order for VBA to see the events; without further abstraction, we can’t quite pass a IDialogView implementation to the presenter logic without popping up the actual dialog. Pieter Geerkens has a nice answer on Stack Overflow that describes how an Adapter Pattern can be used to solve this problem by introducing more interfaces, but covering this design pattern will be the subject of another article.


4 thoughts on “‘Apply’ logic for UserForm dialog”

  1. Mille merci Mathieu, this is as usual *really* useful stuff.

    I would love-love-love to use this MVP approach with Access.
    But haven’t succeeded with it yet.

    For example, with this particular case, when I try to use this approach on Access, it runs into specific difficulties.
    Such as, a modal form seems to need to be opened via:
    DoCmd.OpenForm view.Name, acNormal, , , , acDialog

    1) I don’t know if that’s actually opening the view form object)
    2) This seems to create problems because where this line doesn’t work so well:
    If Dialog.ShowDialog(viewModel) Then ApplyChanges viewModel
    Eg. upon closing the form, the code goes back to the Presenter class, and the form’s this.IsCancelled loses its state

    I’ll keep working my way through these issues, and hopefully get there.
    But if in the meantime you have any ideas, I’d be happy to hear them 🙂

    Cheers again, really appreciate what you’re doing with this!



    1. Thanks! I’m not very familiar with Access, but I do know that Rubberduck sees Access forms are “document” modules, like a worksheet in Excel. This might explain it. Know that you can still have MSForms UserForm modules in an Access VBA project though: IIRC vanilla-AccessVBE hides the command, but Rubberduck’s code explorer doesn’t care and exposes the command anyway, so you can add a UserForm in Access if you need one. Hope it helps!


  2. I decided to convert my current project over to using proper OOP techniques, following your posts. It has a UserForm that has an Import button, Report Button and a Multi-Column-ListBox that lists all the projects from a database among other things. I’m having trouble deciding how far I want to go into defining the Model for the ListBox. It looks like I’m going to implement AddItem, List, Selected, ListCount & ListIndex properties. It’s reminding me of my awful 64-bit COM wrapper for the .Net Listview control :(.

    How would you deal with ListBox or ComboBox controls in the Model?

    Liked by 1 person

    1. Controls themselves belong only in the view; in the model I’d have a getter that gives me the items I need to have in the listbox, and a read/write property for the current/selected item. The code-behind for the listbox would set the model’s selecteditem on change, and the setter for the view’s “model” property would populate the listbox. I hope it’s not too confusing, it’s a bit hard to be clear in a comment box.. it’s very similar to a ViewModel in WPF/MVVM, except instead of XAML bindings you use the controls’ event handlers to set the model properties – like I’m doing here with these textboxes.


Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s