Excel decisionmaker

Fer9us

New Member
Joined
Nov 28, 2020
Messages
10
Office Version
  1. 365
Platform
  1. Windows
Hi MrExcel,

I'm trying to use excel to help me to create a tool to make complex decisions -- to find favourite items or options among a single list of multiple items or options.
This should be relatively simple to program, only I am a newbie at this.

I would really appreciate suggestions on how to code this process:

1. User inputs a number of items/options into LIST A.

2. Excel calculates the number of entries in LIST A and the number of combinations of pairs entries (without any repeats/duplicates).

3. Excel then generates a 2-COLUMN TABLE showing all the possible different pair combinations from the original list.

4. Entries from the 2-COLUMN TABLE are fed into a QUESTIONNAIRE (row by row), using a template "Which option from this pair is preferable? A or B?"

5. The user completes the QUESTIONNAIRE, and the votes for each individual item/option are tallied.

6. The item/option with the most votes wins, and the winning entry is displayed, followed by the runner up, 3rd place, 4th place and so on until all entries on LIST A have been put into order of preference.

END.

Note, ideally the program/code is scalable -- so could be used to for a list of up to "N" entries.

Look forward to hearing your ideas,

Fer9us
 

Fer9us

New Member
Joined
Nov 28, 2020
Messages
10
Office Version
  1. 365
Platform
  1. Windows
@rpaulson — do you have any suggestions on what I’m doing wrong?
Note this is an activex button I think - I tried earlier with a macro button but that was only directing me to the earlier two scripts.
Thanks,
Fergus
 

Some videos you may like

Excel Facts

What do {} around a formula in the formula bar mean?
{Formula} means the formula was entered using Ctrl+Shift+Enter signifying an old-style array formula.

rpaulson

Well-known Member
Joined
Oct 4, 2007
Messages
1,158
I never used Active X Buttons.

I have the below code in a standard module.
VBA Code:
Sub form()
UserForm1.Show
End Sub

I then add a basic text box or a shape to my sheet
right click on it and assign-macro to open the form.

-Ross
 

Fer9us

New Member
Joined
Nov 28, 2020
Messages
10
Office Version
  1. 365
Platform
  1. Windows
OK weird,
Without changing the "NEW BUTTON" I closed the form, restarted the machine and reopened it and now the button works.

Weird also, the userform comes up but its buttons don't change:

1607000265981.png


So it doesn't matter how many times I press Cow or Dog, the options don't seen to move on to the next options.

On the Calc tab, the combinations are all shown:

1607000352103.png


I've also followed your suggested steps to put the code you suggested into a standard module which I inserted in the VBA screen (Alt F11).
Then I created a fourth button under Developer > Insert > Form controls > Button (Form control), and assigned the Macro "form" to it. I called it THE RED BUTTON

That brings up the same userform, which presents me with two named options, but when I click them, the form stays the same.

As you can see below if I keep clicking Dog, I can keep going and it doesn't change the combination. The dog score just keeps going up.

1607001225582.png


Does this work on your side?
Thanks,

Fer9us
 

rpaulson

Well-known Member
Joined
Oct 4, 2007
Messages
1,158
on your form you have the text of num under cow.

this should actually be a number, it will show you the current selection you are on an will increment by 1 every time you answer the question.
this is also how the code to change the selections to new next row of the picking list.

I don't think you Named the label on your form "num", I think its probably still named Label1.
right click on the label in the form and make sure the (Name) Property is num

the name of this label must match the name used in the code. num = num + 1
 

Fer9us

New Member
Joined
Nov 28, 2020
Messages
10
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

Looks like it's WORKING!

1607010162768.png


Retests wins!

I like the way it re-prioritises the list at the end based on the votes. Nice one.

Next step would be: Can I add a static text field to the Userform that presents the question to the User, "Which option do you prefer?"

Also, there's the issue of the tiebreaker.

Thanks for your guidance and patience with this - works well and it's pretty simple.
 

Attachments

  • 1607009943923.png
    1607009943923.png
    31.7 KB · Views: 0

rpaulson

Well-known Member
Joined
Oct 4, 2007
Messages
1,158
You can add anything that you would like onto the userform.

Personally I would add another label that would show the user the total questions that will be asked. ie 15

to do this
- add a label on your form and name it tot
then in the user for Private Sub UserForm_Initialize() code add the below line after the num=1 line

tot = ws.Cells(Rows.Count, "A").End(xlUp).Row
 

Fer9us

New Member
Joined
Nov 28, 2020
Messages
10
Office Version
  1. 365
Platform
  1. Windows
Works well. I'll tweak it a bit more over the weekend. For now it's looking pretty schmick.
Thanks again,
Fer9us

1607120233685.png
 

Watch MrExcel Video

Forum statistics

Threads
1,127,116
Messages
5,622,816
Members
415,934
Latest member
adstocking

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
Top