Insert Criteria Based on Current Month
 

Hello,

I have a worksheet that lists all awards won since January 1, 2006. Each
month I have to send a report that shows the wins for that month. I put a
"N" next to the award date to denote "new this month". Is there any way to
automatically do this when I put in the date of the award and it is the
current month? Thanks for your help.

Mark

Insert Criteria Based on Current Month
 

Private Sub Worksheet_SelectionChange(ByVal Target As Range)

On Error GoTo ws_exit
Application.EnableEvents = False
If Target.Column = 5 Then
If Month(Target.Value) = Month(Date) Then
Target.Offset(0, 1) = "N"
End If
End If

ws_exit:
Application.EnableEvents = True
On Error GoTo 0

End Sub

This is worksheet event code, which means that it needs to be
placed in the appropriate worksheet code module, not a standard
code module. To do this, right-click on the sheet tab, select
the View Code option from the menu, and paste the code in.


--

HTH

Bob Phillips

(remove nothere from the email address if mailing direct)

"Mark Jackson" wrote in message
...
Hello,

I have a worksheet that lists all awards won since January 1, 2006. Each
month I have to send a report that shows the wins for that month. I put a
"N" next to the award date to denote "new this month". Is there any way
to
automatically do this when I put in the date of the award and it is the
current month? Thanks for your help.

Mark

Insert Criteria Based on Current Month
 

Bob,

I copied and paste the event code into the worksheet and now I get an error
when I open the workbook. This is the error: Microsoft Excel cannot calulate
a formula. Cell references in the formula's result, creating a circular
reference. Is there anyway to delete this or modify so it will work? Any
help is appreciated.

Sincerely,

Mark

"Bob Phillips" wrote:

Private Sub Worksheet_SelectionChange(ByVal Target As Range)

On Error GoTo ws_exit
Application.EnableEvents = False
If Target.Column = 5 Then
If Month(Target.Value) = Month(Date) Then
Target.Offset(0, 1) = "N"
End If
End If

ws_exit:
Application.EnableEvents = True
On Error GoTo 0

End Sub

This is worksheet event code, which means that it needs to be
placed in the appropriate worksheet code module, not a standard
code module. To do this, right-click on the sheet tab, select
the View Code option from the menu, and paste the code in.


--

HTH

Bob Phillips

(remove nothere from the email address if mailing direct)

"Mark Jackson" wrote in message
...
Hello,

I have a worksheet that lists all awards won since January 1, 2006. Each
month I have to send a report that shows the wins for that month. I put a
"N" next to the award date to denote "new this month". Is there any way
to
automatically do this when I put in the date of the award and it is the
current month? Thanks for your help.

Mark

Insert Criteria Based on Current Month
 

I cannot see that this is anything to do with this code, as it does not
create any formulae. It sounds as if you have a formula with a circular
reference. It should show the offending cell in the status bar, check it
back.

--

HTH

Bob Phillips

(remove nothere from the email address if mailing direct)

"Mark Jackson" wrote in message
...
Bob,

I copied and paste the event code into the worksheet and now I get an
error
when I open the workbook. This is the error: Microsoft Excel cannot
calulate
a formula. Cell references in the formula's result, creating a circular
reference. Is there anyway to delete this or modify so it will work? Any
help is appreciated.

Sincerely,

Mark

"Bob Phillips" wrote:

Private Sub Worksheet_SelectionChange(ByVal Target As Range)

On Error GoTo ws_exit
Application.EnableEvents = False
If Target.Column = 5 Then
If Month(Target.Value) = Month(Date) Then
Target.Offset(0, 1) = "N"
End If
End If

ws_exit:
Application.EnableEvents = True
On Error GoTo 0

End Sub

This is worksheet event code, which means that it needs to be
placed in the appropriate worksheet code module, not a standard
code module. To do this, right-click on the sheet tab, select
the View Code option from the menu, and paste the code in.


--

HTH

Bob Phillips

(remove nothere from the email address if mailing direct)

"Mark Jackson" wrote in message
...
Hello,

I have a worksheet that lists all awards won since January 1, 2006.
Each
month I have to send a report that shows the wins for that month. I
put a
"N" next to the award date to denote "new this month". Is there any
way
to
automatically do this when I put in the date of the award and it is
the
current month? Thanks for your help.

Mark