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
Option Explicit
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.
Hi,
A newbie who says thanks for the clear explanation.