Cell specific code

[Patrick C. Simonds]

I want the code below to run only when cell B4 is selected and show the
Calendar only if the value in cell N2 is 0. If I click on cell B4 and cell
N2 is greater than 0, my message displays just as I want it to. But for some
reason Calendar displays no matter what cell I select. Where have I gone
wrong?


Private Sub Worksheet_SelectionChange(ByVal Target As Range)

If Not Application.Intersect(Target, Range("B4")) Is Nothing Then

If Range("N2").Value > 0 Then GoTo NotBlank
End If

Calendar.Show

GoTo Finished

NotBlank:
MsgBox "This Workbook contains Vacation data. That data must be removed
before you can change the year. If you want to start a new year, please use
the Template."

Finished:

End Sub

 

[Per Jessen]

Hi

If Not Application.Intersect(Target, Range("B4")) Is Nothing Then
If Range("N2").Value > 0 Then GoTo NotBlank
Calendar.Show
End If

Regards,
Per

 

[Patrick C. Simonds]

Problem is, now my MsgBox located at NotBlank: is displayed every time a
cell is clicked on, so this event does not seem to be restricted to clicking
on cell B4 only.

Is there any way to restrict this code to running only when cell B4 is
clicked on?

 

[Per Jessen]

This should do it:

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If Not Application.Intersect(Target, Range("B4")) Is Nothing Then
If Range("N2").Value > 0 Then
MsgBox "This Workbook contains Vacation data." & vbLf & _
"That data must be removed before you can change the year." _
& vbLf & "If you want to start a new year, please use the
Template."
Else
Calendar.Show
End If
End If
End Sub

Regards,
Per