Replicating the Access Form.Dirty property for an Excel UserForm

[BanginMyHeadOnMyDesk]

Good evening all.

Does anyone have any bright ideas about replicating the Form.Dirty property from Access in Excel?

I'm creating a workflow tool in Excel that utilises a UserForm to display records that are pulled from an Access database.

Users will be able to amend the record details on the form and submit the changes to update the database.

For info, it has to be in Excel as everyone in my organisation has that and not everyone has Access, even the Runtime version.

It works by importing the results of a query (SQL in VBA) into a table in Excel. The form can then navigate between records using Prev/Next buttons but I don't want the user to be able to move to another record if they've made changes to the current one that haven't been saved/submitted.

Short of a long-winded process using Enter and AfterUpdate events on each TextBox and ComboBox I'm stumped. My knowledge of Class modules is nowhere near up to this if that's even an option.

Any help gratefully received!

Beth.

[LJMetzger]

Hi Beth,

See the file associated with post #11 in the following thread: http://www.excelforum.com/excel-prog...matically.html

It uses dynamic and static TextBoxes and assigns a class module as the event handler for the Text Boxes. Important items are highlighted in red.

Implementation of the Class module is not trivial, but requires little understanding as use is entirely cookbook.

There is a little known UserForm Control .Tag attribute that you can use to store anything you want. If you store the original TextBox value in each TextBox .tag attribute, you can then loop thru the controls to determine if the data is Dirty.

The code for the above referenced post follows:
In the file, Class Module 'Class4' contains the following code:

Please Login or Register  to view this content.

UserForm1 code module contains the following code:

Please Login or Register  to view this content.

Ordinary code module code (concatenated from 3 different code modules contains):

Please Login or Register  to view this content.

Lewis

[BanginMyHeadOnMyDesk]

Many thanks, Lewis.

There's a lot going on there but I shall take some time to digest it and see if I can pick out the bits I need.

Beth.