Question about manipulating list box value in VBA

[ballack]

Hi all,
I want to know ways by which i can change the List box option in excel using VB.
For eg. : I have a drop down called "Info" and has values a,b,c and have another drop down called "Flag" and has values Yes and no.

Now if the user selects the value c in first drop down i must show only yes option in the second drop down.

How can i do this?

-Ballack.

[boylejob]

Ballack,

Try something like this on the Click event of the first ListBox. This should clear the second ListBox and then add back only "Yes.

Please Login or Register  to view this content.

I'm sure you will need to play around with this a little for it to fit into your application.

Hope it helps!

[ballack]

Thank you Jeff

[ballack]

Hi Jeff,
I dont use useforms, i write list data in some cells and define a name for it and then use it in LIst data. that is i go to data->validation
and in settings i specify allow as "List" and source i specify as =name.

So if i use this to specify the list box how can i make the listbox show only yes based on some condition?

[Alfaholic]

You could try playing with the Change event for the worksheet, putting in code that, when there's a change, checks what cell range the change is on. If it's one of the "name" list validation cells, then it calls a sub which rewrites the equivalent Yes / No validation cell to suit the selection. To be fair, if the only option is Yes, then it may be better to have the macro clear the Yes / No validation bit completely and just write "Yes" to the cell.

[boylejob]

ballack,

You can try something like this

Please Login or Register  to view this content.

In this code, it will check to see what the value is of E1. If the value is "c", it changes the drop down list in F1 to just Yes otherwise it changes it to Yes, No.

This code would go under ThisWorkbook so that it would fire when any cell is changed. You will definitely have to recode to make this fit your application. I am not real familiar with this, so I cannot tell you if it is set up exactly right, but it does function in the fashion you have requested on my sample sheet.

Hopefully, this will give you something to play around with and get you started.