PDA

View Full Version : [SOLVED] Round Robin Triggered by Button Click



jorgegarcia2
11-16-2015, 09:53 AM
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?

Paul_Hossler
11-16-2015, 01:20 PM
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




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

Paul_Hossler
11-16-2015, 03:26 PM
and a button way to do it

mikerickson
11-16-2015, 08:06 PM
"how to get a click to add 1 to that number with each click and then go back to 1 after I reach 5."


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

jorgegarcia2
11-17-2015, 08:10 PM
"how to get a click to add 1 to that number with each click and then go back to 1 after I reach 5."


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?



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

SamT
11-17-2015, 09:51 PM
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.