Creating a combo box listing

[azizrasul]

I have a list of dates from 01/01/2011 to 31/12/2011 from which I have created 2 lists using Data|Validation and selecting List and using the dates above which are on the same sheet.

What I would like to do is when the user selects say, 01/08/2011 from the first list, I want the data in the second list to begin from 01/08/2011 and not 01/01/2011 as it does currently.

In short is there an event that is triggered when I select the data from the first list (cell A1) that will change the date list on the second list (cell A2)?

 

[MickG]

Try this:-
Based on Range of dates in column "A".
Validation list in "B1". set up for dates from column "A".(A1:A365)
Place this code in your sheet.
When you select a Date from "B1" (Say 1/5/2011) then a New validation Box appears in "D1" with dates starting from "1/5/2011".

[COLOR=navy]Sub[/COLOR] MG29Jun55
Private [COLOR=navy]Sub[/COLOR] Worksheet_Change(ByVal Target [COLOR=navy]As[/COLOR] Range)
[COLOR=navy]Dim[/COLOR] Rng [COLOR=navy]As[/COLOR] Range
[COLOR=navy]Dim[/COLOR] oDys [COLOR=navy]As[/COLOR] [COLOR=navy]Integer[/COLOR]
[COLOR=navy]If[/COLOR] Target.Address(0, 0) = "B1" [COLOR=navy]Then[/COLOR]
    oDys = 365 - DateDiff("d", [b1], "31/12/2011")
    [COLOR=navy]Set[/COLOR] Rng = Range("A" & oDys & ":" & "A365")
    Range("D1").Select
[COLOR=navy]With[/COLOR] Selection.Validation
    .Delete
    .Add Type:=xlValidateList, Formula1:="=" & Rng.Address & ""
[COLOR=navy]End[/COLOR] With
[COLOR=navy]End[/COLOR] [COLOR=navy]If[/COLOR]
[COLOR=navy]End[/COLOR] [COLOR=navy]Sub[/COLOR]

Regards Mick

 

[azizrasul]

Many thanks Mick.