Subscript out of range error
 

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

Subscript out of range error
 

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


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

--
If this post helps click Yes
---------------
Jacob Skaria


"Ken Warthen" wrote:

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

Subscript out of range error
 

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.
--
HTH...

Jim Thomlinson


"Ken Warthen" wrote:

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

Subscript out of range error
 

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" wrote:

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


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

--
If this post helps click Yes
---------------
Jacob Skaria


"Ken Warthen" wrote:

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

Subscript out of range error
 

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
---------------
Jacob Skaria


"Ken Warthen" wrote:


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" wrote:

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


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

--
If this post helps click Yes
---------------
Jacob Skaria


"Ken Warthen" wrote:

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

Subscript out of range error
 

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" wrote:

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
---------------
Jacob Skaria


"Ken Warthen" wrote:


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" wrote:

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


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

--
If this post helps click Yes
---------------
Jacob Skaria


"Ken Warthen" wrote:

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

Subscript out of range error
 

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

ThisWorkbook.Worksheets("Price Groups").Visible = True
--
If this post helps click Yes
---------------
Jacob Skaria


"Ken Warthen" wrote:


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" wrote:

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
---------------
Jacob Skaria


"Ken Warthen" wrote:


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" wrote:

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


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

--
If this post helps click Yes
---------------
Jacob Skaria


"Ken Warthen" wrote:

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

Subscript out of range error
 

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)



On Fri, 15 May 2009 10:45:01 -0700, Ken Warthen
wrote:


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" wrote:

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
---------------
Jacob Skaria


"Ken Warthen" wrote:


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" wrote:

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


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

--
If this post helps click Yes
---------------
Jacob Skaria


"Ken Warthen" wrote:

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