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

Drill down list box selection

ysherriff

Member
Hello all. I am doing something new that I have never attempted to do and need help. I am familiar with listbox but I need help in developing a drill down for my list box via VBA and need help where to start.

Here is a screen snapshot

upload_2015-1-28_11-51-11.png

In this example, column A will be the only item shown initially in the list box. When the user clicks on the item in column A, then all items in Column B will show. When the user clicks on any item in column B, then the corresponding states in column D will show. When the user click on a specific state, then the corresponding facilities in the states are shown.

If anyone has a similar vba code or one that I can modify, I would appreciate it. I will try to take a stab it but clueless where to start.

thanks for your help.
 

Attachments

  • chandoo- list box vba.xlsx
    15.6 KB · Views: 4
Hi:

Please find the macro to drill down till 3rd level double click on GLC you will get Northeast,southeast and West double click on any of these region it will give you next level. I could not make out the relationship of the next two levels ,if you can explain me that I can try out the other levels as well

Note:

If you click outside the list box will reset to the first level

Thanks
 

Attachments

  • chandoo- list box vba.xlsm
    28.2 KB · Views: 4
thanks Nebu. To answer your question, if you click on a state that is shown in Column F, then the list of facilities in Column G will show up that is in that state.

Also, there is an error. When you click on Clinical Operations, GA in Column D should show up, but currently there is nothing.

I will try to work and see if I can add the other level. What you have given me is a good learning starting point. I appreciate it.

Attached is the file again.
 

Attachments

  • chandoo- list box vba.xlsm
    26.5 KB · Views: 0
Hi:

I have completed the macro. I saw the dropbox the concept is same as mine, instead of button you have to double click on the item you want to drill down. I have also included a back button it will help you to drill back to next level.

Note:
Click on the list box before clicking the back down to drill back.

Thanks
 

Attachments

  • chandoo- list box vba.xlsm
    30.4 KB · Views: 4
thank you but I will have to modify. I want the ability to drill up a level. when you click the back button, it goes to the very first list "GLC". That is the reason why I have the up and down arrow.

I really appreciate your help and will learn a lot from you. Another issue is the Clinical Operation is not selected. I will try to see how the up and down arrow works.
 
I modify it a bit but when I click on Clinical Operations, the selected range is not populating the list box. Can you tell me where I am going wrong?


Thanks
 

Attachments

  • chandoo- list box vba v.04.xlsm
    405.5 KB · Views: 7
Hi:

I am not sure what modification you have done, unless and until you specify that it is difficult to comment on the same. There is a lot of code in there. It will need time to go thru all the code and understand what it does.

Thanks
 
Back
Top