Advanced Filtering and Interacting with User

[gupta425]

Hey there,
My name is Avi, and I am working as a Process Manager for a company in Seattle, USA. I am fairly new to EXCEL VBA, and need help with a problem.

So, my company has a number of suppliers to choose from. Based on a comparison of product specs, I want the sales team to pick out the best supplier for a chosen spec
Can someone help me with creating a interactive USERform, such that the sales team can easily use it.
Please find attached my excel file capturing this data:

A few details about the data
Objective: When the user types/chooses a certain spec, I want the output to display all the suppliers which offer that spec, along with the tier rating of the supplier.
If you open the file right now, the way the data is organized, if a supplier does offer a certain spec, that spec has a "1" for that supplier under the spec. If the supplier doesn't, it has been left blank.

Please do advise me on what I can do here

Would really appreciate any help

Thank you,
Avi

 

[p45cal]

In the attached is a bare bones solution.
Click the button in the top right portion of the sheet.
It uses a hidden sheet.

 

[gupta425]

p45cal Thank you so much! You are a great help. If it's not too much trouble, could you also explain the code? It works, but I just want to understand what each line is doing?

Avi

 

[p45cal]

It is too much trouble, unfortunately. It takes a lot longer to explain than to write.
All I can suggest is that you unhide Sheet2, show the Immediate pane in the VBE (Ctrl+G if you can't see it) and the Locals pane (Alt+V, S if you can't see it)
Put break points at the beginning of Private Sub ListBox1_Change() and Private Sub UserForm_Initialize()
Then run the blah macro using Alt+F8, and step through the code using F8 on the keyboard.
Whilst doing so, at various points, you can enter commands such as:

Application.Goto DataRng
Application.Goto DataRng.Offset(, 1).Resize(1, DataRng.Columns.Count - 2)
Application.Goto ResultRng
Application.Goto Destn
Application.Goto critrng

?ResultRng.Address(external:=True)
'[Chandoo42923Test Data.xlsm]Sheet2'!$A$2:$B$18 (this is not a command but the result of executing the command above it)

in the Immediate Pane and press Enter and see what's selected on the sheets. You can re-execute these commands just by putting the cursor on one of them and pressing Enter.
You can also expand the various variables found in the Locals pane so that you can explore what's in them: