I have code in an Access module that deletes sheets in an excel workbook at the end of the sub. Is there a way to suppress the conformation of deleting the sheet with VBA code?
I have code in an Access module that deletes sheets in an excel workbook at the end of the sub. Is there a way to suppress the conformation of deleting the sheet with VBA code?
Use Application.DisplayAlerts = False before deleting the sheets and set it back to true after
You know you're really in trouble when the light at the end of the tunnel turns out to be the headlight of a train hurtling towards you
The major part of getting the right answer lies in asking the right question...
Made your code more readable, use VBA tags (this automatically inserts [vba] at the start of your code, and [/vba ] at the end of your code) | Help those helping you by marking your thread solved when it is.
Do you have to use the Excel object for the application, or is it across the board?Originally Posted by johnske
Use Application.DisplayAlerts = False before deleting the sheets and set it back to true after
New to the forum? Check out our Introductions section to get to know some of the members here. Feel free to tell us a little about yourself as well.
The alert will be thrown by Excel, not Access, so you'd have to use the Excel object.
Ken Puls, CMA - Microsoft MVP (Excel)
I hate it when my computer does what I tell it to, and not what I want it to.
Learn how to use our KB tags! -||- Ken's Excel Website -||- Ken's Excel Forums -||- My Blog -||- Excel Training Calendar
This is a shameless plug for my new book "RibbonX - Customizing the Office 2007 Ribbon". Find out more about it here!
Help keep VBAX clean! Use the 'Thread Tools' menu to mark your own threads solved!
That's what I thought. Thanks Ken
New to the forum? Check out our Introductions section to get to know some of the members here. Feel free to tell us a little about yourself as well.
What is the correct syntax for that? I tried something along those lines but it didn't seem to work. Thanks guys and congrats Ken just read about your MVP status.
What do you have now?
All you need to do is use your excel application variable in place of "Application" from John's post..like if xlApp is the variable...
[vba]
Sub Testme()
Dim xlApp As Object
Set xlApp = CreateObject("Excel.Application")
xlApp.DisplayAlerts = False
' Check
Debug.Print xlApp.DisplayAlerts
' Reset
xlApp.DisplayAlerts = True
'Check Again
Debug.Print xlApp.DisplayAlerts
' Clean up
Set xlApp = Nothing
End Sub
[/vba]
Hope this helps
New to the forum? Check out our Introductions section to get to know some of the members here. Feel free to tell us a little about yourself as well.
Posting Permissions
Reply With Quote
