I need help with this situation.
lets stay column A has drop down menu where I can select 5 countries,
depending on what country I select in A I want the corresponding cell
in column B to allow me to select only from a list of cities of the
country selected in A.

Thank you



--
AERIF
------------------------------------------------------------------------
AERIF's Profile: http://www.excelforum.com/member.php...o&userid=28395
View this thread: http://www.excelforum.com/showthread...hreadid=479867

Hi,
Look at the following which explains how to create dependent Data
Validation lists.

www.contextures.com/xlDataVal02.html

HTH

"AERIF" wrote:


I need help with this situation.
lets stay column A has drop down menu where I can select 5 countries,
depending on what country I select in A I want the corresponding cell
in column B to allow me to select only from a list of cities of the
country selected in A.

Thank you



--
AERIF
------------------------------------------------------------------------
AERIF's Profile: http://www.excelforum.com/member.php...o&userid=28395
View this thread: http://www.excelforum.com/showthread...hreadid=479867

Hello Aerif,

This code uses a Drop Down box that holds the names of the countrie
and List Box that will display the cities. Selecting a country load
the List Box with the cities you want.

You need to set the Drop Down list's macro to SelectCountry to make i
work. I was uncertain if you wanted to return the city that wa
selected to a particular cell. Let me know and I can change that fo
you.

Copy and paste this code into the declarations section of the Workshee
the Drop Down list and List Box are on. You need to change the number
of the Drop Down and List Box to match those on your worksheet. The
are marked in red. Also the you will need to change the cities list
You can add as many country/city combinations as you need. Just follo
the format as it is shown.


Code
-------------------
Sub SelectCountry()

With Shapes("Drop Down 4").ControlFormat
If .ListIndex < 0 Then
LoadListBox (.List(.ListIndex))
Else
Shapes("List Box 6").ControlFormat.RemoveAllItems
End If
End With

End Sub
__________________________________________________ _
Sub LoadListBox(ByVal Country As String)

Dim Cities

Select Case Country
Case Is = "US"
Cities = Array("New York", "Boston", "Atalnta", "Baton Rouge", "Jackson")
Case Is = "Mexico"
Cities = Array("Mexico City", "Jalisco", "Tijuana", "Cabo San Lucas", "Acapulco")
End Select

Shapes("List Box 6").ControlFormat.RemoveAllItems
Shapes("List Box 6").OLEFormat.Object.List = Cities


End Sub

-------------------

--
Leith Ros

-----------------------------------------------------------------------
Leith Ross's Profile: http://www.excelforum.com/member.php...fo&userid=1846
View this thread: http://www.excelforum.com/showthread.php?threadid=47986