Reliably get sheet 1 of the active workbook
 

I have some VBA code that does the following:

Option Compare Text

for i = 1 to ActiveWorkBook.Worksheets(i)
if ActiveWorkBook.Worksheets(i).CodeName = "Sheet1" then
set ws = ActiveWorkBook.Worksheets(i)
exit for
end if
next i

The problem is that this code doesn't always return as expected. In the
debuger, it always works, when run from an Add-In menu item, it works about
10% of the time.

Any ideas,
Thanks,
Mike.

Reliably get sheet 1 of the active workbook
 

Hi

Not sure exactly what you are trying to do, but try this .
You may be able to modify to work as you want.

For i = 1 To ActiveWorkbook.Worksheets.Count
If ActiveWorkbook.Worksheets(i).CodeName = "Sheet1" Then
Set ws = ActiveWorkbook.Worksheets(i)
ws.Activate
Exit For
End If
Next i

HTH

Ken


"Michael D. Ober" wrote in message
ink.net...
I have some VBA code that does the following:

Option Compare Text

for i = 1 to ActiveWorkBook.Worksheets(i)
if ActiveWorkBook.Worksheets(i).CodeName = "Sheet1" then
set ws = ActiveWorkBook.Worksheets(i)
exit for
end if
next i

The problem is that this code doesn't always return as expected. In the
debuger, it always works, when run from an Add-In menu item, it works
about
10% of the time.

Any ideas,
Thanks,
Mike.





---
Outgoing mail is certified Virus Free.
Checked by AVG anti-virus system (http://www.grisoft.com).
Version: 6.0.525 / Virus Database: 322 - Release Date: 09/10/2003

Reliably get sheet 1 of the active workbook
 

I would expect it to fail 100% of the time, unless you set i to
something 0 before the for i = ... line (since Worksheets(i) will
return a subscript out of range error if i=0).

How about:

Dim ws As Worksheet
Dim found As Boolean
For Each ws In ActiveWorkbook.Worksheets
If ws.CodeName = "Sheet1" Then
found = True
Exit For
End If
Next ws
If found Then
'do stuff, e.g.,
MsgBox ws.Name
Else
MsgBox "No worksheets found with codename Sheet1"
End If


In article . net,
"Michael D. Ober" wrote:

I have some VBA code that does the following:

Option Compare Text

for i = 1 to ActiveWorkBook.Worksheets(i)
if ActiveWorkBook.Worksheets(i).CodeName = "Sheet1" then
set ws = ActiveWorkBook.Worksheets(i)
exit for
end if
next i

The problem is that this code doesn't always return as expected. In the
debuger, it always works, when run from an Add-In menu item, it works about
10% of the time.

Any ideas,
Thanks,
Mike.

Reliably get sheet 1 of the active workbook
 

I just tried this and it still didn't work. After putting in some msgbox
statements, I discovered that the "CodeName" property doesn't exist until
you enter the VBE environment. Your comment about using for each ... next
instead of for i ... next is valid in general. However, I found that a
workbook will always have at least one worksheet, thus the for i ... next
does work. I did switch to for each ... next because it makes the code
shorter and easier to read.

Thanks,
Mike Ober.

"J.E. McGimpsey" wrote in message
...
I would expect it to fail 100% of the time, unless you set i to
something 0 before the for i = ... line (since Worksheets(i) will
return a subscript out of range error if i=0).

How about:

Dim ws As Worksheet
Dim found As Boolean
For Each ws In ActiveWorkbook.Worksheets
If ws.CodeName = "Sheet1" Then
found = True
Exit For
End If
Next ws
If found Then
'do stuff, e.g.,
MsgBox ws.Name
Else
MsgBox "No worksheets found with codename Sheet1"
End If


In article . net,
"Michael D. Ober" wrote:

I have some VBA code that does the following:

Option Compare Text

for i = 1 to ActiveWorkBook.Worksheets(i)
if ActiveWorkBook.Worksheets(i).CodeName = "Sheet1" then
set ws = ActiveWorkBook.Worksheets(i)
exit for
end if
next i

The problem is that this code doesn't always return as expected. In the
debuger, it always works, when run from an Add-In menu item, it works
about
10% of the time.

Any ideas,
Thanks,
Mike.

Reliably get sheet 1 of the active workbook
 

I'm not sure what you mean by "the "CodeName" property doesn't exist
until you enter the VBE environment."

When I put the code into a UDF, it works fine when called from the
worksheet, e.g.:

Public Function foo()
Dim ws As Worksheet
Dim found As Boolean
Application.Volatile
For Each ws In ActiveWorkbook.Worksheets
If ws.CodeName = "Sheet1" Then
found = True
Exit For
End If
Next ws
If found Then
'do stuff, e.g.,
foo = Application.CountIf(ws.Cells, "5")
Else
foo = CVErr(xlErrRef)
End If
End Function

and call it like:

=foo()

I get a valid return (as long as there's no circular reference).
Worked fine when foo() was in an add-in, too.

In article ,
"Michael D. Ober" <[email protected] wrote:

I just tried this and it still didn't work. After putting in some msgbox
statements, I discovered that the "CodeName" property doesn't exist until
you enter the VBE environment. Your comment about using for each ... next
instead of for i ... next is valid in general. However, I found that a
workbook will always have at least one worksheet, thus the for i ... next
does work. I did switch to for each ... next because it makes the code
shorter and easier to read.

Reliably get sheet 1 of the active workbook
 

Try your function on a CSV file that you just opened. Let me know if it
works then.

Mike.

"J.E. McGimpsey" wrote in message
...
I'm not sure what you mean by "the "CodeName" property doesn't exist
until you enter the VBE environment."

When I put the code into a UDF, it works fine when called from the
worksheet, e.g.:

Public Function foo()
Dim ws As Worksheet
Dim found As Boolean
Application.Volatile
For Each ws In ActiveWorkbook.Worksheets
If ws.CodeName = "Sheet1" Then
found = True
Exit For
End If
Next ws
If found Then
'do stuff, e.g.,
foo = Application.CountIf(ws.Cells, "5")
Else
foo = CVErr(xlErrRef)
End If
End Function

and call it like:

=foo()

I get a valid return (as long as there's no circular reference).
Worked fine when foo() was in an add-in, too.

In article ,
"Michael D. Ober" <[email protected] wrote:

I just tried this and it still didn't work. After putting in some
msgbox
statements, I discovered that the "CodeName" property doesn't exist
until
you enter the VBE environment. Your comment about using for each ...
next
instead of for i ... next is valid in general. However, I found that a
workbook will always have at least one worksheet, thus the for i ...
next
does work. I did switch to for each ... next because it makes the code
shorter and easier to read.

Reliably get sheet 1 of the active workbook
 

Works fine.

Instead of challenging, why not explain fully what problem you're
seeing. Beats playing "bring me a rock..."

In article ,
"Michael D. Ober" <[email protected] wrote:

Try your function on a CSV file that you just opened. Let me know if it
works then.