Improve company productivity with a Business Account.Sign Up

x
?
Solved

Excel 2010 Change DisplayAlerts Setting When Sheet Not Running Automatically

Posted on 2010-11-24
3
Medium Priority
?
769 Views
Last Modified: 2012-05-10
Hi.   I created sheets that use vba to automatically pull data from a database.  Sometimes these sheets are executed by an automatic process while other times the users manually press the "Get Data" button.  The main user of these sheets is having a problem with Excel 2010 that he did not experience in Excel 2003.  When he executes the "Get Data" button using Excel 2010 and then closes the sheet, Excel does not save or prompt to save the data. Therefore, if he does not remember to Save the sheet manually, he loses his data.  Also, if he has other Excel sheets open at the time and works in them after he has run the macros on the other sheets, if he forgets to manually save those other sheets, he loses the data too because Excel doesn't prompt to save unsaved data.  I figured out that the problem is my use of the DisplayAlerts setting, which I have set to false so the automatic process can run the sheets.  I had to make these changes while working on getting the sheets to work manually without popping up messages that require user intervention.  

My question is this:  Is there a way I can use the same set of sheets that can run automatically or manually?  Right now, the automatic process is set up through a master Excel sheet that looks in the folder and processes all the Excel sheets by calling the macro behind the "Get Data" button.  Do you think I can just remove all the DisplayAlerts = false statements in the sheets and just put the DisplayAlerts statements in the master sheet so I turn off the alerts before executing all the sheets and then turn them back on again when the process is done?  I hope I am making sense.

Thanks,
Alexis
0
Comment
Question by:alexisbr
  • 2
3 Comments
 
LVL 37

Accepted Solution

by:
TommySzalapski earned 2000 total points
ID: 34206650
There are a few good ways to handle this that I can think of.

You MIGHT not need to use display alerts, you may be able to code without it. (i.e. use wkbook.Close(false) and it skips the alert automatically.

Another option: have the automatic process disable alerts and re-enable them. Something like this
Sub AutomaticProcess()
xlsApp = CreateObject("Excel.Application") 'Or however you do this, if it's within Excel just use Application
'Add this line
xlsApp.DisplayAlerts = False

'Original code goes here

xlsApp.DisplayAlerts = True

End Sub

Open in new window

0
 

Author Comment

by:alexisbr
ID: 34206926
Thanks, Tommy.  Your second approach was what I had in mind.  I need to do some testing.  I will post back after I am done testing.

Alexis
0
 

Author Comment

by:alexisbr
ID: 34217038
Your suggestion worked.  Thanks a lot.
Alexis
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.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

In Part II of this series, I will discuss how to identify all open instances of Excel and enumerate the workbooks, spreadsheets, and named ranges within each of those instances.
Here is why.
This Micro Tutorial will demonstrate on a Mac how to change the sort order for chart legend values and decrpyt the intimidating chart menu.
This Micro Tutorial will demonstrate how to use longer labels with horizontal bar charts instead of the vertical column chart.

607 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