Subscript out of range error

[Ken Warthen]

I have an Excel 2003 format spreadsheet with quite a bit of VBA code to
validate user entries, create reports, and as code behind several userforms.
I've got everything working fine, except it the user has a second spreadsheet
open in which case my login userform fails with an error 9, subscript out of
range error message on the following line.

Workbooks(1).Worksheet("Price Plan").Visible=True

Any idea on why my code is failing, and how it can be fixed?

TIA,

Ken

 

[Jacob Skaria]

Try the below. Specify the workbook..in between the quotes


Workbooks("<workbookname>").Sheets("Price Plan").Visible = True

 

[Jim Thomlinson]

Your issue is probably with Workbooks(1). You are referencing the book by
it's index number and generally spaeking that is a bad idea. With boks being
opened and closed there is no effective way to keep track of the indexes. I
assume that the sheet Price Plan is in the book running the code. Assuming
that to be true ThisWorkbook always refers to the book running the code so...

ThisWorkbook.Worksheet("Price Plan").Visible=True

You could also probably use activeworkbook but that is a lot more prone to
error since the book running the code is not neccessarily the active workbook.

 

[Ken Warthen]

Jacob,

Thanks for the help. I changed the Workbooks reference to the following.

Workbooks("Drink Plan").Worksheets("Price Groups").Visible = True

The code continues to fail.

Ken

 

[Jacob Skaria]

What is the error?? Is should be "Drink Plan.xls"

Workbooks("Drink Plan.xls").Activate
Workbooks("Drink Plan.xls").Worksheets("Price Groups").Visible = True


If this post helps click Yes

 

[Ken Warthen]

Jacob,

Adding the xls extension did the trick. If an end user happens to change
the file name will the code then fail again?

Ken

 

[Jacob Skaria]

Replace with the below if you are using this code from the User form of Drink
Plan.xls

ThisWorkbook.Worksheets("Price Groups").Visible = True

 

[Chip Pearson]

Adding the xls extension did the trick. If an end user happens to change

the file name will the code then fail again?

As a general rule, you should always use the ".xls" file extension
when using the workbook name with the Workbooks collection. It will
always work. Omitting the ".xls" extension will work if the user has
the "Hide extensions of known file types" option in effect. This is a
Windows option, not an Excel option.

See http://www.cpearson.com/excel/FileExtensions.aspx for more
information.

Cordially,
Chip Pearson
Microsoft Most Valuable Professional
Excel Product Group, 1998 - 2009
Pearson Software Consulting, LLC
www.cpearson.com
(email on web site)