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.
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
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