Choose from List Box

[ddnron]

Hi,

Thanks for your help and time. I have not used this forum for a while, so I hope I can explain well enough.

Background Current -

I'm using Data validation for populating cells, in two columns.

Sheet Name - Register
Cells "i5:i1000" is for selecting a category (Bills, Credit, Gas, Grocery, etc)
Cells "j5:J1000" is for selecting a sub-category, based on what was selected in Category cell. Example - Category "Bills" selected - Sub category cells validation show "Direct Tv, Ameren, Verizon, etc)

As we know Drop down lists for validation, only displays 8 items. My sub-category list, has as many as 15 items, so you have to scroll to get to all of the items on the lists.

I'm trying to make this selection process easier for my wife. She struggles moving mouse to particular area on sheet, like a little drop-down area, then scrolling and selecting the right item.

So I want to make it easier.

Would like Future -

When we select a cell in "i5:i1000", a list is displayed that shows the Category, and the Sub-category list. When we select the category, the sub-category list is update appropriately and displays all 15 items.

We select the Category and sub-category, and that data is populated to the target cell, we selected in column "i". Then the sub-category is also populated in "j" column.

When any other cell is selected on sheet, category list and sub-category list is hidden. So, the only time list shows up, is when we select a cell between "i5i1000".

Apricate any help on this.

Thanks again

Ron

 

[abdelfattah]

Hi,
I think using form contains two combo boxes will be better than of using data validation .
or check this
Macro to create searchable data validation+combobox

 

[ddnron]

Hi Abdelfattah,

Thanks for taking the time to help me and for your suggestion. I agree that we could use a combo box, but I do not need a search function. I only have 12 Categories and my largest subcategory list is 15. When I created a combo box, it created a box with scroll button arrow, not wanting a scroll feature.

However...when I created a listbox1, it provided the Category list, where I could just select from the list. My lack of code knowledge...is getting what I selected in the listbox1, to the target selected cell, which are i5:i1000. I can create another listbox for subcategories. But do not know how to fill subcategory list, when selecting the category.

In Image...i need select item from list box to go to target cell (which I highlighted). I also need to populate a 2nd list box, by what I selected in first list box. then move both selections to i5 and j5. Then next entry would be row 6, target, move down, as I spend money.

Thanks for helping

 

[abdelfattah]

Can you show me example when select item from Category column how should populate item in subcategory column?
just I want to make sure I don't misunderstood you .

 

[ddnron]

Can you show me example when select item from Category column how should populate item in subcategory column?
just I want to make sure I don't misunderstood you .

See Image -

How I Invision:

When I select a cell in Column "i" - Listbox1 is displayed and Listbox2.

In first example - I selected Grocery, from List Box1 - then it finds Named range Grocery, and display the list. I then Select Walmart, and it returns the data to the target cell "i5" and "j5".

Example2 - I select "i6" - List Box1 and List Box2 are visible. I select Credit, from listbox1, and listbox2 displays BOA, Capone and PNC. I choose Capone...and it returns the data to target cell row "i6 and "j6".

After I select my choices in list box1 and 2...It returns the data to I and J... when I select any cell on the screen.

Hope this helps

And thanks again for your time and effort.