Code not getting triggered by change in value

Hi,
I've this following code in my sheet to create a dynamic "Data validated
cells (to list).
But this is not getting triggered by the change of value in the first Data
validated (to list) cell

Can someone please let me know the solution? I've spent my whole day on this
with no results/.......

Thanks

Option Explicit
'----------------------------------------------------
Private Sub Worksheet_Calculate()
Dropdown_Change ActiveCell
End Sub
'----------------------------------------------------
Private Sub Dropdown_Change(ByVal Target As Range)
Dim oFoundCell As Range
Dim iTargetCol As Long
If Not Intersect(Range(kList1), Target) Is Nothing Then
If Target.Count = 1 Then
With Data.Range(kList1Hnd)
Set oFoundCell = .Find(what:=Target.Value, LookIn:=xlValues)
If oFoundCell Is Nothing Then
MsgBox "Selected group has no clients"
Exit Sub
End If
End With
'load the List2 dropdown and set the default to item 1
iTargetCol = oFoundCell.Column
fzCreateValidationList2 Target.Offset(4, 0), iTargetCol, Target
Target.Offset(4, 0).Value = Data.Range(kList2Hnd &
iTargetCol).Value
End If
End If
End Sub
'----------------------------------------------------
--
Baapi

Put a little message box at the start of your code. Something like:
MsgBox "Working"
Now make a change in some other cell, any cell. The message box should pop
up. If it does and it still doesn't when you make a change to the Data
Validation cell, I would say that you have an older version of Excel that
will not react to a change in a Data Validation cell. HTH Otto
"Baapi" <[email protected]> wrote in message
news:[email protected]...
> Hi,
> I've this following code in my sheet to create a dynamic "Data validated
> cells (to list).
> But this is not getting triggered by the change of value in the first Data
> validated (to list) cell
>
> Can someone please let me know the solution? I've spent my whole day on
> this
> with no results/.......
>
> Thanks
>
> Option Explicit
> '----------------------------------------------------
> Private Sub Worksheet_Calculate()
> Dropdown_Change ActiveCell
> End Sub
> '----------------------------------------------------
> Private Sub Dropdown_Change(ByVal Target As Range)
> Dim oFoundCell As Range
> Dim iTargetCol As Long
> If Not Intersect(Range(kList1), Target) Is Nothing Then
> If Target.Count = 1 Then
> With Data.Range(kList1Hnd)
> Set oFoundCell = .Find(what:=Target.Value,
> LookIn:=xlValues)
> If oFoundCell Is Nothing Then
> MsgBox "Selected group has no clients"
> Exit Sub
> End If
> End With
> 'load the List2 dropdown and set the default to item 1
> iTargetCol = oFoundCell.Column
> fzCreateValidationList2 Target.Offset(4, 0), iTargetCol, Target
> Target.Offset(4, 0).Value = Data.Range(kList2Hnd &
> iTargetCol).Value
> End If
> End If
> End Sub
> '----------------------------------------------------
> --
> Baapi

I just looked at your code and saw "Dropdown_Change" in the name of the
macro. That's supposed to be "Worksheet_Change". HTH Otto
"Otto Moehrbach" <[email protected]> wrote in message
news:O5ms8l%[email protected]...
> Put a little message box at the start of your code. Something like:
> MsgBox "Working"
> Now make a change in some other cell, any cell. The message box should
> pop up. If it does and it still doesn't when you make a change to the
> Data Validation cell, I would say that you have an older version of Excel
> that will not react to a change in a Data Validation cell. HTH Otto
> "Baapi" <[email protected]> wrote in message
> news:[email protected]...
>> Hi,
>> I've this following code in my sheet to create a dynamic "Data validated
>> cells (to list).
>> But this is not getting triggered by the change of value in the first
>> Data
>> validated (to list) cell
>>
>> Can someone please let me know the solution? I've spent my whole day on
>> this
>> with no results/.......
>>
>> Thanks
>>
>> Option Explicit
>> '----------------------------------------------------
>> Private Sub Worksheet_Calculate()
>> Dropdown_Change ActiveCell
>> End Sub
>> '----------------------------------------------------
>> Private Sub Dropdown_Change(ByVal Target As Range)
>> Dim oFoundCell As Range
>> Dim iTargetCol As Long
>> If Not Intersect(Range(kList1), Target) Is Nothing Then
>> If Target.Count = 1 Then
>> With Data.Range(kList1Hnd)
>> Set oFoundCell = .Find(what:=Target.Value,
>> LookIn:=xlValues)
>> If oFoundCell Is Nothing Then
>> MsgBox "Selected group has no clients"
>> Exit Sub
>> End If
>> End With
>> 'load the List2 dropdown and set the default to item 1
>> iTargetCol = oFoundCell.Column
>> fzCreateValidationList2 Target.Offset(4, 0), iTargetCol,
>> Target
>> Target.Offset(4, 0).Value = Data.Range(kList2Hnd &
>> iTargetCol).Value
>> End If
>> End If
>> End Sub
>> '----------------------------------------------------
>> --
>> Baapi
>
>

Thanks
--
Baapi


"Otto Moehrbach" wrote:

> I just looked at your code and saw "Dropdown_Change" in the name of the
> macro. That's supposed to be "Worksheet_Change". HTH Otto
> "Otto Moehrbach" <[email protected]> wrote in message
> news:O5ms8l%[email protected]...
> > Put a little message box at the start of your code. Something like:
> > MsgBox "Working"
> > Now make a change in some other cell, any cell. The message box should
> > pop up. If it does and it still doesn't when you make a change to the
> > Data Validation cell, I would say that you have an older version of Excel
> > that will not react to a change in a Data Validation cell. HTH Otto
> > "Baapi" <[email protected]> wrote in message
> > news:[email protected]...
> >> Hi,
> >> I've this following code in my sheet to create a dynamic "Data validated
> >> cells (to list).
> >> But this is not getting triggered by the change of value in the first
> >> Data
> >> validated (to list) cell
> >>
> >> Can someone please let me know the solution? I've spent my whole day on
> >> this
> >> with no results/.......
> >>
> >> Thanks
> >>
> >> Option Explicit
> >> '----------------------------------------------------
> >> Private Sub Worksheet_Calculate()
> >> Dropdown_Change ActiveCell
> >> End Sub
> >> '----------------------------------------------------
> >> Private Sub Dropdown_Change(ByVal Target As Range)
> >> Dim oFoundCell As Range
> >> Dim iTargetCol As Long
> >> If Not Intersect(Range(kList1), Target) Is Nothing Then
> >> If Target.Count = 1 Then
> >> With Data.Range(kList1Hnd)
> >> Set oFoundCell = .Find(what:=Target.Value,
> >> LookIn:=xlValues)
> >> If oFoundCell Is Nothing Then
> >> MsgBox "Selected group has no clients"
> >> Exit Sub
> >> End If
> >> End With
> >> 'load the List2 dropdown and set the default to item 1
> >> iTargetCol = oFoundCell.Column
> >> fzCreateValidationList2 Target.Offset(4, 0), iTargetCol,
> >> Target
> >> Target.Offset(4, 0).Value = Data.Range(kList2Hnd &
> >> iTargetCol).Value
> >> End If
> >> End If
> >> End Sub
> >> '----------------------------------------------------
> >> --
> >> Baapi
> >
> >
>
>
>