“Dependents.Count” statement restarting macro when used in “Worksheet_SelectionChange” Sub

[Ethun_Hunt]

I am trying to get number of dependents of selected range (if number of cells in selection is one). When I use following code it works without any error for all cells (for cells without any dependents and for cells having dependents)

'Case 1
Sub Example()
Dim rng As Excel.Range
Set rng = Excel.SelectionIf Target.Count =1 Then
If HasDependents(rng) Then
MsgBox rng.Dependents.Count &" dependancies found."
Else
MsgBox "No dependancies found."
End If
End If
End Sub

Public Function HasDependents(ByVal Something As Excel.Range)As Boolean
On Error Resume Next
HasDependents = Something.Dependents.Count
End Function

But when same logic is used in Worksheet_SelectionChange (as shown in following code)

'Case 2
Sub Worksheet_SelectionChange(ByVal Target As Excel.Range)
If Target.Count =1 Then
If HasDependents(Target) Then
MsgBox Target.Dependents.Count &" dependancies found."
Else
MsgBox "No dependancies found."
End If
End If
End Sub

Public Function HasDependents(ByVal Something As Excel.Range)As Boolean
On Error Resume Next
HasDependents = Something.Dependents.Count
End Function

it works for cells not having any dependents, but for cells having dependents "Dependents.Count" restarts macro. After executing

HasDependents = Something.Dependents.Count

Statement, macro restarts and executes

Sub Worksheet_SelectionChange(ByVal Target As Excel.Range)

again. My question is why macro is restarting in second case after executing "Dependents.Count"?
For "Worksheet_SelectionChange" I have put the code in Sheet Code area not in module.

 

[Hui]

Prior to:
HasDependents = Something.Dependents.Count

You need to put a line like:

Application.Enableevents=False
HasDependents = Something.Dependents.Count
Application.Enableevents=True

Because by changing a value, you are changing the cells value and that is a WorksheetChange event

Disabling events stops that being seen

 

[NARAYANK991]

Hi ,

The issue is not to do with the Worksheet_Change event procedure ; after all , the actual event procedure being used is the Worksheet_SelectionChange procedure.

What I find is that the HasDependents property is recursive ; I might be wrong.

If the Worksheet_SelectionChange event procedure is changed to handle this , it works correctly. This would not be the case if it were being called again and again.

Try this code :

Sub Worksheet_SelectionChange(ByVal Target As Excel.Range)
    If ActiveCell.Address <> Target.Address Then Exit Sub
    If Target.Count = 1 Then
      If HasDependents(Target) Then
          MsgBox Target.Address & " has " & Target.Dependents.Count & " dependencies."
      Else
          MsgBox "No dependencies found."
      End If
    End If
End Sub

Public Function HasDependents(ByVal Something As Excel.Range) As Boolean
                On Error Resume Next
                HasDependents = Something.Dependents.Count
End Function

Narayan

 

[Lori]

Also see the latest blog post of @Colin Legg:

https://colinlegg.wordpress.com/2016/03/06/unwanted-worksheet-events-maybe-specialcells-is-to-blame/

 

[NARAYANK991]

Also see the latest blog post of @Colin Legg:

https://colinlegg.wordpress.com/2016/03/06/unwanted-worksheet-events-maybe-specialcells-is-to-blame/

Hi Lori ,

Thanks for the link.

Try out the two methods and see for yourself.

With the use of Application.EnableEvents in the called function , the recursive behaviour is not eliminated ; when the cursor is moved to a cell which does not have any dependencies , the appropriate message box is displayed once. When the cursor is moved to a cell which does have a dependency , both message boxes are displayed.

With the use of Application.EnableEvents in the Worksheet_SelectionChange event procedure , the recursive behaviour is eliminated ; irrespective of whether the cursor is moved to a cell which has or does not have any dependencies , the appropriate message box is displayed only once.

With the use of the additional line of code in the Worksheet_SelectionChange event procedure , the appropriate message box is displayed only once in any case.

Narayan