I have a spreadsheet that I distribute to up to 4 people
in my office (depending on what account we are on) These
recipients fill out data in specific order that is based
on the prior persons info input.

To start the chain I have inserted a userform that lets me
(The host and also the first person to input information)
set up all the users for that day. The names get inserted
into cells D11-G11. With another cell (C2) set up to say
in very bold lettering whose turn it is to enter the next
Information.

What I want to do is instead of having the users change
this cell every time they are done inserting their info
and before they send it to the next person for input, is
to have this done automatically by cycling through the
list of names in order until all info is entered and then
the sheet will come back to the beginning (Me). Then I
start the new series of info inputting until we have all
the data filled in, this takes about 6 rounds before it is
complete.

The spreadsheet is set up with a macro button that
calculates the final info and sends an e-mail to a
dedicated server for backup filing. When this macro is
preformed I want to cycle the name at that point and e-
mail the saved file to the next user.

Is this possible?

TIA for any insight.
Pete

To update the current name cell you might try something like this:

Sub UpdateName()
Dim CurrName As String
Dim CurrIdx As Integer
CurrName = Range("CurrentName").Value
If CurrName = "" Then
CurrIdx = 1
Else
On Error GoTo BadMatch
CurrIdx = Application.Match(CurrName, Range("NameList"), False)
CurrIdx = CurrIdx + 1
If CurrIdx Range("NameList").Columns.Count Then
CurrIdx = 1
End If
End If
ResumeHe
Range("CurrentName").Value = Range("NameList").Cells(CurrIdx).Value
Exit Sub
BadMatch:
CurrIdx = 1
Resume ResumeHere
End Sub

Assign the name "CurrName" to cell C2 and "NameList" to D11:G11 using Define
Name (Ctrl-F3).

--
Jim Rech
Excel MVP

Jim

This code works great, thank you for your help. One
question though.

I don't always have 4 people involved in these inputting
sessions. When I set up the worksheet to only have 2 or 3
users the last person that enters the data ends up sending
it to no one in particular.

Example----
2 users - 1)Pete 2)Tom

When Tom hits his calculate button the "CurrentName" range
changes to "Blank" and the the program returns and error
because it can't find a name to send the worksheet to the
next user.

Any thoughts.
Pete


-----Original Message-----
To update the current name cell you might try something
like this:

Sub UpdateName()
Dim CurrName As String
Dim CurrIdx As Integer
CurrName = Range("CurrentName").Value
If CurrName = "" Then
CurrIdx = 1
Else
On Error GoTo BadMatch
CurrIdx = Application.Match(CurrName, Range
("NameList"), False)
CurrIdx = CurrIdx + 1
If CurrIdx Range("NameList").Columns.Count Then
CurrIdx = 1
End If
End If
ResumeHe
Range("CurrentName").Value = Range("NameList").Cells
(CurrIdx).Value
Exit Sub
BadMatch:
CurrIdx = 1
Resume ResumeHere
End Sub

Assign the name "CurrName" to cell C2 and "NameList" to
D11:G11 using Define
Name (Ctrl-F3).

--
Jim Rech
Excel MVP


.

Sorry for not seeing your response sooner...

The way the macro works requires you to redefine the range "NameList" when
you add or remove names.

Here's a different version that only needs you to assign the name
"FirstListName" to the first cell of the name list (D11 in your example) so
it should be more flexible. The name "NameList" used in the first macro is
no longer needed.

Sub UpdateName()
Dim CurrName As String
Dim CurrIdx As Integer
Dim NameListRg As Range
CurrName = Range("CurrentName").Value
Set NameListRg = Range(Range("FirstListName"),
Range("FirstListName").End(xlToRight))
If CurrName = "" Then
CurrIdx = 1
Else
On Error GoTo BadMatch
CurrIdx = Application.Match(CurrName, NameListRg, False)
CurrIdx = CurrIdx + 1
If CurrIdx NameListRg.Columns.Count Then
CurrIdx = 1
End If
End If
ResumeHe
Range("CurrentName").Value = NameListRg.Cells(CurrIdx).Value
Exit Sub
BadMatch:
CurrIdx = 1
Resume ResumeHere
End Sub

--
Jim Rech
Excel MVP