|
activating correct worksheet
Hi
The code Worksheets(€śNames€ť).Activate or Select brings up €śerror 9 Subscript out of range€ť if sitting in the €śNames€ť Worksheet, but will activate if sitting in any other sheet, why? What am I missing? I just want to be sure the Formatting sub procedure called up runs on the correct sheet. TIA -- Thank you Regards Aussie Bob C. Using Windows XP Home + Office 2003 Pro |
activating correct worksheet
? activesheet.Name
Sheet2 Worksheets("Sheet2").Activate in the immediate window did not raise any error. What you say is very strange. -- Regards, Tom Ogilvy "Robert Christie" wrote in message ... Hi The code Worksheets("Names").Activate or Select brings up "error 9 Subscript out of range" if sitting in the "Names" Worksheet, but will activate if sitting in any other sheet, why? What am I missing? I just want to be sure the Formatting sub procedure called up runs on the correct sheet. TIA -- Thank you Regards Aussie Bob C. Using Windows XP Home + Office 2003 Pro |
activating correct worksheet
Hi Robert,
Your code works fine for me also. If I define a range as "Names" I get a "Type mismatch" message. Thereafter, surprisingly, the defined name seems fine and your code still works. But do you have such a name? Regards, Peter T "Robert Christie" wrote in message ... Hi The code Worksheets("Names").Activate or Select brings up "error 9 Subscript out of range" if sitting in the "Names" Worksheet, but will activate if sitting in any other sheet, why? What am I missing? I just want to be sure the Formatting sub procedure called up runs on the correct sheet. TIA -- Thank you Regards Aussie Bob C. Using Windows XP Home + Office 2003 Pro |
activating correct worksheet
Hi Peter
No ranges defined as "Names" in workbook. Thanks for the post Regards Bob C. "Peter T" wrote: Hi Robert, Your code works fine for me also. If I define a range as "Names" I get a "Type mismatch" message. Thereafter, surprisingly, the defined name seems fine and your code still works. But do you have such a name? Regards, Peter T "Robert Christie" wrote in message ... Hi The code Worksheets("Names").Activate or Select brings up "error 9 Subscript out of range" if sitting in the "Names" Worksheet, but will activate if sitting in any other sheet, why? What am I missing? I just want to be sure the Formatting sub procedure called up runs on the correct sheet. TIA -- Thank you Regards Aussie Bob C. Using Windows XP Home + Office 2003 Pro |
activating correct worksheet
Hi Tom & Peter
Just replaced the Line "Worksheets("Names").Activate" with "Sheets("Names").Select" and it selects "Names" wether Names worksheet is ActiveSheet or not. Strange very strange. Thank you both for your posts. Regards Aussie Bob C. "Tom Ogilvy" wrote: ? activesheet.Name Sheet2 Worksheets("Sheet2").Activate in the immediate window did not raise any error. What you say is very strange. -- Regards, Tom Ogilvy "Robert Christie" wrote in message ... Hi The code Worksheets("Names").Activate or Select brings up "error 9 Subscript out of range" if sitting in the "Names" Worksheet, but will activate if sitting in any other sheet, why? What am I missing? I just want to be sure the Formatting sub procedure called up runs on the correct sheet. TIA -- Thank you Regards Aussie Bob C. Using Windows XP Home + Office 2003 Pro |
activating correct worksheet
Found I could duplicate your original problem if trying to activate a Macro
sheet, ie one created with Ctrl-F11. Sub Test() Sheets.Add Type:=xlExcel4MacroSheet For Each sh In ActiveWorkbook.Sheets Debug.Print TypeName(sh), sh.Type, sh.Name If sh.Type = xlExcel4MacroSheet Then s = sh.Name Next Worksheets(s).Activate 'fails Sheets(s).Activate End Sub Regards, Peter "Robert Christie" wrote in message ... Hi Peter No ranges defined as "Names" in workbook. Thanks for the post Regards Bob C. "Peter T" wrote: Hi Robert, Your code works fine for me also. If I define a range as "Names" I get a "Type mismatch" message. Thereafter, surprisingly, the defined name seems fine and your code still works. But do you have such a name? Regards, Peter T "Robert Christie" wrote in message ... Hi The code Worksheets("Names").Activate or Select brings up "error 9 Subscript out of range" if sitting in the "Names" Worksheet, but will activate if sitting in any other sheet, why? What am I missing? I just want to be sure the Formatting sub procedure called up runs on the correct sheet. TIA -- Thank you Regards Aussie Bob C. Using Windows XP Home + Office 2003 Pro |
activating correct worksheet
Found I could duplicate your original problem if trying to activate a Macro
sheet, ie one created with Ctrl-F11. Sub Test() Sheets.Add Type:=xlExcel4MacroSheet For Each sh In ActiveWorkbook.Sheets Debug.Print TypeName(sh), sh.Type, sh.Name If sh.Type = xlExcel4MacroSheet Then s = sh.Name Next Worksheets(s).Activate 'fails Sheets(s).Activate End Sub Regards, Peter "Robert Christie" wrote in message ... Hi Peter No ranges defined as "Names" in workbook. Thanks for the post Regards Bob C. "Peter T" wrote: Hi Robert, Your code works fine for me also. If I define a range as "Names" I get a "Type mismatch" message. Thereafter, surprisingly, the defined name seems fine and your code still works. But do you have such a name? Regards, Peter T "Robert Christie" wrote in message ... Hi The code Worksheets("Names").Activate or Select brings up "error 9 Subscript out of range" if sitting in the "Names" Worksheet, but will activate if sitting in any other sheet, why? What am I missing? I just want to be sure the Formatting sub procedure called up runs on the correct sheet. TIA -- Thank you Regards Aussie Bob C. Using Windows XP Home + Office 2003 Pro |
| All times are GMT +1. The time now is 02:28 AM. |
Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
ExcelBanter.com