CODEWITHSUNDEEP
vbaexcelrangetarget
RatuNiko
RatuNiko

Reputation: 23

Problems getting excel to run vba code when a cell is selected

I have written the function below, but I get an error that this is unable to compile due to a time mismatch on the If intersect... line. Debugging shows the value of the selected cell when I hover over 'Target' rather than the range (I don't know if this is indicative of the problem)

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Dim lastEntry As Integer
Dim shiftEntries As Range

lastEntry = LastEntryRow()
Set shiftEntries = Range("A11:L" & lastEntry)

If Intersect(Target, shiftEntries) Then
    Dim shiftDate As String
    shiftDate = Cells(Target.Row, 1).Value
    Cells(10, 15) = ShiftsInSevenDays(shiftDate)
End If
End Sub

Any help is much appreciated.

Upvotes: 2

Views: 54

Answers (1)

nbayly
nbayly

Reputation: 2167

Please try the following ammended code:

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Dim lastEntry As Integer
Dim shiftEntries As Range

lastEntry = LastEntryRow()
Set shiftEntries = Range("A11:L" & lastEntry)

If Not Intersect(Target, shiftEntries) Is Empty Then
    Dim shiftDate As String
    shiftDate = Cells(Target.Row, 1).Value
    Cells(10, 15) = ShiftsInSevenDays(shiftDate)
End If
End Sub

Intersect will return a range not a boolean (TRUE or FALSE). I am unsure if you should use Is Empty or Is Nothing, but it's one of the two. Regards,

Upvotes: 1

Related Questions