Vote - To opt out Voter after voted and duplicate vote count cross dept vote

Hi All,

Really on red alert for some experts on macros. the link redirects to the workbook I've worked on. It's a voting poll using userform of which have 2 dependant combo boxes on the voters on Page 1 while Page 2 are the nominees. I will say that I'm still a beginner in macro excel and much of what is attached comes from the previous post thread and also some from other websites on excel macros. So, I would really appreciate for the exact macros and where to paste it.

https://www.dropbox.com/s/9bsx65wjlr5crcs/Sample.xls

The additional requests from my employer are 2 things:-
1. After Voted, the Voter should be out from the Voters List. The original macro does this, but without the dependant combo box to choose dept first then Name. When I manage to get the macros to allow dependant values, I just don't know how to modify the original macro to take off the name after voted. I've tried for days, the name who already voted are still there when the next voter use the userform.

2. Vote Count should be 2 when the Voter votes for the nominee who is not from the same department as s/he is. e.g. I'm from IT Dept, when I vote for a nominee which is from HR dept, that nominee should have 2 vote counts, but if I vote a nominee who is also from IT dept, the nominee will get only 1 vote count.

Please help as I really must complete this by Mon, 16/6/2014.

Thank you in advance.

Originally Posted by jaslake

Hi Dahlia

In the attached I've added a couple of Named Ranges and I've changed the definition of the "Voters" Named Range.

I've also added a couple of lines to the UserForm Initialize Event and have indicated where I added them.

The Code will run from a Button on Sheet 2. I'd assume you'll hide Sheet 1.

I've modified quite heavily the "Vote" Button...let me know what works and what doesn't.

Hi Jaslake,

Thank you for the prompt assistance. I've tried but I notice there is an issue. Probably I have stated it wrongly earlier. When a voter has voted, the name should not appear in the list at the userform but it should still stays in the excel spreadsheet and the column "Has Voted" shud indicated as "TRUE". eg. Freddy is the 1st voter. He runs the userform, select his Dept, select his name, verified by staff ID, then select nominees, then vote. After he logs out, when he or other user runs the userform again, eg, Aina, when she selects Dept followed by Name, she shud no longer see Freddy's name in the dropdown combo-box while at the back-end, the spreadsheet Sheet1 will show that Freddy's name still stays while in column "Has Voted" shows TRUE.

As for the vote count for cross dept nominees, it works great. But, it only runs once only then the next run will appear the error "Object Required". I suspect this happens due to the deletion of the first voter in the spreadsheet.

Appreciate your help to modify necessarily.

Thank you in advance.