Detecting Input Change on User Form

[Don Wiss]

I'm using Excel 97. I have a user form for input. It has buttons for OK
(which saves) and Cancel. But one person was clicking the X in the upper
right and losing his input. So I implemented a UserForm_QueryClose (when
CloseMode <> 1) to make the user confirm that they wanted to exit and lose
any changes. But to be friendlier I'd like to only display this
confirmation if the user has actually made a change to the data. Is there
some flag I can read to detect whether input has been changed or not?

Thanks, Don <donwiss at panix.com>.

 

[John Wilson]

Don,

One thing that you could do is set a public variable in the
UserForm and set it to true when a change event on any
of your controls is fired.
Example:

Public ItChanged as Boolean

Private Sub UserForm_Activate()
' Resetthe variable when the UserForm is opened
ItChanged = False
Exit Sub

Private Sub TextBox1_Change()
' Needs to be done in every "Change" event on the UserForm
ItChanged = True
End If

Private Sub UserForm_QueryClose(Cancel As Integer, CloseMode As Integer)
' Sample QueryClose code
If ItChanged = False Then Exit Sub ' No changes made. Allow the close.
' Changes were made. Alert the user. Cancel the close.
If CloseMode = vbFormControlMenu Then
Cancel = True
MsgBox Prompt:="I can't let you do that. Changes were made that need to
be saved" & _
vbCrLf & "Please use one of the buttons on the form", _
Buttons:=vbInformation, _
Title:="Awww...Gee wilikers!!!!"
End If
End Sub

John

 

[Don Wiss]

One thing that you could do is set a public variable in the
UserForm and set it to true when a change event on any
of your controls is fired.

Uh, thanks, but this is a six tab input form with over 100 text fields,
option buttons, checkboxes, comboboxes, etc. No way am I going to create
change events for all of them.

Each also corresponds to a cell someplace on the spreadsheet. I could
compare each field to see if still the same, but again great overkill for
the problem at hand.

Don <donwiss at panix.com>.

 

[John Wilson]

Don,

J-Walk has some coding on his site to create a class module
to handle groups of controls.

You said that you're changing cells on worksheets. Are they
linked cells? Is there anything that's firing the sheet or
workbook calculate event? You might be able to use that.

IMHO though, I think it's overkill to try to detect changes on the
UserForm just so the user won't see you cancel message if they
try to close the UserForm with the "X". You have a button that
closes the form and performs whatever tasks you want it to.
They should learn to use it and they won't get your warning message.

Another option you might want to try is to create a userform
without the "X". You can find an example at Steven Bullen's
site (FormFun.zip)
http://www.bmsltd.co.uk/Excel/Default.htm

John