| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Yet another undesired pop-up
Hi All......
I recorded a macro that works ok, except that with each sheet deletion, I get a pop-up that stops the macro execution until I manually acknowledge it. Oh, another annoyance is that if the sheet does not exist, the macro crashes....... Could anyone please help with either problem? Sub ClearProgram() 'Delete the unwanted sheets Sheets("q793complete").Select ActiveWindow.SelectedSheets.Delete Sheets("tblDPM").Select ActiveWindow.SelectedSheets.Delete Sheets("PartTrend").Select ActiveWindow.SelectedSheets.Delete Sheets("q794Trends").Select ActiveWindow.SelectedSheets.Delete Sheets("q261Defects").Select ActiveWindow.SelectedSheets.Delete Sheets("q261Parts").Select ActiveWindow.SelectedSheets.Delete 'Clear the NUMBERBASE range on the DPMcalcs sheet Sheets("DPMCalcs").Select ActiveWindow.ScrollColumn = 1 Application.GoTo Reference:="NUMBERBASE" Selection.ClearContents Sheets("Main").Select Range("C8").Select End Sub TIA Vaya con Dios, Chuck, CABGx3 |
|
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Yet another undesired pop-up
CLR,
You need to turn the warnings off. Try... Application.DisplayAlerts = False YOUR CODE HERE Application.DisplayAlerts = True HTH, Gary Brown "CLR" wrote: Hi All...... I recorded a macro that works ok, except that with each sheet deletion, I get a pop-up that stops the macro execution until I manually acknowledge it. Oh, another annoyance is that if the sheet does not exist, the macro crashes....... Could anyone please help with either problem? Sub ClearProgram() 'Delete the unwanted sheets Sheets("q793complete").Select ActiveWindow.SelectedSheets.Delete Sheets("tblDPM").Select ActiveWindow.SelectedSheets.Delete Sheets("PartTrend").Select ActiveWindow.SelectedSheets.Delete Sheets("q794Trends").Select ActiveWindow.SelectedSheets.Delete Sheets("q261Defects").Select ActiveWindow.SelectedSheets.Delete Sheets("q261Parts").Select ActiveWindow.SelectedSheets.Delete 'Clear the NUMBERBASE range on the DPMcalcs sheet Sheets("DPMCalcs").Select ActiveWindow.ScrollColumn = 1 Application.GoTo Reference:="NUMBERBASE" Selection.ClearContents Sheets("Main").Select Range("C8").Select End Sub TIA Vaya con Dios, Chuck, CABGx3 |
|
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Yet another undesired pop-up
Thanks very much Gary.......That took care of the first part, real fine.....
Vaya con Dios, Chuck, CABGx3 "Gary Brown" wrote: CLR, You need to turn the warnings off. Try... Application.DisplayAlerts = False YOUR CODE HERE Application.DisplayAlerts = True HTH, Gary Brown "CLR" wrote: Hi All...... I recorded a macro that works ok, except that with each sheet deletion, I get a pop-up that stops the macro execution until I manually acknowledge it. Oh, another annoyance is that if the sheet does not exist, the macro crashes....... Could anyone please help with either problem? Sub ClearProgram() 'Delete the unwanted sheets Sheets("q793complete").Select ActiveWindow.SelectedSheets.Delete Sheets("tblDPM").Select ActiveWindow.SelectedSheets.Delete Sheets("PartTrend").Select ActiveWindow.SelectedSheets.Delete Sheets("q794Trends").Select ActiveWindow.SelectedSheets.Delete Sheets("q261Defects").Select ActiveWindow.SelectedSheets.Delete Sheets("q261Parts").Select ActiveWindow.SelectedSheets.Delete 'Clear the NUMBERBASE range on the DPMcalcs sheet Sheets("DPMCalcs").Select ActiveWindow.ScrollColumn = 1 Application.GoTo Reference:="NUMBERBASE" Selection.ClearContents Sheets("Main").Select Range("C8").Select End Sub TIA Vaya con Dios, Chuck, CABGx3 |
|
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Yet another undesired pop-up
at the top of the code add
on error resume next When the sheet to delete is not found the code will just continue on the next line... HTH "CLR" wrote: Thanks very much Gary.......That took care of the first part, real fine..... Vaya con Dios, Chuck, CABGx3 "Gary Brown" wrote: CLR, You need to turn the warnings off. Try... Application.DisplayAlerts = False YOUR CODE HERE Application.DisplayAlerts = True HTH, Gary Brown "CLR" wrote: Hi All...... I recorded a macro that works ok, except that with each sheet deletion, I get a pop-up that stops the macro execution until I manually acknowledge it. Oh, another annoyance is that if the sheet does not exist, the macro crashes....... Could anyone please help with either problem? Sub ClearProgram() 'Delete the unwanted sheets Sheets("q793complete").Select ActiveWindow.SelectedSheets.Delete Sheets("tblDPM").Select ActiveWindow.SelectedSheets.Delete Sheets("PartTrend").Select ActiveWindow.SelectedSheets.Delete Sheets("q794Trends").Select ActiveWindow.SelectedSheets.Delete Sheets("q261Defects").Select ActiveWindow.SelectedSheets.Delete Sheets("q261Parts").Select ActiveWindow.SelectedSheets.Delete 'Clear the NUMBERBASE range on the DPMcalcs sheet Sheets("DPMCalcs").Select ActiveWindow.ScrollColumn = 1 Application.GoTo Reference:="NUMBERBASE" Selection.ClearContents Sheets("Main").Select Range("C8").Select End Sub TIA Vaya con Dios, Chuck, CABGx3 |
|
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Yet another undesired pop-up
Hmmmm.....I must be doing something wrong......when I did that, after adding
Gary's suggestion, the macro passed over that part of the code where it looked for the sheets I wanted to delete.........but then for some reason, it wiped out almost every other sheet in the book. Vaya con Dios, Chuck, CABGx3 "Jim Thomlinson" wrote: at the top of the code add on error resume next When the sheet to delete is not found the code will just continue on the next line... HTH "CLR" wrote: Thanks very much Gary.......That took care of the first part, real fine..... Vaya con Dios, Chuck, CABGx3 "Gary Brown" wrote: CLR, You need to turn the warnings off. Try... Application.DisplayAlerts = False YOUR CODE HERE Application.DisplayAlerts = True HTH, Gary Brown "CLR" wrote: Hi All...... I recorded a macro that works ok, except that with each sheet deletion, I get a pop-up that stops the macro execution until I manually acknowledge it. Oh, another annoyance is that if the sheet does not exist, the macro crashes....... Could anyone please help with either problem? Sub ClearProgram() 'Delete the unwanted sheets Sheets("q793complete").Select ActiveWindow.SelectedSheets.Delete Sheets("tblDPM").Select ActiveWindow.SelectedSheets.Delete Sheets("PartTrend").Select ActiveWindow.SelectedSheets.Delete Sheets("q794Trends").Select ActiveWindow.SelectedSheets.Delete Sheets("q261Defects").Select ActiveWindow.SelectedSheets.Delete Sheets("q261Parts").Select ActiveWindow.SelectedSheets.Delete 'Clear the NUMBERBASE range on the DPMcalcs sheet Sheets("DPMCalcs").Select ActiveWindow.ScrollColumn = 1 Application.GoTo Reference:="NUMBERBASE" Selection.ClearContents Sheets("Main").Select Range("C8").Select End Sub TIA Vaya con Dios, Chuck, CABGx3 |
|
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Yet another undesired pop-up
Here is a solution to your problem:
' Delete Summary sheet if it exists On Error Resume Next Application.DisplayAlerts = False Sheets("Summary").Delete On Error GoTo 0 The "Application.DisplayAlerts = False" will avoid the pop- up. The "On Error Reume Next" will avoid the error in case the sheet does not exist. Sergio Mendes -----Original Message----- Hi All...... I recorded a macro that works ok, except that with each sheet deletion, I get a pop-up that stops the macro execution until I manually acknowledge it. Oh, another annoyance is that if the sheet does not exist, the macro crashes....... Could anyone please help with either problem? Sub ClearProgram() 'Delete the unwanted sheets Sheets("q793complete").Select ActiveWindow.SelectedSheets.Delete Sheets("tblDPM").Select ActiveWindow.SelectedSheets.Delete Sheets("PartTrend").Select ActiveWindow.SelectedSheets.Delete Sheets("q794Trends").Select ActiveWindow.SelectedSheets.Delete Sheets("q261Defects").Select ActiveWindow.SelectedSheets.Delete Sheets("q261Parts").Select ActiveWindow.SelectedSheets.Delete 'Clear the NUMBERBASE range on the DPMcalcs sheet Sheets("DPMCalcs").Select ActiveWindow.ScrollColumn = 1 Application.GoTo Reference:="NUMBERBASE" Selection.ClearContents Sheets("Main").Select Range("C8").Select End Sub TIA Vaya con Dios, Chuck, CABGx3 . |
|
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Yet another undesired pop-up
Sorry... Wee need to clean up some of your delet code...
Change all of the deletes as follows: sheets("SheetName").delete instead of sheets("Sheetname").select activewindow.selectedsheets.delete My goof. "CLR" wrote: Hmmmm.....I must be doing something wrong......when I did that, after adding Gary's suggestion, the macro passed over that part of the code where it looked for the sheets I wanted to delete.........but then for some reason, it wiped out almost every other sheet in the book. Vaya con Dios, Chuck, CABGx3 "Jim Thomlinson" wrote: at the top of the code add on error resume next When the sheet to delete is not found the code will just continue on the next line... HTH "CLR" wrote: Thanks very much Gary.......That took care of the first part, real fine..... Vaya con Dios, Chuck, CABGx3 "Gary Brown" wrote: CLR, You need to turn the warnings off. Try... Application.DisplayAlerts = False YOUR CODE HERE Application.DisplayAlerts = True HTH, Gary Brown "CLR" wrote: Hi All...... I recorded a macro that works ok, except that with each sheet deletion, I get a pop-up that stops the macro execution until I manually acknowledge it. Oh, another annoyance is that if the sheet does not exist, the macro crashes....... Could anyone please help with either problem? Sub ClearProgram() 'Delete the unwanted sheets Sheets("q793complete").Select ActiveWindow.SelectedSheets.Delete Sheets("tblDPM").Select ActiveWindow.SelectedSheets.Delete Sheets("PartTrend").Select ActiveWindow.SelectedSheets.Delete Sheets("q794Trends").Select ActiveWindow.SelectedSheets.Delete Sheets("q261Defects").Select ActiveWindow.SelectedSheets.Delete Sheets("q261Parts").Select ActiveWindow.SelectedSheets.Delete 'Clear the NUMBERBASE range on the DPMcalcs sheet Sheets("DPMCalcs").Select ActiveWindow.ScrollColumn = 1 Application.GoTo Reference:="NUMBERBASE" Selection.ClearContents Sheets("Main").Select Range("C8").Select End Sub TIA Vaya con Dios, Chuck, CABGx3 |
|
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Yet another undesired pop-up
When it can't find the worksheet, it goes to the next line which basically
says DELETE THE CURRENT WORKSHEET. Use this syntax.. Sheets("q793complete").Delete That way, if it can't find the sheet, it moves on instead of doing a blind delete. HTH, Gary Brown "CLR" wrote: Hmmmm.....I must be doing something wrong......when I did that, after adding Gary's suggestion, the macro passed over that part of the code where it looked for the sheets I wanted to delete.........but then for some reason, it wiped out almost every other sheet in the book. Vaya con Dios, Chuck, CABGx3 "Jim Thomlinson" wrote: at the top of the code add on error resume next When the sheet to delete is not found the code will just continue on the next line... HTH "CLR" wrote: Thanks very much Gary.......That took care of the first part, real fine..... Vaya con Dios, Chuck, CABGx3 "Gary Brown" wrote: CLR, You need to turn the warnings off. Try... Application.DisplayAlerts = False YOUR CODE HERE Application.DisplayAlerts = True HTH, Gary Brown "CLR" wrote: Hi All...... I recorded a macro that works ok, except that with each sheet deletion, I get a pop-up that stops the macro execution until I manually acknowledge it. Oh, another annoyance is that if the sheet does not exist, the macro crashes....... Could anyone please help with either problem? Sub ClearProgram() 'Delete the unwanted sheets Sheets("q793complete").Select ActiveWindow.SelectedSheets.Delete Sheets("tblDPM").Select ActiveWindow.SelectedSheets.Delete Sheets("PartTrend").Select ActiveWindow.SelectedSheets.Delete Sheets("q794Trends").Select ActiveWindow.SelectedSheets.Delete Sheets("q261Defects").Select ActiveWindow.SelectedSheets.Delete Sheets("q261Parts").Select ActiveWindow.SelectedSheets.Delete 'Clear the NUMBERBASE range on the DPMcalcs sheet Sheets("DPMCalcs").Select ActiveWindow.ScrollColumn = 1 Application.GoTo Reference:="NUMBERBASE" Selection.ClearContents Sheets("Main").Select Range("C8").Select End Sub TIA Vaya con Dios, Chuck, CABGx3 |
|
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Yet another undesired pop-up
Thank you all......Gary, Jim, and Sergio.......with you guys help, it all
works super-fine now..... Thanks again Vaya con Dios, Chuck, CABGx3 "CLR" wrote: Hi All...... I recorded a macro that works ok, except that with each sheet deletion, I get a pop-up that stops the macro execution until I manually acknowledge it. Oh, another annoyance is that if the sheet does not exist, the macro crashes....... Could anyone please help with either problem? Sub ClearProgram() 'Delete the unwanted sheets Sheets("q793complete").Select ActiveWindow.SelectedSheets.Delete Sheets("tblDPM").Select ActiveWindow.SelectedSheets.Delete Sheets("PartTrend").Select ActiveWindow.SelectedSheets.Delete Sheets("q794Trends").Select ActiveWindow.SelectedSheets.Delete Sheets("q261Defects").Select ActiveWindow.SelectedSheets.Delete Sheets("q261Parts").Select ActiveWindow.SelectedSheets.Delete 'Clear the NUMBERBASE range on the DPMcalcs sheet Sheets("DPMCalcs").Select ActiveWindow.ScrollColumn = 1 Application.GoTo Reference:="NUMBERBASE" Selection.ClearContents Sheets("Main").Select Range("C8").Select End Sub TIA Vaya con Dios, Chuck, CABGx3 |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| On exit from edit, Excel jumps to remote undesired cell. | Excel Discussion (Misc queries) | |||
| Undesired links: Pasting from one excel document to another | Links and Linking in Excel | |||
| undesired "breaks" between worksheets | Excel Discussion (Misc queries) | |||
| coding worksheets for undesired selections | Excel Programming | |||
| Undesired Rounding in VBA; accumulating values | Excel Programming | |||
Linear Mode
