function IsEmpty doesn't work for Workbook_SheetSelectionChange

[Abdo]

hI
I try subtracting column C from column D when call my macro by using function isempty but doesn't work.
so what I look for . for instance when select C2 & D2 and clear the values , then the cell E2 should be 0 .

Private Sub Workbook_SheetSelectionChange(ByVal Sh As Object, ByVal Target As Range)
If Target.CountLarge > 1 Then Exit Sub
If IsEmpty(Target.Column = 3 And Target.Column) = 4 Then
Application.EnableEvents = False
Call test
Application.EnableEvents = True
End If
End Sub

any idea to achieve it please?

 

[jasonb75]

The procedure that you have chosen only works when you select cells, but when you delete what is in them. For what you want, you need to use worksheet_change.

You have specified that the procedure should exit when the target range is more than one cell yet you say that you are changing two cells at a time. Two is more than one so you are telling it to exit without doing anything.

IsEmpty is for checking object variables, not cell values. To check if a cell is empty you should use something like .Value = ""

There may be other things that you're doing wrong, with such a vague description details are easily missed.

 

[Abdo]

For what you want, you need to use worksheet_change.

what do you mean?
I put the values in column C,D then should show the values in column E after subtracting . what's the problem ?

You have specified that the procedure should exit when the target range is more than one cell yet you say that you are changing two cells at a time. Two is more than one so you are telling it to exit without doing anything.

if you mean this

If Target.CountLarge > 1 Then Exit Sub

it doesn't make difference even delete this line

There may be other things that you're doing wrong, with such a vague description details are easily missed.

just select two cells together . there is no error
I don't think to can post more details . the matter just depends on select two cells together
just if you need post the code where call from it , then I will do it.

 

[Jaafar Tribak]

As Jasonb kindly explained, you want the Worksheet_Change event .

so what I look for . for instance when select C2 & D2 and clear the values , then the cell E2 should be 0 .

I think, this is what you are trying to achieve :

Place this In the ThisWorkbook Module :

Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)
    With Target
        If .CountLarge > 1 Then Exit Sub
        Select Case .Column
            Case Is = 3
                If IsEmpty(Target) And IsEmpty(.Offset(, 1)) Then
                    .Offset(, 2) = 0
                End If
            Case Is = 4
                If IsEmpty(Target) And IsEmpty(.Offset(, -1)) Then
                    .Offset(, 1) = 0
                End If
        End Select
    End With
End Sub

Since the event handler is the ThisWorkbook module, the code will fire in all worksheets. I am not sure if that is intended. If not, move the code to the module of the worksheet you are working on.

IsEmpty is for checking object variables, not cell values. To check if a cell is empty you should use something like .Value = ""

Actually, IsEmpty is for checking Variant variables to see if they have been initialized or not. Although the Target arg is a Range object, it implicitly resolves to its Default value Property which is of variant type.

The line below returns TRUE when deleting a cell content
Debug.Print VarType(Target) = vbEmpty

 

[Abdo]

@Jaafar Tribak thanks for that

you want the Worksheet_Change event .

to understand why insest for that. somtimes the user will clear two cells together accidentally use select two cells together to clear the contenets , then the balance remains in column E without change . it doesn't make sense show the values in column E after subtract column C from D should be zero . I can't gurantee the user will use change the values for each cell individaully as you suggest use change event instead of select . any suggestion to fix this problem? I'm all of my ears.

 

[Alex Blakenburg]

See if this does what you are after:

Private Sub Worksheet_Change(ByVal Target As Range)

    Dim rngInputs As Range
    Dim rngRows As Variant
    
    Set rngInputs = Intersect(Target, Columns("C:D"))

    If Not rngInputs Is Nothing Then
        Application.EnableEvents = False
        
        For Each rngRows In rngInputs.Rows
            If rngRows.Row <> 1 Then
                If IsEmpty(Cells(rngRows.Row, "C")) Or IsEmpty(Cells(rngRows.Row, "D")) Then
                    Cells(rngRows.Row, "E") = 0
                End If
            End If
        Next rngRows
        
        Application.EnableEvents = True
    End If

End Sub

 

[Abdo]

it works , but still my macro to subtract column C from coumn D .where I put call test inside the code ,please ?

 

[Alex Blakenburg]

Try this:

        For Each rngRows In rngInputs.Rows
            Debug.Print rngRows.Row
            If rngRows.Row <> 1 Then
                If IsEmpty(Cells(rngRows.Row, "C")) Or IsEmpty(Cells(rngRows.Row, "D")) Then
                    Cells(rngRows.Row, "E") = 0
                Else
                    Cells(rngRows.Row, "E") = Cells(rngRows.Row, "D") - Cells(rngRows.Row, "C")
                End If
            End If
        Next rngRows

 

[Abdo]

should be and not or as I said

select two cells together to clear the contenets

If IsEmpty(Cells(rngRows.Row, "C")) and IsEmpty(Cells(rngRows.Row, "D")) Then

I tought this idea could not be possible , but you did it
all of things are great !
many thanks for you ,Jaafar Tribak and jasonb

 

[Alex Blakenburg]

Thanks for the feedback. Glad we could help.