I've found a problem in Excel/VBA in the Worksheet_Change Event. I need to assign Target.Dependents to a Range, but if it has no dependents it brings up an error. I tried testing Target.Dependents.Cells.Count but that didn't work. Any Ideas?
Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Cells.Count > 1 OR Target.Dependents.Cells.Count = 0 Then Exit Sub
Dim TestRange As Range
Set TestRange = Target.Dependents
I've also tried "Target.Dependents Is Nothing".
Select the range of cells that you want to search. To search the entire worksheet, click any cell. On the Home tab, in the Editing group, click Find & Select, and then click Find. In the Find what box, enter the text—or numbers—that you need to find.
Excel ISBLANK function The ISBLANK function in Excel checks whether a cell is blank or not. Like other IS functions, it always returns a Boolean value as the result: TRUE if a cell is empty and FALSE if a cell is not empty. Where value is a reference to the cell you want to test.
The below formula can help you check if a range of cells is blank or not in Excel. Please do as follows. 1. Select a blank cell, enter formula =SUMPRODUCT(--(G1:K8<>""))=0 into the formula bar, and then press the Enter key.
Short answer, there is no way to test for dependents without raising an error, as the property itself is set to raise an error if accessed and there aren't any. I dislike the design but there is no way to prevent it without suppressing errors. AFAIK this is about the best you are going to be able to do with it.
Sub Example()
Dim rng As Excel.Range
Set rng = Excel.Selection
If HasDependents(rng) Then
MsgBox rng.Dependents.Count & " dependancies found."
Else
MsgBox "No dependancies found."
End If
End Sub
Public Function HasDependents(ByVal target As Excel.Range) As Boolean
On Error Resume Next
HasDependents = target.Dependents.Count
End Function
Explanation, if there are no dependents an error is raised and the value of HasDependents stays unchanged from the type default,which is false, thus false is returned. If there are dependents, the count value will never be zero. All non-zero integers convert to true, so when count is assigned as the return value, true is returned. It's pretty close to what you are already using.
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With