Drill down list box selection

[ysherriff]

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



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.

 

[Nebu]

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

 

[ysherriff]

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.

 

[ysherriff]

Here is one that is perfect that I am trying to emulate. look at the last tab called trend view. this is a perfect example.

https://www.dropbox.com/s/zjc2e261r...get Achievement Report WE 2014-08-03.zip?dl=0

 

[Nebu]

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

 

[ysherriff]

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.

 

[ysherriff]

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

 

[Nebu]

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