• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 289
  • Last Modified:

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

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
ssmith94015
Asked:
ssmith94015
  • 5
  • 4
  • 3
2 Solutions
 
SiddharthRoutCommented:
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
 
ssmith94015Author Commented:
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
 
SiddharthRoutCommented:
When the user click on the Red Cross button the terminate event will fire.

Sid
0
NFR key for Veeam Agent for Linux

Veeam is happy to provide a free NFR license for one year.  It allows for the non‑production use and valid for five workstations and two servers. Veeam Agent for Linux is a simple backup tool for your Linux installations, both on‑premises and in the public cloud.

 
ssmith94015Author Commented:
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
 
SiddharthRoutCommented:
Do you want me to post a sample ?

Sid
0
 
SiddharthRoutCommented:
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
 
dlmilleCommented:
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
 
dlmilleCommented:
@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
 
ssmith94015Author Commented:
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
 
ssmith94015Author Commented:
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
 
ssmith94015Author Commented:
Thank you.  A combination of both worked.
0
 
dlmilleCommented:
Good luck and speed on your project.

Dave
0

Featured Post

Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

  • 5
  • 4
  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now