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.