Solved

Excel 2010 Change DisplayAlerts Setting When Sheet Not Running Automatically

Posted on 2010-11-24
3
716 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 500 total points
Comment Utility
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
Comment Utility
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
Comment Utility
Your suggestion worked.  Thanks a lot.
Alexis
0

Featured Post

How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

Join & Write a Comment

A little background as to how I came to I design this code: Around 5 years ago I designed an add-in that formatted Excel files to a corporate standard, applying different cell colours and font type depending on whether the cells contained inputs,…
How to quickly and accurately populate Word documents with Excel data, charts and images (including Automated Bookmark generation) David Miller (dlmille) Synopsis In this article you’ll learn how to use ExcelToWord! to copy data,charts, shapes …
The viewer will learn how to use the =DISCRINV command to create a discrete random variable, use this command to model a set of probabilities and outcomes in a Monte Carlo simulation, and learn how to find the standard deviation of a set of probabil…
This Micro Tutorial demonstrates in Microsoft Excel how to consolidate your marketing data by creating an interactive charts using form controls. This creates cool drop-downs for viewers of your chart to choose from.

771 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

Need Help in Real-Time?

Connect with top rated Experts

11 Experts available now in Live!

Get 1:1 Help Now