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

? 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

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

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

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

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

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