0

I want to visialize a dependant list box from VBA list box.

I have an excel table as as Letters A B C D E Numbers 1 2 3 4 5

And I have VBA userform with two list boxes. In first one I want to select weather letters or numbers In the second list box,I need to make the viewing list dependant based on the fist choice

I made this with excel data validationand it is successfull, now i need to visulaize it from VBA form.

h pavi
  • 15
  • 4
  • 1
    Have you tried anything so far? Could you show us some code? A hint could be to use a dictionary for that. – Storax Dec 24 '18 at 08:24
  • 1
    I'm sure in SO there's plenty of examples concerning your very issue: search for them, make your trial and error work and if you got stuck come back with your code and its issues – DisplayName Dec 24 '18 at 08:25
  • 3
    Possible duplicate of [dependent dictionaries excel vba](https://stackoverflow.com/questions/48406176/dependent-dictionaries-excel-vba) – Storax Dec 24 '18 at 08:28

1 Answers1

0

Assume listboxes are called LstChoice and LstOptions. Assume the table is in the range("a1:B5") In the After_update event of lstChoice put this

 Private Sub LstChoice_AfterUpdate()

Dim x As Long
Me.LstOptions.Clear
Select Case LstChoice.ListIndex

      Case 0
          'Letters

          For x = 0 To 4
              Me.LstOptions.AddItem Cells(1, 1).Offset(x, 0)
          Next x
      Case 1
              'numbers
               For x = 0 To 4
                       Me.LstOptions.AddItem Cells(1, 2).Offset(x, 0)
                 Next x
End Select

End Sub

Harassed Dad
  • 4,669
  • 1
  • 10
  • 12
  • Thanks all for your great ideas..I still not clear on how to manage large set of data. Until now what I have tried was created two list boxes in VBA, for the first one edited rowSource reference to the two cells letters and numbers as sheet1!C11:D11, and the second list box, i do not know how to get the relevent data filtered based on the first choice – h pavi Dec 24 '18 at 16:00
  • Hi Harassed,I tried this video as you explained in your answer. https://www.youtube.com/watch?v=YIEt_S65GrU – h pavi Dec 25 '18 at 09:59