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

Hello Jeff,

If the sheet name contains any trailing spaces, the converted valu
will be wrong. I had this happen not long ago while writing a macro fo
a client. The client sent me a copy of the workbook in which he wa
having trouble referencing the sheets. I tried to get the code to ru
and it failed. 3 days later I discoverd the client had aligned th
sheet names with trailing spaces. This allowed the sheet name to b
centered in the sheet tab at the bottom, but never compared correctly
I used a TRIM statement to get rid of any leading or trailing space
and the comparasions worked.

TO REMOVE ANY LEADING AND TRAILING SPACES FROM THE SHEET NAME
shName = Val(Trim(Right(CurSheet.Name, 3)))

If you have your macro in *Personal.xls* then you should change al
references of ThisWorkbook to ActiveWorkbook. ThisWorkbook refers t
the Workbook where the code is located, and ActiveWorkbook refers t
the Workbook with the Focus. It is a subtle but important distinction

--
Leith Ros

-----------------------------------------------------------------------
Leith Ross's Profile: http://www.excelforum.com/member.php...fo&userid=1846
View this thread: http://www.excelforum.com/showthread.php?threadid=38029

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