[Last Call] Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

Excel 2010 Change DisplayAlerts Setting When Sheet Not Running Automatically

Posted on 2010-11-24
3
Medium Priority
?
765 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

VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

Question has a verified solution.

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

Excel can be a tricky bit of software to get your head around. Whilst you’ll be able to eventually get to grips with the basic understanding of how to get by, there are a few Excel tips that not everybody will even know about let alone know how to d…
Windows Explorer let you handle zip folders nearly as any other folder: Copy, move, change, and delete, etc. In VBA you can also handle normal files and folders, but zip folders takes a little more - and that you'll find here.
Excel styles will make formatting consistent and let you apply and change formatting faster. In this tutorial, you'll learn how to use Excel's built-in styles, how to modify styles, and how to create your own. You'll also learn how to use your custo…
Finds all prime numbers in a range requested and places them in a public primes() array. I've demostrated a template size of 30 (2 * 3 * 5) but larger templates can be built such 210  (2 * 3 * 5 * 7) or 2310  (2 * 3 * 5 * 7 * 11). The larger templa…

834 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