Reliably get sheet 1 of the active workbook

  • Thread starter Thread starter Michael D. Ober
  • Start date Start date
M

Michael D. Ober

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.
 
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
 
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
 
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.
 
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.
 
Try your function on a CSV file that you just opened. Let me know if it
works then.

Mike.
 
Works fine.

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