Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people, just like you, are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
Solved

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

Posted on 2011-02-25
12
284 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
Ransomware: The New Cyber Threat & How to Stop It

This infographic explains ransomware, type of malware that blocks access to your files or your systems and holds them hostage until a ransom is paid. It also examines the different types of ransomware and explains what you can do to thwart this sinister online threat.  

 

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 42

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 42

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 42

Expert Comment

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

Dave
0

Featured Post

Simplifying Server Workload Migrations

This use case outlines the migration challenges that organizations face and how the Acronis AnyData Engine supports physical-to-physical (P2P), physical-to-virtual (P2V), virtual to physical (V2P), and cross-virtual (V2V) migration scenarios to address these challenges.

Question has a verified solution.

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

Suggested Solutions

Entering time in Microsoft Access can be difficult. An input mask often bothers users more than helping them and won't catch all typing errors. This article shows how to create a textbox for 24-hour time input with full validation politely catching …
Deploying a Microsoft Access application in a Citrix environment is not difficult but takes a few steps. However, Citrix system people are often of little help, as they typically know next to nothing about Access. The script provided here will take …
The viewer will learn how to create two correlated normally distributed random variables in Excel, use a normal distribution to simulate the return on different levels of investment in each of the two funds over a period of ten years, and, create a …
This Micro Tutorial demonstrates using Microsoft Excel pivot tables, how to reverse engineer competitors' marketing strategies through backlinks.

860 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