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

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" <(E-Mail Removed)> skrev i meddelelsen
news:(E-Mail Removed)...
>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
>

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" <(E-Mail Removed)> wrote in message
news:%(E-Mail Removed)...
> 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" <(E-Mail Removed)> skrev i meddelelsen
> news:(E-Mail Removed)...
>>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
>>
>

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

"Patrick C. Simonds" <(E-Mail Removed)> skrev i meddelelsen
news:usPdT2$(E-Mail Removed)...
> 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" <(E-Mail Removed)> wrote in message
> news:%(E-Mail Removed)...
>> 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" <(E-Mail Removed)> skrev i meddelelsen
>> news:(E-Mail Removed)...
>>>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
>>>
>>
>