Cycle through a list (Part 2)
 

I have this code, I got from Jim R in this newsgroup, and
the the code works great (THANKS JIM) if I have 4 users
set up.

My problem is I don't always have 4 users 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.

I was hoping someone out here would have a neasy fix for
me.

***Code****

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

Any thoughts
TIA
Pete

Cycle through a list (Part 2)
 

Pete,

If I understand the code correctly, then making "NameList"
a variable sized range rather then fixed to four columns
should allow it to work with any number of users.

Pete

-----Original Message-----
I have this code, I got from Jim R in this newsgroup, and
the the code works great (THANKS JIM) if I have 4 users
set up.

My problem is I don't always have 4 users 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.

I was hoping someone out here would have a neasy fix for
me.

***Code****

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

Any thoughts
TIA
Pete

.

Cycle through a list (Part 2)
 

Pete

I am not sure I understand your responce, how do I make my
range name (NameList) an variable list?

Please advise
Pete


-----Original Message-----
Pete,

If I understand the code correctly, then
making "NameList"
a variable sized range rather then fixed to four columns
should allow it to work with any number of users.

Pete

-----Original Message-----
I have this code, I got from Jim R in this newsgroup,
and
the the code works great (THANKS JIM) if I have 4 users
set up.

My problem is I don't always have 4 users 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.

I was hoping someone out here would have a neasy fix for
me.

***Code****

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

Any thoughts
TIA
Pete

.

.