function IsEmpty doesn't work for Workbook_SheetSelectionChange

Abdo

Board Regular
Joined
May 16, 2022
Messages
201
Office Version
  1. 2019
  2. 2010
Platform
  1. Windows
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 .
VBA Code:
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?
 

Excel Facts

Why does 9 mean SUM in SUBTOTAL?
It is because Sum is the 9th alphabetically in Average, Count, CountA, Max, Min, Product, StDev.S, StDev.P, Sum, VAR.S, VAR.P.
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.
 
Upvote 0
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
VBA Code:
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.
 
Upvote 0
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 :
VBA Code:
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
 
Last edited:
Upvote 0
@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.
 
Upvote 0
See if this does what you are after:
VBA Code:
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
 
Upvote 0
Solution
it works , but still my macro to subtract column C from coumn D .where I put call test inside the code ,please ?
 
Upvote 0
Try this:
Rich (BB code):
        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
 
Upvote 0
should be and not or as I said
select two cells together to clear the contenets
Rich (BB code):
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
 
Upvote 0

Forum statistics

Threads
1,223,237
Messages
6,170,928
Members
452,366
Latest member
TePunaBloke

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top