"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

Select a hidden cell
Somehide hide payroll data in column G? Press F5. Type G1. Enter. Look in formula bar while you arrow down through G.

Forum statistics

Threads
1,223,896
Messages
6,175,264
Members
452,627
Latest member
KitkatToby

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