Round Robin Triggered by Button Click

What I need: a cell that populates the next name from a list whenever a macro button is clicked. When the list is exhausted, it should restart with the first name. I'm essentially looking to route leads evenly by creating a Google Sheet that provides the lead router with the next name on the list. The reason it needs to be done by click is the sheet will be shared with multiple lead routers.

I used this video (youtube.com/watch?v=HCmsJAfe6t8) to show me how to create a round robin in Excel. I want to hide the round robin in a hidden tab and simply create a button that when clicked, will bring up the next name on the list. The round robin is triggered by I5 (the start number). I can't seem to figure out how to get a click to add 1 to that number with each click and then go back to 1 after I reach 5 as a start number.

I've attached the file.

Does this make sense?

You can use the double click event on the worksheet so that if you double click I5 it counts up to 5 an then resets to 1

The 5 is hardcoded, but there's ways to let the macro determine it

Code:

---

Option Explicit

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)

    If Target.Address <> "$I$5" Then Exit Sub

    Application.EnableEvents = False

    If Target.Value + 1 > 5 Then
        Target.Value = 1
    Else
        Target.Value = Target + 1
    End If

    Application.EnableEvents = True

End Sub

---

It might be cleaner to avoid the WS formulas and just have a macro button bring up the next name

and a button way to do it

"how to get a click to add 1 to that number with each click and then go back to 1 after I reach 5."

Code:

---

Sub ButtonClick()
    With Range("A1")
        .Value = (.Value Mod 5) + 1
    End With
End Sub

---

Quote:

---

Originally Posted by mikerickson

"how to get a click to add 1 to that number with each click and then go back to 1 after I reach 5."

Code:

---

Sub ButtonClick()
    With Range("A1")
        .Value = (.Value Mod 5) + 1
    End With
End Sub

---

---

Thanks everyone! Unfortunately I couldn't open your attachments because I don't have enough post points? I'll have to go find some easy questions to answer correctly so I can boost my points.

I was able to get an answer that works in Excel, but it turns out Google doesn't use VBA (probably should have checked that first). Can anyone recode this into GAS?

Code:

---

Option Explicit

Private Sub CommandButton1_Click()
Dim Rng As Range
Static n
Set Rng = Range(Range("A1"), Range("A" & Rows.Count).End(xlUp))
    n = n + 1
    n = IIf(n = Rng.Count + 1, 1, n)
    Range("B1") = Rng(n)
End Sub

---

Quote:

---

Unfortunately I couldn't open your attachments because...

---

You have to save/download the attachments to your computer, then open them like any other file.