asked on

Run Time Error 9 - Subscript out of Range

Hi Experts

I cannot see the wood for the trees as i am getting "Run Time Error 9 - Subscript out of Range" on the following macro....

on line Workbooks("Apples@AShow2013.xls").Activate

The above work book is open when i run the macro below.......

Private Sub Auto_Open()
Dim dtmModifiedDate As Date

Application.ScreenUpdating = False
Application.Calculation = xlCalculationManual
AddIns("Analysis ToolPak").Installed = True
Application.Workbooks.Open "\\mydocuments\XXXX2013.xls", , True
Workbooks("Apples@AShow2013.xls").Activate
dtmModifiedDate = FileDateTime("\\mydocuments\XXXX2013.xls")
Sheets("Sheet1").Unprotect
Range("LastUpdate").Value = dtmModifiedDate
With Sheet1
    .Protect UserInterfaceOnly:=True
    .EnableOutlining = True
End With
Application.Calculation = xlCalculationAutomatic
Application.ScreenUpdating = True
End Sub

Open in new window

SOLUTION

Rgonzo1971

membership

Create a free account to see this answer

Signing up is free and takes 30 seconds. No credit card required.

See answer

[ fanpages ]

Hi,

Rgonzo1971:

At which line do you have the error?

route217:

on line Workbooks("Apples@AShow2013.xls").Activate

---

Is the workbook "Apples@AShow2013.xls" open within the same instance (session) of Microsoft Excel that the code is running within?

What is the name of the workbook where the Auto_Open() event code is stored?

PS. Was your other question a "Script out of range" error, or a "Subscript out of range" error?

[ https://www.experts-exchange.com/questions/28177187/Personal-xlsb-macro-not-running-when-workbook-is-open.html ]

BFN,

fp.

SOLUTION

Martin Liss

membership

Create a free account to see this answer

Signing up is free and takes 30 seconds. No credit card required.

See answer

SOLUTION

[ fanpages ]

membership

Create a free account to see this answer

Signing up is free and takes 30 seconds. No credit card required.

See answer

Martin Liss

'Workbooks' is a collection and VBA only adds workbooks to that collection when they are opened from within the current workbook. It may be possible using APIs to find an already open workbook but I'm not sure how you would activate it.

ASKER CERTIFIED SOLUTION

[ fanpages ]

membership

Create a free account to see this answer

Signing up is free and takes 30 seconds. No credit card required.

See answer

Martin Liss

It is possible.

Is that a question?

[ fanpages ]

No. It's a statement of fact.

route217

ASKER

thanks... all experts...for the excllent feedback. ..apologies I could reply back sooner.

Martin Liss

You're welcome and I'm glad I was able to help.

Marty - MVP 2009 to 2013