• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Advanced Filtering and Interacting with User

gupta425

New Member
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
 

Attachments

gupta425

New Member
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
 
Last edited by a moderator:

p45cal

Well-Known Member
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:

63749
 
Top