OK....This is more of a general purpose approach
There are only 2 rules:
Rule_1:
Each ParentList cell must have a Sheet-Level range name that contains the
phrase: ListParent. Sheet-Level range names are created by prepending the
sheet name to the range name.
Example (if cell B5 is the parent Data Validation list for the cell below it:
Names in workbook: Sheet1!ListParent01
Refers to: =Sheet1!B5
Rule_2:
The Dependent List cell must be immediately below the Parent List cell
When a designated Parent List cell's value changes, the below code attempts
to clear the contents of the cell below the Parent List cell and display it's
dropdown list.
If that cell does NOT have data validation or the DV is not a list, the code
processing stops.
In a General Module...paste this code:
'--------start of code----------
Sub EngageDependentList(ByVal rngDepCell As Range)
'This sub receives a cell as its only argument
'and attempts to display the Data Validation list for that cell
Dim vTest As Variant
On Error GoTo errTrap
With rngDepCell
If (.Validation.Type = 3) Then
'the cell has a dropdown list
.ClearContents
.Select
MsgBox _
Title:="Notice", _
Prompt:="You must now select an item from the list", _
Buttons:=vbInformation + vbOKOnly
'Send [alt]+[down arrow] to the cell
Application.SendKeys ("%{DOWN}")
End If
End With
errTrap:
End Sub
'--------end of code----------
Paste this code in the code module for each sheet with Parent/Dependent lists:
'--------start of code----------
Private Sub Worksheet_Change(ByVal Target As Range)
Dim rngCell As Range
Dim nmName As Name
For Each nmName In ActiveSheet.Names
'Is the name designated as a ListParent?
If InStr(1, nmName.Name, "ListParent") <> 0 Then
'Test if the changed cell is THAT named cell
Set rngCell = Range(nmName.RefersTo)
If Not Intersect(rngCell, Target) Is Nothing Then
'Yes...So initiate the dependent list
EngageDependentList _
rngDepCell:=rngCell _
.Offset(RowOffset:=1, ColumnOffset:=0)
Exit Sub
End If
End If
Next nmName
End Sub
'--------end of code----------
Does that help?
***********
Regards,
Ron
XL2002, WinXP
Rajat said:
Ron
thanx a lot, the code worked and it was just what i needed.
But i have another question for you if i need to extend this code to all the
sheets of the workbook what modification do i require to made. if you can
tell me this it will be a great help.
regards
Ron Coderre said:
To do what you're asking, you'll need a bit of VBA code.
Try this:
Select the sheet with the Data Validation
Right-Click on the sheet tab
Select: View Code.....(that will open the VBA editor)
Paste this code into the VBA editor:
'--------start of code----------
Private Sub Worksheet_Change(ByVal Target As Range)
Dim rngParentCell As Range
Dim rngDepCell As Range
Dim rngCell As Range
Set rngParentCell = Range("A1")
Set rngDepCell = Intersect(Target, rngParentCell)
If Not rngDepCell Is Nothing Then
Set rngCell = Range("A2")
rngCell.ClearContents
rngCell.Select
MsgBox _
Title:="Notice", _
Prompt:="You must now select an item from cell A2", _
Buttons:=vbInformation + vbOKOnly
Application.SendKeys ("%{DOWN}")
End If
Set rngParentCell = Nothing
Set rngDepCell = Nothing
Set rngCell = Nothing
End Sub
'--------end of code----------
Now try changing the A1 value.
The message should pop up.
After the user clicks [OK]...A2 will be selected and the list displayed.
Is that something you can work with?
***********
Regards,
Ron
XL2002, WinXP
Rajat said:
In cell A1 i have created a "Drop Down list " using validation which calls
data from B1:B4.
and in A2 i have created a "drop Down List" using validation which Calls
data from D1
4.
And B1 , B2, B3, B4 contains data Like 1, 2, 3, 4 respectively.
C1 = A1 cell data
then in D1 : D4 cell i have used vlookup function to create a dynamic list
i.e. if in C1 data is = 1 the D1
4 shows 1.1, 1.2, 1.4, 1.4 respectively and
if in C1 data is = 2 the D1
4 shows 2.1, 2.2, 2.4, 2.4 respectively
Which then shows up in the list box of A2.
The problem i face is say first i selected 1 in cell A1 and the list Box of
A2 shows (1.1, 1.2, 1.4, 1.4) and i selected 1.2 but later on i chaged A1
cell value to 2 but but subsequently D1
4 value chaged to 2.1, 2.2, 2.4, 2.4
respectively but as i entered data in A2 didn't chaged.
what i want is if i chaged data in A1 then the the cell A2 value to be made
"" (Blank) and a alert message to provide to select data in A2.
