Solved

Set a saved or not saved flag when moving data from Excel form to worksheet

Posted on 2011-02-25
12
273 Views
Last Modified: 2012-08-14
I have code that, when the user hits the Save button, move the form data into the woksheet.  But I would like to have some sort of a flat that if there is data in the form and the user hits the close button, they are warned that the data has not been saved.  I was thinking of a blnSaveFlag as aboolean, but am not sure where or how to initialize.  When the data is aved, I can set the value to true - it would be a public variable so should be able to be recgozied by the close button, but am just a little perplexed on how to handle and where to set the value to 0.
0
Comment
Question by:ssmith94015
  • 5
  • 4
  • 3
12 Comments
 
LVL 30

Expert Comment

by:SiddharthRout
ID: 34984112
On way I can think of is in the Form's terminate event check if there are values in the Form's Controls and if there are, then prompt a message.

If you can upload your workbook then maybe I can give you an example.

Sid
0
 

Author Comment

by:ssmith94015
ID: 34984133
I do not understand the terminate event, would that take place before or after the unload me when the user hits the Close button?
0
 
LVL 30

Expert Comment

by:SiddharthRout
ID: 34984143
When the user click on the Red Cross button the terminate event will fire.

Sid
0
 

Author Comment

by:ssmith94015
ID: 34984146
Sid, this is condifential data so I will clean out the workbook but will not be able to post until tomorrow.  It has gotten a little crazy here all of the sudden.
0
 
LVL 30

Expert Comment

by:SiddharthRout
ID: 34984153
Do you want me to post a sample ?

Sid
0
 
LVL 30

Accepted Solution

by:
SiddharthRout earned 250 total points
ID: 34984193
In fact use the UserForm_QueryClose event instead of the Terminate event.

For example if you have 4 text boxes on the form and all has data and then user presses the Close Button then this will help.

Private Sub UserForm_QueryClose(Cancel As Integer, CloseMode As Integer)
    If Len(Trim(TextBox1.Text)) <> 0 And _
    Len(Trim(TextBox2.Text)) <> 0 And _
    Len(Trim(TextBox3.Text)) <> 0 And _
    Len(Trim(TextBox4.Text)) <> 0 Then
        ret = MsgBox("Do you want to save the data?", vbYesNo)
        If ret = vbYes Then Cancel = True
    End If
End Sub

Open in new window


Sid
0
What Is Threat Intelligence?

Threat intelligence is often discussed, but rarely understood. Starting with a precise definition, along with clear business goals, is essential.

 
LVL 41

Assisted Solution

by:dlmille
dlmille earned 250 total points
ID: 34988575
I'm building on Sid's input as an assist, if this is helpful...

Perhaps the textboxes already have initial values?  perhaps I can also assist...

First - declare blnsavedflag as a global variable.  In a module, put the following command:

Public blnSavedflag as boolean

You probably have the following two commands to initialize and show your Userform:

Load Userform

and at some point

Userform.Show

right before the Userform.Show statement, you can set blnSavedFlag = FALSE

Something like....

 
Sub Userform_Initialize_and_Load()

     Load Userform

     'code that perhaps initializes some of the Userform values
     blnSavedFlag = false

     Userform.Show

End Sub

Open in new window



Then, in your UserForm code, if there are certain conditions met (and you know what they are, per your original question), you set blnSavedFlag = True

On the terminate event - something you run when the X is hit, or you make run when they hit the CLOSE button - goes in your Userform code like this:
Private Sub UserForm_QueryClose(Cancel as Integer, CloseMode As Integer)
 ' do whatever checking needs to be done to ensure the Userform can be closed, or at least give the user a chance to override
 ' Sid's method is appropriate, but if there are many controls and you are setting the blnSavedFlag as a result of different actions being
 ' taken or settings set that indicate the contents are SAVED, then just do the following:

 if NOT blnSavedFlag then
   ret = MsgBox("Do you really want to exit (your changes are not saved!)",vbYesNo,"Hit No to return to the Userform")
   if ret = vbNo then Cancel = true

End Sub

Open in new window

0
 
LVL 41

Expert Comment

by:dlmille
ID: 34988600
@ssmith -

It occurrs to me that perhaps you are is writing to a database, and potentially have initialized the firm with initial values.  In this case, you might want an additional variable like  blnChanged as boolean - where you use Sid's example to test whether something has changed on the form - there are events to detect changes on most if not all the Userform controls.  No need to burden the user if nothing's changed.  Then, the code is more correctly....

Public blnChanged as boolean - could be declared globally as well and initialized to FALSE as part of your UserForm routine, as with blnSavedFlag.

 
Private Sub UserForm_QueryClose(Cancel as Integer, CloseMode As Integer)
 ' do whatever checking needs to be done to ensure the Userform can be closed, or at least give the user a chance to override
 ' Sid's method is appropriate, but if there are many controls and you are setting the blnSavedFlag as a result of different actions being
 ' taken or settings set that indicate the contents are SAVED, then just do the following:

 if NOT blnSavedFlag and blnChanged then
   ret = MsgBox("Do you really want to exit (your changes are not saved!)",vbYesNo,"Hit No to return to the Userform")
   if ret = vbNo then Cancel = true

End Sub

Open in new window

Cheers,

Dave
0
 

Author Comment

by:ssmith94015
ID: 34991642
Sorry I have not been back to this question, I was informed that there was a "new" project and oh, by the way, it has to be on line MONDAY so have been working madly on that.  I will look at all the suggestions and get back to you.  I will be soOOOOooo glad when this contract is up........
0
 

Author Comment

by:ssmith94015
ID: 34991646
BTW, this is all in EXCEL 2003.  They did not want to use a database, which would have made this all so much simpler.  
0
 

Author Closing Comment

by:ssmith94015
ID: 34991662
Thank you.  A combination of both worked.
0
 
LVL 41

Expert Comment

by:dlmille
ID: 34992713
Good luck and speed on your project.

Dave
0

Featured Post

6 Surprising Benefits of Threat Intelligence

All sorts of threat intelligence is available on the web. Intelligence you can learn from, and use to anticipate and prepare for future attacks.

Join & Write a Comment

As with any other System Center product, the installation for the Authoring Tool can be quite a pain sometimes. This article serves to help you avoid making these mistakes and hopefully save you a ton of time on troubleshooting :)  Step 1: Make sur…
We were having a lot of "Heartbeat Alerts" in our SCOM environment, now "Heartbeat" in a SCOM environment for those of you who might not be familiar with SCOM is a packet of data sent from the agent to the management server on a regular basis, basic…
This Micro Tutorial demonstrate the bugs in Microsoft Excel for Mac with Pivot Charts.
This is Part 3 in a 3-part series on Experts Exchange to discuss error handling in VBA code written for Excel. Part 1 of this series discussed basic error handling code using VBA. http://www.experts-exchange.com/videos/1478/Excel-Error-Handlin…

705 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

19 Experts available now in Live!

Get 1:1 Help Now