CODEWITHSUNDEEP
vbaexcelobjectevents
hod
hod

Reputation: 761

Event Macro Throws an Error "Object Required"

I have this simple Event Macro, which for some reason throws me an

Object Required

error on this line If Not AppDate Is Nothing Then. Any idea what might be causing this?

Option Explicit

Private Sub Worksheet_Change(ByVal Target As Range)
    Dim LastRow As Integer
    Dim ThisRow As String
    Dim AppDate As Variant
    Application.EnableEvents = False

    LastRow = Cells(Rows.Count, "C").End(xlUp).Row
    If Target.Column = 3 Then
        ThisRow = Target.Row
        AppDate = Target.Value
        If Not AppDate Is Nothing Then
            (...) Recalculate Date in Column F
        Else
        End If
    End If

    Application.EnableEvents = True
End Sub

Any suggestions would be greatly appreciated. Thank you!

Upvotes: 1

Views: 599

Answers (2)

cyboashu
cyboashu

Reputation: 10433

Is operator works with objects and not values. 

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    Dim x As Variant
    Dim y As New Collection
    Dim z As Object

    x = Target.Value

    If IsEmpty(x) Then
        MsgBox "may be use this."
    End If

    MsgBox TypeName(x)

    If y Is Nothing Then
     MsgBox "Works"
    End If

    If z Is Nothing Then
     MsgBox "Works"
    End If

    If x Is Nothing Then '/This won't work

    End If


End Sub

Upvotes: 7

ashleedawg
ashleedawg

Reputation: 21619

Use:

…
If Not IsEmpty(AppDate) 
…

To help clarify, here is a lengthy tutorial about Nothing. lol

Upvotes: 4

Related Questions