Excel 2010 Change DisplayAlerts Setting When Sheet Not Running Automatically
Posted on 2010-11-24
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.