[2 days left] What’s wrong with your cloud strategy? Learn why multicloud solutions matter with Nimble Storage.Register Now

x
?
Solved

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

Posted on 2011-02-25
12
Medium Priority
?
288 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
[X]
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
  • 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
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 

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

Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

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

Question has a verified solution.

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

This code takes an Excel list of URL’s and adds a header titled “URL List”. It then searches through all URL’s in column “A”, looking for duplicates. When a duplicate is found, it is moved to the top of the list. The duplicate URL’s are then highlig…
If you need to forecast numbers -- typically for finance -- the Windows and Mac versions of Excel 2016 have a basket of tools to get the job done.
Graphs within dashboards are meant to be dynamic, representing data from a period of time that will change each time the dashboard is updated with new data. Rather than update each graph to point to a different set within a static set of data, t…
This Micro Tutorial will demonstrate on a Mac how to change the sort order for chart legend values and decrpyt the intimidating chart menu.

649 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