Solved

Excel 2010 Change DisplayAlerts Setting When Sheet Not Running Automatically

Posted on 2010-11-24
3
751 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
[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
  • 2
3 Comments
 
LVL 37

Accepted Solution

by:
TommySzalapski earned 500 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

Technology Partners: 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!

Question has a verified solution.

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

Suggested Solutions

Introduction While answering a recent question (http:/Q_27311462.html), I created an alternative function to the Excel Concatenate() function that you might find useful.  I tested several solutions and share the results in this article as well as t…
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…
This Micro Tutorial demonstrate the bugs in Microsoft Excel for Mac with Pivot Charts.
This Micro Tutorial will demonstrate on a Mac how to change the sort order for chart legend values and decrpyt the intimidating chart menu.

738 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