Sheets and the sub-collection Worksheets have index numbers, Names (as seen
in the tab) and Codenames (as seen in the VB editor)
All worksheets are index in the worksheets collection.

Name and CodeName do not change if the sequence in which the worksheets
appears in the workbook changes. The index number will always start from #1
to #N and show the order of the workbooks as they appear in the workbook,
change the order and the index numbers for the worksheets will change.
Worksheet name can be changed by the user so is the less reliable method
since who knows what they might be renamed! Codename can only be changed
using VBA or the VBA editor, so they are useful for programme control.
Index values change for each worksheet depending on the order but are useful
for looping through all worksheets starting from the first to last for
example.

Try this code to see the three elements in action

Sub nameloop()
Dim sh As Worksheet
For Each sh In Worksheets
MsgBox "Index: " & sh.Index & vbCrLf & _
"Name: " & sh.Name & vbCrLf & _
"CodeName: " & sh.CodeName
Next
End Sub

--
Cheers
Nigel



"Jeffrey R Dempsey" wrote in message
...
Hi All,

This might be a dumb question (and yes, there are some...) but how do I
loop
through a bunch of sheets, and I have a UserForm that I need displayed for
each sheet. Here is my code:

For Each CurSheet In ThisWorkbook.Sheets
shName = Val(Right(CurSheet.Name, 3))
If shName =
ThisWorkbook.Sheets("Configuration").Range("P3").V alue
And shName <= ThisWorkbook.Sheets("Configuration").Range("Q3").V alue Then
ThisWorkbook.Sheets(CurSheet.Name).Activate
frmWorkEntry.Show (0)
End If
Next CurSheet

OK, it just loops through all of the sheets and ends at the last one. I
have a 29 sheets (and it can get larger) named XXX401 through XXX430, and
the sheets are not aligned in numerical order.

I do not want to go modal, because there is information on the sheets the
users may need to complete the current sheet.

Here is the sequence of events:
1. Start Loop
2. Show user form for an item
3. Display frmWorkEntry, and let the user enter the work done for that
item.
4. Validate Work entry
5. When the 'Next' button is pressed, populate current sheet, activate
next
sheet (validated to ensure that it is a correct sheet for work entry), and
loop back to step 2.
6. When there are no more work forms, quit loop.

Is there something that I am missing?

Thanks,

Jeff