Dynamic Dropdown from Column data

[mani_bbc05]

Hi Friends,

I need your help in creating 3 ActiveX ComboBox with dynamic drop down List Range which should be interdependent on each other.

I have attached a test file, with notes which i have to follow while creating the Comboboxes.

Thanks & Regards

 

[Luke M]

Hi mani_bbc05

Take a look at this example. Might need some touch-up to get it looking good, but the functionality is there I believe.

 

[mani_bbc05]

Hi mani_bbc05

Take a look at this example. Might need some touch-up to get it looking good, but the functionality is there I believe.

Thanks a lot Luke M, its perfectly as i was trying to do, except one thing.
In the ComboBox when i am clicking on it then scroll bar is coming in the dropdown list, because of which I am not able to see the whole list.

- If you dont mind then can you please tell me why this scroll bar is coming in the combobox.
- And is there any way to remove the scroll bar from the combobox list range.
- For Sub group "Canada" in 3rd dropdown country name is not coming, same problem is with sub group "Japan"

Here is link to error screenshot https://www.dropbox.com/s/pcyt4mcz0lsrk9x/Error Screenshot.JPG

 

[Luke M]

I noticed that popping up a bit...it seemed to go away when I would click on the drop down again. I'm nto sure why that happens...maybe the closeness of the drop downs to each other?

You can post the screenshot directly to the forum, either as a file or as a thumbnail. I'm afraid I can't access dropbox sites.

 

[Debraj]

Hi Mani Madhukar Prasad...

For activex Control. if your control's name & database are arrange in a manner.. then its easy to handle such scenario with a function..

try this..

In Workbook_open module..

Private Sub Workbook_Open()
deb = Sheets("Sheet1").Range("A6").CurrentRegion
For j = 2 To UBound(deb)
  If InStr(raj & ",", "," & deb(j, 1) & ",") = 0 Then raj = raj & "," & deb(j, 1)
Next

With Sheets("Sheet1")
  .ComboBox1.List = Split(Mid(raj, 2), ",")
  .ComboBox2.Clear
  .ComboBox3.Clear
End With
End Sub

In Sheet contain objects Module..

Private Sub ComboBox1_Change()
  ComboBox2.ListIndex = -1
  ComboBox3.ListIndex = -1
  If ComboBox1.ListIndex > -1 Then ComboBox2.List = Split(roy(1), ",")
End Sub

Private Sub ComboBox2_Change()
  If ComboBox2.ListIndex > -1 Then ComboBox3.List = Split(roy(2), ",")
End Sub

Function roy(x)
deb = Sheets("Sheet1").Range("A6").CurrentRegion

For j = 1 To UBound(deb)
  For jj = 1 To x
  If deb(j, jj) <> Sheets("Sheet1").OLEObjects("ComboBox" & jj).Object.Value Then Exit For
  Next
  If jj = x + 1 And InStr(raj & ",", "," & deb(j, jj) & ",") = 0 Then raj = raj & "," & deb(j, jj)
Next

roy = Mid(raj, 2)
End Function

Courtesy : snb

 

[mani_bbc05]

Hi Mani Madhukar Prasad...

For activex Control. if your control's name & database are arrange in a manner.. then its easy to handle such scenario with a function..

try this..

In Workbook_open module..

Private Sub Workbook_Open()
deb = Sheets("Sheet1").Range("A6").CurrentRegion
For j = 2 To UBound(deb)
  If InStr(raj & ",", "," & deb(j, 1) & ",") = 0 Then raj = raj & "," & deb(j, 1)
Next

With Sheets("Sheet1")
  .ComboBox1.List = Split(Mid(raj, 2), ",")
  .ComboBox2.Clear
  .ComboBox3.Clear
End With
End Sub

In Sheet contain objects Module..

Private Sub ComboBox1_Change()
  ComboBox2.ListIndex = -1
  ComboBox3.ListIndex = -1
  If ComboBox1.ListIndex > -1 Then ComboBox2.List = Split(roy(1), ",")
End Sub

Private Sub ComboBox2_Change()
  If ComboBox2.ListIndex > -1 Then ComboBox3.List = Split(roy(2), ",")
End Sub

Function roy(x)
deb = Sheets("Sheet1").Range("A6").CurrentRegion

For j = 1 To UBound(deb)
  For jj = 1 To x
  If deb(j, jj) <> Sheets("Sheet1").OLEObjects("ComboBox" & jj).Object.Value Then Exit For
  Next
  If jj = x + 1 And InStr(raj & ",", "," & deb(j, jj) & ",") = 0 Then raj = raj & "," & deb(j, jj)
Next

roy = Mid(raj, 2)
End Function

Courtesy : snb

Thanks a lot DEB, i am surprised to see that this is possible with such a small code.

Thanks again Deb