Method to trap Userform changes

[BillWilts]

I have a dynamic multipage userform with textboxes and optionbuttons. I am trying to include a routine that will allow me to ask the user if they want to save any changes made on any of the pages or any of the optionbuttons. It would be too complex to test each control to see if it has changed and I was wondering if there is a global test to trap data changes on a user form.

[Richard Buttrey]

Hi,

I'm not aware of any such global parameter, but if you were to store the results of all the relevant objects in an Excel table, you could then use a 'before' table (which would be a copy of the table before you initialise your form), and in Excel have a cell (or cells) which compare the before/after tables and return say a 1 or 0 value. Then just use the value of this test cell(s) to decide whether to save changes or not.

HTH

[Leith Ross]

Hello BillWilts,

You mention this a dynamic UserForm. Does that mean you are creating this UserForm at runtime?

Richard is correct that there is no global event that is raised whenever a control's value has changed on the UserForm. I find it curious you only want to know that something has changed without first knowing if it is valid before you save it.

[mikerickson]

If you are looking to have a UnDo changes button, you could put the previous values in each control's .Tag property.

If you want to record all the changes, a generic routing become more problematic. In addition to storing the values of the controls, the .Tag property might also be a factor since many UFs use Tag properties for their operation. Similarly, ListBoxes and ComboBoxes have two problems, accomidation of multi-select and changing List contents is an issue. Some UF's change the ColumnWidths in the course of their operation. Also the .Visible and .Enable properties often change in response to a users actions.

[snb]

You can easily store the userform's VBA code when initializing and compare that to the userform's VBA code at any given point later. (or compare the exported frm files at different points).
But why would you do it ?

[BillWilts]

Thanks to all for the replies.

The form is created at run time based on the cells from two worksheets. The purpose of the application is to create a contract as a Word document containing blocks of text using parameters (e.g customer name, country, etc.) and optional clauses selected via check boxes and option buttons.

The idea trapping of changes is that a contract might be written out for Customer A and then the identical contract for Customer B. As the only change is the customer name, it is probably not worth saving the changes but it would be nice to ask the user the question!

[snb]

Please explore Word's builtin mailmerge-facilities.

[BillWilts]

I don't have a problem with the Word part. I can write out a fully formatted document with headers, footers and a table of contents.

The question was about a simple way of trapping any changes made on an Excel user form without having to test each individual control.

On the basis this is a nice to have (users will just need to save all of their edits!) and that a simple answer probably does not exist, I think I need to let this one go...