Linking Userforms and Worksheets: Part III

See Part I: Setting Up the Form
See Part II: Helper Procedures

When the userform is showing a record, I want the user to be able to tell if he has made any changes. I only want my Save commandbutton to be enabled if the record has changed. I also want to warn the user if they are moving off an unsaved record. For these reasons, I’ve set up a system to keep track of when a record is dirty.

I use a class module called CControlEvents that looks like this

Public WithEvents gTextBox As MSForms.TextBox
Public WithEvents gCombo As MSForms.ComboBox

Private Sub gCombo_Change()

UContact.IsDirty = True

End Sub

Private Sub gTextBox_Change()

UContact.IsDirty = True

End Sub

I couldn’t get it to work with a generic Control object, so I had to create a variable for each kind of control on the form, namely a textbox and a combobox. I use these controls’ Change events to flag the record as dirty. The class is created in the userform’s Initialize event which I will discuss in a future post.

As you already know, a userform is just a class module that has a user interface built-in. That means that I can create custom properties for that userform,. The event code above uses the IsDirty property that I created in the userform.

Private mbIsDirty As Boolean

Property Get IsDirty() As Boolean

IsDirty = mbIsDirty

End Property

Property Let IsDirty(bDirty As Boolean)

mbIsDirty = bDirty
Me.cmdSave.Enabled = bDirty

End Property

The Property Get procedure allows me to read the value of IsDirty, which I do in code that will be shown later. The Property Set procedure stores the status of the record in the module level variable. It also changes the Enabled property of the Save commandbutton. I don’t want the user to be able to save unless changes have been made.

4 thoughts on “Linking Userforms and Worksheets: Part III

  1. Hi Dick,

    Love the website and slowly trawling through all the posts from 2004 onwards :o)

    But when I click on the link for Part I, it takes me to Part II?

    Any ideas?

    Many thanks,


  2. Ken: Part I got lost to the internet monster that ate my blog last year. Thank goodness for the wayback machine. It should be back in action. I think I’ll go back up my blog right now.

  3. Oh yeah, Ken, as long as I have your attention. Since you’re checking out the archives, let me know if you see anything else that isn’t right. You can leave a comment or email me at Also, if there are any posts you’d like to see, you have but to ask. Thanks.

Posting code? Use <pre> tags for VBA and <code> tags for inline.

Leave a Reply

Your email address will not be published.