Ischyros
03-11-2009, 06:27 AM
I have a shared workbook that is on a shared drive at work. This database runs fine for most people except one. This user recieves "subscript out of range run time error 9" with two sub routines. The difference between these subroutines and the others is they involve another workbook. Specifically, the sub routine below always gives the error 9.
I did some investigative work and found that the erros in both subroutines happen whenever I reference the non active workbook. Lines like "Workbooks("Not Active").Range("A1") = Workbooks("Active").Range("A1")" and "Workbooks("Not Active").Activate", error out.
Is there some weird property on his computer and/or his version of excel? I am missing something!
Sub Save_Project_List()
Dim Uname As String
Uname = Environ("UserName")
Workbooks("Project Resource Management Form").Activate
Sheets("Project List").Copy
Windows(ActiveWorkbook.Name).Activate
ActiveSheet.Unprotect
ActiveSheet.Shapes("Button 10").Delete
ActiveSheet.Shapes("Button 4").Delete
ActiveSheet.Shapes("Button 6").Delete
ActiveSheet.Shapes("Button 5").Delete
ActiveSheet.Shapes("Button 8").Delete
ActiveSheet.Shapes("Button 7").Delete
ActiveSheet.Shapes("Button 9").Delete
ActiveSheet.Shapes("Button 11").Delete
ActiveSheet.Shapes("CommandButton2").Delete
ActiveSheet.Shapes("CommandButton1").Delete
ActiveSheet.Shapes("CommandButton3").Delete
ActiveSheet.Shapes("Button 12").Delete
ActiveWorkbook.SaveAs FileName:= _
"C:\Documents and Settings\" + Uname + "\Desktop\Copy - Project List.xls", _
FileFormat:=xlNormal, Password:="", WriteResPassword:="", _
ReadOnlyRecommended:=False, CreateBackup:=False
End Sub
I did some investigative work and found that the erros in both subroutines happen whenever I reference the non active workbook. Lines like "Workbooks("Not Active").Range("A1") = Workbooks("Active").Range("A1")" and "Workbooks("Not Active").Activate", error out.
Is there some weird property on his computer and/or his version of excel? I am missing something!
Sub Save_Project_List()
Dim Uname As String
Uname = Environ("UserName")
Workbooks("Project Resource Management Form").Activate
Sheets("Project List").Copy
Windows(ActiveWorkbook.Name).Activate
ActiveSheet.Unprotect
ActiveSheet.Shapes("Button 10").Delete
ActiveSheet.Shapes("Button 4").Delete
ActiveSheet.Shapes("Button 6").Delete
ActiveSheet.Shapes("Button 5").Delete
ActiveSheet.Shapes("Button 8").Delete
ActiveSheet.Shapes("Button 7").Delete
ActiveSheet.Shapes("Button 9").Delete
ActiveSheet.Shapes("Button 11").Delete
ActiveSheet.Shapes("CommandButton2").Delete
ActiveSheet.Shapes("CommandButton1").Delete
ActiveSheet.Shapes("CommandButton3").Delete
ActiveSheet.Shapes("Button 12").Delete
ActiveWorkbook.SaveAs FileName:= _
"C:\Documents and Settings\" + Uname + "\Desktop\Copy - Project List.xls", _
FileFormat:=xlNormal, Password:="", WriteResPassword:="", _
ReadOnlyRecommended:=False, CreateBackup:=False
End Sub