Consulting

Results 1 to 6 of 6

Thread: Round Robin Triggered by Button Click

  1. #1

    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?
    Attached Files Attached Files

  2. #2
    VBAX Sage
    Joined
    Apr 2007
    Location
    United States
    Posts
    8,711
    Location
    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
    Attached Files Attached Files
    ---------------------------------------------------------------------------------------------------------------------

    Paul


    Remember: Tell us WHAT you want to do, not HOW you think you want to do it

    1. Use [CODE] ....[/CODE ] Tags for readability
    [CODE]PasteYourCodeHere[/CODE ] -- (or paste your code, select it, click [#] button)
    2. Upload an example
    Go Advanced / Attachments - Manage Attachments / Add Files / Select Files / Select the file(s) / Upload Files / Done
    3. Mark the thread as [Solved] when you have an answer
    Thread Tools (on the top right corner, above the first message)
    4. Read the Forum FAQ, especially the part about cross-posting in other forums
    http://www.vbaexpress.com/forum/faq...._new_faq_item3

  3. #3
    VBAX Sage
    Joined
    Apr 2007
    Location
    United States
    Posts
    8,711
    Location
    and a button way to do it
    Attached Files Attached Files
    ---------------------------------------------------------------------------------------------------------------------

    Paul


    Remember: Tell us WHAT you want to do, not HOW you think you want to do it

    1. Use [CODE] ....[/CODE ] Tags for readability
    [CODE]PasteYourCodeHere[/CODE ] -- (or paste your code, select it, click [#] button)
    2. Upload an example
    Go Advanced / Attachments - Manage Attachments / Add Files / Select Files / Select the file(s) / Upload Files / Done
    3. Mark the thread as [Solved] when you have an answer
    Thread Tools (on the top right corner, above the first message)
    4. Read the Forum FAQ, especially the part about cross-posting in other forums
    http://www.vbaexpress.com/forum/faq...._new_faq_item3

  4. #4
    Mac Moderator VBAX Guru mikerickson's Avatar
    Joined
    May 2007
    Location
    Davis CA
    Posts
    2,778
    "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

  5. #5

    Found another way

    Quote Originally Posted by mikerickson View Post
    "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

  6. #6
    Moderator VBAX Sage SamT's Avatar
    Joined
    Oct 2006
    Location
    Near Columbia
    Posts
    7,814
    Location
    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.
    I expect the student to do their homework and find all the errrors I leeve in.


    Please take the time to read the Forum FAQ

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •