Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Subscript out of range Error | Excel Programming | |||
Runtime Error - Subscript out of range despite On Error statement | Excel Programming | |||
Subscript out of range error - save copy error | Excel Programming | |||
Subscript out of range error - save copy error | Excel Programming | |||
Type Mismatch error & subscript out of range error | Excel Programming |