List box value

[bnageshrao]

In one of the cell I have a drop down box showing various values which can be picked by end user. Is it possible to have another drop down box in adjacent cell which shows different values for each value picked up in the earlier cell.

 

[Kaah]

Yes, you can do it,

In the on_change method of dropdown1, put a call to the method that populates dropdown2. Ofcourse the code that updates dropdown2 should have the current dropdown1 selection as an input parameter. You'll as well need to clear dropdown2 each time you repopulate.

Let me know if you have any issues...

K.

 

[bnageshrao]

Since I have not used VBA much, I will appreciate if you can send me some pseudo codes. Thanks

 

[Kaah]

Yep,
heres code:

Private Sub ComboBox1_Change()
ComboBox2.Clear
ComboBox2.list = PopulateValueArray(ComboBox1.list(ComboBox1.listindex))
end sub


Function PopulateValueArray (ListType as variant) as variant

dim vReturnArray as variant

'\\insert code here to populate an array with whatever
'\\list you want to occur for each member of the
'\\combobox1. I suggest a select case statement. You
'\\haven't told me where you want to get the lists from
'\\ so I cant go any further!

PopulateValueArray = vReturnArray

end function


I hope this helps, Ive used te variant datatype only because I dont know what kind of data you want to fill your combobox/array with. The only thing left for you to code is the arraypopulation function which should be easy for you (being a programmer and all)...


Kaah

 

[bnageshrao]

The way I populated first cell was by going to Data menu and then picking validation submenu and then picking list from validation criteria. I can type various states there. This gives me a drop down box in that cell. Now I wanted another drop down box in the adjacent cell which gives me various cities for a perticular state.
As per you I put two combobox and try to populate both boxes and somehow couldnot get any values in those boxes, apparently i must be making some mistakes. Is it possible for you to show how to add states to combobox1 and corresponding cities for those in combobox2. Thanks

 

[Kaah]

B,

I dont know the ins-and-outs of coding for data validation tools, Do you really need to use it for this application? The dropdown that comes from the data validation function is system generated and I've never tried to reference it through code...

However here's code that will generate an array based on a group of cells. You can do it in two ways, base the list on all cells in a named range, or start at one cell and move along until you find a blank cell (The code below does it this way, but I've set it up so you can insert your own code to do it the first way). The data validation function bases itself on a range of cells, so this should do the trick for you I think...

function PopArrayFromRange (rMyRange as Range) as Variant

dim iCount as integer
dim vReturnArray as variant

if rMyRange.cells.count>1 then
'\\cycle through cells in range

elseif rMyRange.cells.count = 1 then
'\\start from range cell and move according to bDown
redim vReturnArray(0)
vReturnArray(0) = rMyRange.Value
icount = 1
do
if rMyRange.offset(icount, 0).value <> &quot;&quot; then
redim Preserve vReturnArray(iCount)
vReturnArray(icount) = rMyRange.offset(iCount, 0).value
icount = icount + 1
else
PopArrayFromRange = vReturnArray
exit function
end if
loop until iCount> rMyRange.Parent.Usedrange.Rows.count

End if

PopArrayFromRange = vReturnArray

end function


I haven't tested this code BTW, so you might find a little syntax error here and there, nothing you wont be able to iron out I'm sure...(I've also taken a few short cuts as well, again, I'm sure you can expand on the functionality where required)

Hope it helps

Kaah.

 

[bnageshrao]

Thanks

 

[ratman]

Hello bnageshrao,

If you still require help email me the file and I'll set it up for ya.
drat@mediaone.net


Ratman