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.

 

[ddnron]

Hi,

I found this code, which enters the Category Name in column "i".
When I select a cell in "i5:i1000" range. It displays the list box,
Then I select the Category and then select any cell on sheet, it enters the Category in the pre-selected cell, then hides the display box.

The next code I need:

When I select "J5" as an example, it will display list box 2.
There are 8 Categories that can be selected in 1st list box (column "i".)
When I select a cell in column "J", List box 2 will be displayed, and the sub-categories will be displayed, populated from the selection in "i" column.
After I select my sub-category from list box 2 , then select any cell on spreadsheet, it will populate preselected cell target and hide listbox 2.

Any help would be appreciated.

Thanks,

Public Precell As String
Public Curcell As String

Private Sub Worksheet_SelectionChange(ByVal Target As Range)

' TO SELECT CATEGORY

Precell = Curcell
Curcell = Target.Address
If Precell = "" Then
Precell = Curcell
End If

If Target.CountLarge > 1 Then
Exit Sub

End If

With Me.ListBoxes("List Box 5")

For i = 1 To .ListCount
If .Selected(i) = True Then
m = m + 1
If m > 1 Then
Range(Precell) = Range(Precell) & "," & .List(i)
Else
Range(Precell) = .List(i)
End If
.Selected(i) = False
End If
Next i


Set LO = Me.ListObjects("cat")
If Intersect(Target, Range("i:i")) Is Nothing Then
.Visible = False
Else
.RemoveAllItems
.Visible = True
.Top = Target.Top
.Left = Target.Offset(0, 3).Left
For i = 1 To LO.ListRows.Count
.AddItem LO.ListRows(i).Range
Next i
End If

End With

End Sub