Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Detecting Input Change on User Form
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. |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Detecting Input Change on User Form
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" wrote in message ... 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. |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Detecting Input Change on User Form
On Mon, 1 Dec 2003, John Wilson wrote:
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. |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Detecting Input Change on User Form
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 "Don Wiss" wrote in message ... On Mon, 1 Dec 2003, John Wilson wrote: 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. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Can I make a picture change based on input from a user? | Excel Discussion (Misc queries) | |||
Trying to select a specific range based on the time value of user form input | New Users to Excel | |||
Trouble with saving user input in an Excel Form. | Excel Discussion (Misc queries) | |||
Change color of cell after user has input a number | Excel Discussion (Misc queries) | |||
Help with a User Input Form | Excel Programming |