| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
before close runtime error 9
I am trying to get the code to delete a sheet, save, then close the
workbook. Right now it deletes the sheet, saves but then I get a Run time Error "9" subscript out of range. I have the code below. Please tell me what I am doing wrong. Private Sub workbook_beforeclose(cancel As Boolean) Application.DisplayAlerts = False ActiveWorkbook.Sheets("sheet1").Delete Application.DisplayAlerts = True ActiveWorkbook.Save ActiveWorkbook.Close End Sub |
|
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
before close runtime error 9
How many sheets in the workbook?
"stewdizzle" wrote: I am trying to get the code to delete a sheet, save, then close the workbook. Right now it deletes the sheet, saves but then I get a Run time Error "9" subscript out of range. I have the code below. Please tell me what I am doing wrong. Private Sub workbook_beforeclose(cancel As Boolean) Application.DisplayAlerts = False ActiveWorkbook.Sheets("sheet1").Delete Application.DisplayAlerts = True ActiveWorkbook.Save ActiveWorkbook.Close End Sub |
|
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
before close runtime error 9
Also try Workbooks(ThisWorkbook).Close
"stewdizzle" wrote: I am trying to get the code to delete a sheet, save, then close the workbook. Right now it deletes the sheet, saves but then I get a Run time Error "9" subscript out of range. I have the code below. Please tell me what I am doing wrong. Private Sub workbook_beforeclose(cancel As Boolean) Application.DisplayAlerts = False ActiveWorkbook.Sheets("sheet1").Delete Application.DisplayAlerts = True ActiveWorkbook.Save ActiveWorkbook.Close End Sub |
|
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
before close runtime error 9
two worksheets one named budget and the other is named sheet1
On Jan 5, 10:23 pm, JLGWhiz wrote: Also try Workbooks(ThisWorkbook).Close "stewdizzle" wrote: I am trying to get the code to delete a sheet, save, then close the workbook. Right now it deletes the sheet, saves but then I get a Run time Error "9" subscript out of range. I have the code below. Please tell me what I am doing wrong. Private Sub workbook_beforeclose(cancel As Boolean) Application.DisplayAlerts = False ActiveWorkbook.Sheets("sheet1").Delete Application.DisplayAlerts = True ActiveWorkbook.Save ActiveWorkbook.Close End Sub |
|
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
before close runtime error 9
Using Workbooks(ThisWorkbook).Close gets rid of the error 9 but now i
get error 13 type mismatch. Everything works up until the close command. Here is the code. Private Sub workbook_beforeclose(cancel As Boolean) Application.DisplayAlerts = False ActiveWorkbook.Sheets("sheet1").Delete Application.DisplayAlerts = True ActiveWorkbook.Save Workbooks(ThisWorkbook).Close End Sub On Jan 6, 10:08 am, "stewdizzle" wrote: two worksheets one named budget and the other is named sheet1 On Jan 5, 10:23 pm, JLGWhiz wrote: Also try Workbooks(ThisWorkbook).Close "stewdizzle" wrote: I am trying to get the code to delete a sheet, save, then close the workbook. Right now it deletes the sheet, saves but then I get a Run time Error "9" subscript out of range. I have the code below. Please tell me what I am doing wrong. Private Sub workbook_beforeclose(cancel As Boolean) Application.DisplayAlerts = False ActiveWorkbook.Sheets("sheet1").Delete Application.DisplayAlerts = True ActiveWorkbook.Save ActiveWorkbook.Close End Sub |
|
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
before close runtime error 9
I'm not sure if this means anything but application.quit works fine.
for some reason i can't get it to just close the workbook. On Jan 6, 10:16 am, "stewdizzle" wrote: Using Workbooks(ThisWorkbook).Close gets rid of the error 9 but now i get error 13 type mismatch. Everything works up until the close command. Here is the code. Private Sub workbook_beforeclose(cancel As Boolean) Application.DisplayAlerts = False ActiveWorkbook.Sheets("sheet1").Delete Application.DisplayAlerts = True ActiveWorkbook.Save Workbooks(ThisWorkbook).Close End Sub On Jan 6, 10:08 am, "stewdizzle" wrote: two worksheets one named budget and the other is named sheet1 On Jan 5, 10:23 pm, JLGWhiz wrote: Also try Workbooks(ThisWorkbook).Close "stewdizzle" wrote: I am trying to get the code to delete a sheet, save, then close the workbook. Right now it deletes the sheet, saves but then I get a Run time Error "9" subscript out of range. I have the code below. Please tell me what I am doing wrong. Private Sub workbook_beforeclose(cancel As Boolean) Application.DisplayAlerts = False ActiveWorkbook.Sheets("sheet1").Delete Application.DisplayAlerts = True ActiveWorkbook.Save ActiveWorkbook.Close End Sub |
|
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
before close runtime error 9
It appears that you have already told Excel to close the workbook
as you are using the beforeclose event. So, try eliminating the last line of your code and see what happens. -- Jim Cone San Francisco, USA http://www.realezsites.com/bus/primitivesoftware "stewdizzle" wrote in message I'm not sure if this means anything but application.quit works fine. for some reason i can't get it to just close the workbook. On Jan 6, 10:16 am, "stewdizzle" wrote: Using Workbooks(ThisWorkbook).Close gets rid of the error 9 but now i get error 13 type mismatch. Everything works up until the close command. Here is the code. Private Sub workbook_beforeclose(cancel As Boolean) Application.DisplayAlerts = False ActiveWorkbook.Sheets("sheet1").Delete Application.DisplayAlerts = True ActiveWorkbook.Save Workbooks(ThisWorkbook).Close End Sub On Jan 6, 10:08 am, "stewdizzle" wrote: two worksheets one named budget and the other is named sheet1 On Jan 5, 10:23 pm, JLGWhiz wrote: Also try Workbooks(ThisWorkbook).Close "stewdizzle" wrote: I am trying to get the code to delete a sheet, save, then close the workbook. Right now it deletes the sheet, saves but then I get a Run time Error "9" subscript out of range. I have the code below. Please tell me what I am doing wrong. Private Sub workbook_beforeclose(cancel As Boolean) Application.DisplayAlerts = False ActiveWorkbook.Sheets("sheet1").Delete Application.DisplayAlerts = True ActiveWorkbook.Save ActiveWorkbook.Close End Sub |
|
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
before close runtime error 9
Thanks for catching it. That did the trick.
On Jan 6, 1:01 pm, "Jim Cone" wrote: It appears that you have already told Excel to close the workbook as you are using the beforeclose event. So, try eliminating the last line of your code and see what happens. -- Jim Cone San Francisco, USAhttp://www.realezsites.com/bus/primitivesoftware "stewdizzle" wrote in message I'm not sure if this means anything but application.quit works fine. for some reason i can't get it to just close the workbook. On Jan 6, 10:16 am, "stewdizzle" wrote: Using Workbooks(ThisWorkbook).Close gets rid of the error 9 but now i get error 13 type mismatch. Everything works up until the close command. Here is the code. Private Sub workbook_beforeclose(cancel As Boolean) Application.DisplayAlerts = False ActiveWorkbook.Sheets("sheet1").Delete Application.DisplayAlerts = True ActiveWorkbook.Save Workbooks(ThisWorkbook).Close End Sub On Jan 6, 10:08 am, "stewdizzle" wrote: two worksheets one named budget and the other is named sheet1 On Jan 5, 10:23 pm, JLGWhiz wrote: Also try Workbooks(ThisWorkbook).Close "stewdizzle" wrote: I am trying to get the code to delete a sheet, save, then close the workbook. Right now it deletes the sheet, saves but then I get a Run time Error "9" subscript out of range. I have the code below. Please tell me what I am doing wrong. Private Sub workbook_beforeclose(cancel As Boolean) Application.DisplayAlerts = False ActiveWorkbook.Sheets("sheet1").Delete Application.DisplayAlerts = True ActiveWorkbook.Save ActiveWorkbook.Close End Sub |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Runtime Error - Subscript out of range despite On Error statement | Excel Programming | |||
| runtime error: syntax error or access violation | Excel Programming | |||
| Excel 2003 Macro Error - Runtime error 1004 | Excel Discussion (Misc queries) | |||
| naming tab macro error runtime error 1004 | Excel Programming | |||
| Syntax Error Runtime Error '424' Object Required | Excel Programming | |||
Linear Mode
