run-time error '9': subscript out of range

[swain.s]

i get the message wheh i try to run this

Private Sub Workbook_BeforeClose(Cancel As Boolean)
Workbooks("DATA.XLS").Close SaveChanges:=False
End Sub

 

[Chip Pearson]

An error 9 indicates that you are attempting to access an item in a
group when that item doesn't exist. Your code will throw an error 9 if
there is no open workbook with the name "Data.xls". If you want to
ignore the error, simply put "On Error Resume Next" on a line of code
just above the code that closes the workbook. Note that using Resume
to ignore an error in no way "fixes" the error; it merely tells VBA to
ignore the error, but the error can have unintended side effects.

Cordially,
Chip Pearson
Microsoft MVP 1998 - 2010
Pearson Software Consulting, LLC
www.cpearson.com
[email on web site]

 

[Mike H]

Hi,

That would happen if you didn't have an open workbook called data.xls
Are you sure the name is correct?
Are there any rogue spaces in the workbook name?

Mike

 

[swain.s]

the data.xls workbook is open
the name is correct
there are no rogue spaces in the workbook name

 

[Dave Peterson]

The question in your previous thread was resolved when you noticed that the
extension wasn't .xls, it was .xlsm (or .xlsx).

Look to see if that continues to be a problem for you.