Listbox

[Milind Keer]

Hi All

I have two lists... depending upon the selction of one list I want to
update another list...
e.g. Country List - Here I am listing UK, Ireland
My second List contains Cities, i.e. London, Manchester, Leeds, Dublin etc...

Now if I select UK in Country list I want to display only UK cities in City
list (i.e. London, Machester, Leeds etc) and If I select Ireland then only
Ireland cities (i.e. Dublin etc)

I have implemeted this functionality creating Named ranges... have created
3 lists, gave them name, and used if condition... everything is working
fine... but issue is earlier selection...

If I Select UK... obviously my city list is currently displaying only UK
cities... here if I am selecting lets say London... now when I select Ireland
in country list... my city list is getting updated with Ireland cities but my
previous selection i.e London is still there... I don't want to display
london when I select Irelant... i want to set it either to any default
value... or blank... but certainly not London...

do I need to write a code to do this?? if yes how? bec's macro is not
identifying list?? and what would be the trigger function??

Please help... lokking forward for your prompt reply... thanks

Milind

 

[Luke M]

Correct, you will need a VB event macro. Right click on sheet tab, view code.
Paste this in, modifying as needed. Note that this will make the second cell
blank.

Private Sub Worksheet_Change(ByVal Target As Range)

'Change to cell with first list
If Intersect(Target, Range("A2")) Is Nothing Then Exit Sub

'Change to cell with second list
Range("A3").ClearContents
End Sub

 

[Milind Keer]

Hi Luke

Thanks for the prompt reply... I thought about this option but on any change
this function is getting called and and resetiing the values... which I don't
want....

Basically what I want is... If I select UK... then other other list box
should retain the value if it belongs to UK... but if I select Ireland then
if UK city is already there in second list box it should updated to blank....
i know this is something possible thr' coding but don;t know which event
should I use....

Is there any event for Dropdown list...??

-milind

 

[Milind Keer]

Hey Luke...

I resolved this issue...

Private Sub Worksheet_Change(ByVal Target As Range)

If Target.Row = 3 And Target.Column = 3 Then
If Range("C3").Value = "IRE" Then
Range("C4").Value = "Dublin"
End If

If Range("C3").Value = "UK" And Range("C4").Value = "Dublin" Then
Range("C4").Value = "London"
End If
Else
Exit Sub
End If

End Sub


I will make more robust... but its working.... thanks a ton...