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" wrote in message
...
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" wrote in message
...
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" wrote in message
...
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" wrote in message
...
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" wrote in message
...
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