Solved

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

Posted on 2011-02-25
12
283 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
Back Up Your Microsoft Windows Server®

Back up all your Microsoft Windows Server – on-premises, in remote locations, in private and hybrid clouds. Your entire Windows Server will be backed up in one easy step with patented, block-level disk imaging. We achieve RTOs (recovery time objectives) as low as 15 seconds.

 

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
 
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

Enterprise Mobility and BYOD For Dummies

Like “For Dummies” books, you can read this in whatever order you choose and learn about mobility and BYOD; and how to put a competitive mobile infrastructure in place. Developed for SMBs and large enterprises alike, you will find helpful use cases, planning, and implementation.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

The new Microsoft OS looks great, is easier than ever to upgrade to, it is even free.  So what's the catch?  If you don't change the privacy settings, Microsoft will, in accordance with the (EULA) you clicked okay to without reading, collect all the…
Having trouble getting your hands on Dynamics 365 Field Service or Project Service trial? Worry No More!!!
This Micro Tutorial will demonstrate in Microsoft Excel how to add style and sexy appeal to horizontal bar charts.
This Micro Tutorial will demonstrate how to create pivot charts out of a data set. I also added a drop-down menu which allows to choose from different categories in the data set and the chart will automatically update.

810 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