"Round Robin" ticket assignment queue

scottcim

New Member
Joined
May 7, 2019
Messages
1
Hi,
Wondering if anyone has any VBA laying around that can help me build a ticket assignment queue.

I'm trying to create a "round robin" ticket assignment queue that will keep track of tickets assigned to individuals. There needs to have the ability to skip names if their cell is shaded red.




  • Press a button to display InputBox to enter ticket number.
  • Assign ticket number to the "next in line" name in Column A
  • If the "next in line" name cell is filled red, they should be skipped.
  • The name assigned the ticket needs to have their cell filled yellow. Only one name should be filled yellow, as they are the last one assigned a ticket.
  • The "ticket number" needs to be placed into the adjacent cell in column D, of the "next in line" name. If a ticket already exists in that cell, it should be moved to the next adjacent column. (A persons newest assigned ticket will always be in column D)
  • Column B should keep a running total of that names tickets.
  • A Msgbox should appear displaying the ticket number and the name it was assigned to.

So, for example below, the next in line ticket should go to Name2. The last assigned was Name16. Name1,3 and 4 should be skipped.


[TABLE="width: 331"]
<colgroup><col width="64" style="width: 48pt;"> <col width="84" style="width: 63pt; mso-width-source: userset; mso-width-alt: 3072;"> <col width="35" style="width: 26pt; mso-width-source: userset; mso-width-alt: 1280;"> <col width="103" style="width: 77pt; mso-width-source: userset; mso-width-alt: 3766;"> <col width="77" style="width: 58pt; mso-width-source: userset; mso-width-alt: 2816;"> <col width="78" style="width: 59pt; mso-width-source: userset; mso-width-alt: 2852;"> <tbody>[TR]
[TD="width: 64, bgcolor: #B4C6E7"]Name[/TD]
[TD="width: 84, bgcolor: #B4C6E7"]Ticket Count
[/TD]
[TD="width: 35, bgcolor: #B4C6E7"] [/TD]
[TD="width: 103, bgcolor: #B4C6E7"]Ticket numbers[/TD]
[TD="width: 77, bgcolor: #B4C6E7"] [/TD]
[TD="width: 78, bgcolor: #B4C6E7"] [/TD]
[/TR]
[TR]
[TD="bgcolor: red"]Name1[/TD]
[TD="bgcolor: #E7E6E6"]2[/TD]
[TD="bgcolor: #E7E6E6"] [/TD]
[TD="bgcolor: #E7E6E6"]Ticket17[/TD]
[TD="bgcolor: #E7E6E6"]Ticket1[/TD]
[TD="bgcolor: #E7E6E6"] [/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]Name2[/TD]
[TD="bgcolor: transparent"]3[/TD]
[TD="bgcolor: transparent"] [/TD]
[TD="bgcolor: transparent"]Ticket34[/TD]
[TD="bgcolor: transparent"]Ticket18[/TD]
[TD="bgcolor: transparent"]Ticket2[/TD]
[/TR]
[TR]
[TD="bgcolor: red"]Name3[/TD]
[TD="bgcolor: #E7E6E6"]2[/TD]
[TD="bgcolor: #E7E6E6"] [/TD]
[TD="bgcolor: #E7E6E6"]Ticket19[/TD]
[TD="bgcolor: #E7E6E6"]Ticket3[/TD]
[TD="bgcolor: #E7E6E6"] [/TD]
[/TR]
[TR]
[TD="bgcolor: red"]Name4[/TD]
[TD="bgcolor: transparent"]2
[/TD]
[TD="bgcolor: transparent"] [/TD]
[TD="bgcolor: transparent"]Ticket20[/TD]
[TD="bgcolor: transparent"]Ticket4[/TD]
[TD="bgcolor: transparent"] [/TD]
[/TR]
[TR]
[TD="bgcolor: red"]Name5[/TD]
[TD="bgcolor: #E7E6E6"]2[/TD]
[TD="bgcolor: #E7E6E6"] [/TD]
[TD="bgcolor: #E7E6E6"]Ticket21[/TD]
[TD="bgcolor: #E7E6E6"]Ticket5[/TD]
[TD="bgcolor: #E7E6E6"] [/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]Name6[/TD]
[TD="bgcolor: transparent"]3[/TD]
[TD="bgcolor: transparent"] [/TD]
[TD="bgcolor: transparent"]Ticket35[/TD]
[TD="bgcolor: transparent"]Ticket22[/TD]
[TD="bgcolor: transparent"]Ticket6[/TD]
[/TR]
[TR]
[TD="bgcolor: #E7E6E6"]Name7[/TD]
[TD="bgcolor: #E7E6E6"]3[/TD]
[TD="bgcolor: #E7E6E6"] [/TD]
[TD="bgcolor: #E7E6E6"]Ticket36[/TD]
[TD="bgcolor: #E7E6E6"]Ticket23[/TD]
[TD="bgcolor: #E7E6E6"]Ticket7[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]Name8[/TD]
[TD="bgcolor: transparent"]3[/TD]
[TD="bgcolor: transparent"] [/TD]
[TD="bgcolor: transparent"]Ticket37[/TD]
[TD="bgcolor: transparent"]Ticket24[/TD]
[TD="bgcolor: transparent"]Ticket8[/TD]
[/TR]
[TR]
[TD="bgcolor: #E7E6E6"]Name9[/TD]
[TD="bgcolor: #E7E6E6"]3[/TD]
[TD="bgcolor: #E7E6E6"] [/TD]
[TD="bgcolor: #E7E6E6"]Ticket38[/TD]
[TD="bgcolor: #E7E6E6"]Ticket25[/TD]
[TD="bgcolor: #E7E6E6"]Ticket9[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]Name10[/TD]
[TD="bgcolor: transparent"]3[/TD]
[TD="bgcolor: transparent"] [/TD]
[TD="bgcolor: transparent"]Ticket39[/TD]
[TD="bgcolor: transparent"]Ticket26[/TD]
[TD="bgcolor: transparent"]Ticket10[/TD]
[/TR]
[TR]
[TD="bgcolor: #E7E6E6"]Name11[/TD]
[TD="bgcolor: #E7E6E6"]3[/TD]
[TD="bgcolor: #E7E6E6"] [/TD]
[TD="bgcolor: #E7E6E6"]Ticket40[/TD]
[TD="bgcolor: #E7E6E6"]Ticket27[/TD]
[TD="bgcolor: #E7E6E6"]Ticket11[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]Name12[/TD]
[TD="bgcolor: transparent"]3[/TD]
[TD="bgcolor: transparent"] [/TD]
[TD="bgcolor: transparent"]Ticket41[/TD]
[TD="bgcolor: transparent"]Ticket28[/TD]
[TD="bgcolor: transparent"]Ticket12[/TD]
[/TR]
[TR]
[TD="bgcolor: #E7E6E6"]Name13[/TD]
[TD="bgcolor: #E7E6E6"]3[/TD]
[TD="bgcolor: #E7E6E6"] [/TD]
[TD="bgcolor: #E7E6E6"]Ticket42[/TD]
[TD="bgcolor: #E7E6E6"]Ticket29[/TD]
[TD="bgcolor: #E7E6E6"]Ticket13[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]Name14[/TD]
[TD="bgcolor: transparent"]3[/TD]
[TD="bgcolor: transparent"] [/TD]
[TD="bgcolor: transparent"]Ticket43[/TD]
[TD="bgcolor: transparent"]Ticket30[/TD]
[TD="bgcolor: transparent"]Ticket14[/TD]
[/TR]
[TR]
[TD="bgcolor: #E7E6E6"]Name15[/TD]
[TD="bgcolor: #E7E6E6"]3[/TD]
[TD="bgcolor: #E7E6E6"] [/TD]
[TD="bgcolor: #E7E6E6"]Ticket44[/TD]
[TD="bgcolor: #E7E6E6"]Ticket31[/TD]
[TD="bgcolor: #E7E6E6"]Ticket15[/TD]
[/TR]
[TR]
[TD="bgcolor: yellow"]Name16[/TD]
[TD="bgcolor: transparent"]3[/TD]
[TD="bgcolor: transparent"] [/TD]
[TD="bgcolor: transparent"]Ticket45[/TD]
[TD="bgcolor: transparent"]Ticket32[/TD]
[TD="bgcolor: transparent"]Ticket16[/TD]
[/TR]
</tbody>[/TABLE]


Thank you!
Scott



 

Excel Facts

What did Pito Salas invent?
Pito Salas, working for Lotus, popularized what would become to be pivot tables. It was released as Lotus Improv in 1989.

Forum statistics

Threads
1,223,214
Messages
6,170,774
Members
452,353
Latest member
strainu

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top