Solved

Excel 2010 Change DisplayAlerts Setting When Sheet Not Running Automatically

Posted on 2010-11-24
3
755 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

Revamp Your Training Process

Drastically shorten your training time with WalkMe's advanced online training solution that Guides your trainees to action.

Question has a verified solution.

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

You need to know the location of the Office templates folder, so that when you create new templates, they are saved to that location, and thus are available for selection when creating new documents.  The steps to find the Templates folder path are …
This article describes a serious pitfall that can happen when deleting shapes using VBA.
The viewer will learn how to use a discrete random variable to simulate the return on an investment over a period of years, create a Monte Carlo simulation using the discrete random variable, and create a graph to represent the possible returns over…
The viewer will learn how to create a normally distributed random variable in Excel, use a normal distribution to simulate the return on an investment over a period of years, Create a Monte Carlo simulation using a normal random variable, and calcul…

696 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