Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Run Time Error 9
Could anyone please explain why would I get a Run Time Error 9 with
this code? Sub submit_roster() Dim myName As String Dim myFile As String Dim myWeek As String Application.ScreenUpdating = False Application.DisplayAlerts = False myFile = ActiveWorkbook.Name myName = ActiveSheet.Name myDate = Range("C5").Value myName = myName & ". " & myDate If Range("C5").Value = "" Then MsgBox ("Pick a week to submit from the dropdown list.") Range("C5").Select Else Range("C10:C192").Select ActiveSheet.Unprotect Password:="richmond" Selection.locked = True ActiveSheet.Protect Password:="richmond" Range("C5").Select myCheck = vbIgnore myCheck = MsgBox("Your " & myDate & " roster is now saved. Are you ready to finalize by sending it to the league?", vbYesNo) If myCheck = vbYes Then Workbooks.Add Sheets(Array("Sheet2", "Sheet3")).Select ActiveWindow.SelectedSheets.Delete ActiveWorkbook.SaveAs myName & ".xls" Windows(myFile).Activate Cells.Copy Windows(myName).Activate ActiveSheet.Paste ActiveSheet.Name = Range("C5").Value & " Roster Submission" Range("C5").Select Selection.locked = True ActiveSheet.Protect Password:="richmond" ActiveWorkbook.Save MsgBox ("The file '" & myName & "' has been created and saved in " & ActiveWorkbook.Path & ". Please attach it in an email and send it to the league distribution list.") Else MsgBox ("Your submission file was not created.") Range("C5").Select End If End If Application.ScreenUpdating = True End Sub |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Run Time Error 9
sure,
when you do myName = myName & ". " & myDate myname has a value like "abcd.xls" whatever you append to it will be after the .xls so myname will be like "abcd.xls.20040831" you need to do myName = left(myname,len(myname)-4) & mydate & ".xls" then change ActiveWorkbook.SaveAs myName & ".xls" to ActiveWorkbook.SaveAs myName -- Regards, Tom Ogilvy "Bankonit9" wrote in message om... Could anyone please explain why would I get a Run Time Error 9 with this code? Sub submit_roster() Dim myName As String Dim myFile As String Dim myWeek As String Application.ScreenUpdating = False Application.DisplayAlerts = False myFile = ActiveWorkbook.Name myName = ActiveSheet.Name myDate = Range("C5").Value myName = myName & ". " & myDate If Range("C5").Value = "" Then MsgBox ("Pick a week to submit from the dropdown list.") Range("C5").Select Else Range("C10:C192").Select ActiveSheet.Unprotect Password:="richmond" Selection.locked = True ActiveSheet.Protect Password:="richmond" Range("C5").Select myCheck = vbIgnore myCheck = MsgBox("Your " & myDate & " roster is now saved. Are you ready to finalize by sending it to the league?", vbYesNo) If myCheck = vbYes Then Workbooks.Add Sheets(Array("Sheet2", "Sheet3")).Select ActiveWindow.SelectedSheets.Delete ActiveWorkbook.SaveAs myName & ".xls" Windows(myFile).Activate Cells.Copy Windows(myName).Activate ActiveSheet.Paste ActiveSheet.Name = Range("C5").Value & " Roster Submission" Range("C5").Select Selection.locked = True ActiveSheet.Protect Password:="richmond" ActiveWorkbook.Save MsgBox ("The file '" & myName & "' has been created and saved in " & ActiveWorkbook.Path & ". Please attach it in an email and send it to the league distribution list.") Else MsgBox ("Your submission file was not created.") Range("C5").Select End If End If Application.ScreenUpdating = True End Sub |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Run Time Error 9
Try this
Dim mydate As Long Dim mycheck As Integer Dave "Bankonit9" wrote in message om... Could anyone please explain why would I get a Run Time Error 9 with this code? Sub submit_roster() Dim myName As String Dim myFile As String Dim myWeek As String Application.ScreenUpdating = False Application.DisplayAlerts = False myFile = ActiveWorkbook.Name myName = ActiveSheet.Name myDate = Range("C5").Value myName = myName & ". " & myDate If Range("C5").Value = "" Then MsgBox ("Pick a week to submit from the dropdown list.") Range("C5").Select Else Range("C10:C192").Select ActiveSheet.Unprotect Password:="richmond" Selection.locked = True ActiveSheet.Protect Password:="richmond" Range("C5").Select myCheck = vbIgnore myCheck = MsgBox("Your " & myDate & " roster is now saved. Are you ready to finalize by sending it to the league?", vbYesNo) If myCheck = vbYes Then Workbooks.Add Sheets(Array("Sheet2", "Sheet3")).Select ActiveWindow.SelectedSheets.Delete ActiveWorkbook.SaveAs myName & ".xls" Windows(myFile).Activate Cells.Copy Windows(myName).Activate ActiveSheet.Paste ActiveSheet.Name = Range("C5").Value & " Roster Submission" Range("C5").Select Selection.locked = True ActiveSheet.Protect Password:="richmond" ActiveWorkbook.Save MsgBox ("The file '" & myName & "' has been created and saved in " & ActiveWorkbook.Path & ". Please attach it in an email and send it to the league distribution list.") Else MsgBox ("Your submission file was not created.") Range("C5").Select End If End If Application.ScreenUpdating = True End Sub |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Run Time Error 9
Thank you for your reply Tom.
One question. If I set myName to the activesheet name, why would it end in .xls? Wouldnt that only be the case if i set myName to the workbooks name, and not the sheet? *** Sent via Developersdex http://www.developersdex.com *** Don't just participate in USENET...get rewarded for it! |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Run Time Error 9
You are correct - it was the variable above that was being assigned to the
workbook name. Trying to scroll through your code in a small window, looking for assignments that might raise such an error . . . well, led to an incorrect conclusion. You haven't said what line is highlighted when you get the error. When you get the error, query the value of the argument to the collection and see if it matches anything. A subscript out of range error is usually caused by a name, used as an index to a collection, which doesn't match any members of the collection. Such as the workbooks collection, worksheets collection and so forth. You should examine such variables as myName and the others that you are using as arguments to index to the collection. -- Regards, Tom Ogilvy "Benjamin Kochanski" wrote in message ... Thank you for your reply Tom. One question. If I set myName to the activesheet name, why would it end in .xls? Wouldnt that only be the case if i set myName to the workbooks name, and not the sheet? *** Sent via Developersdex http://www.developersdex.com *** Don't just participate in USENET...get rewarded for it! |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Run Time Error 9
Thanks Dave.
I somehow forgot to Dim those guys...wouldnt I want to Dim myCheck as a vbMsgBoxResult, rather than as an integer? *** Sent via Developersdex http://www.developersdex.com *** Don't just participate in USENET...get rewarded for it! |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Visual Basic Error Run Time Error, Type Mismatch | Excel Discussion (Misc queries) | |||
Run time error 1004, General ODBC error | New Users to Excel | |||
Befuddled with For Next Loop ------ Run - Time Error '13' Type Mismatch Error | Excel Programming | |||
Code Error - Run Time Error 5 (Disable Cut, Copy & Paste) | Excel Programming | |||
Run-time error '11' & Run-time error '1004' | Excel Programming |