Worksheet Change Event
 

Hello. Within the range of Sheet1 D4:D2500, how can I take a date that is
entered into a cell and convert the DAY to a 1, keeping all else the same?

So for example, if the user enters 12/15/2005 in cell D25, then I would like
the change event to trigger and convert that cell into 12/1/2005.

Thank you!

Worksheet Change Event
 

try

Sub changedayto1()
For Each c In Selection
c.Value = DateSerial(Year(c), Month(c), 1)
Next

--
Don Guillett
SalesAid Software

"Steph" wrote in message
...
Hello. Within the range of Sheet1 D4:D2500, how can I take a date that is
entered into a cell and convert the DAY to a 1, keeping all else the same?

So for example, if the user enters 12/15/2005 in cell D25, then I would
like
the change event to trigger and convert that cell into 12/1/2005.

Thank you!

Worksheet Change Event
 

Hi Don,

I tried to apply what you gave me in a worksheet change event:
'Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Column = 4 And Target.Row 11 And Target.Row < 2500 Then
Cells(Target.Row, 4).Value = DateSerial(Year(Cells(Target.Row, 4)),
Month(Cells(Target.Row, 4)), 1)
End If
End Sub

It works, but it's throwing things into an eternal loop. I heard of this
happening to change events, but don't know how to fix it. Thanks.

"Don Guillett" wrote in message
...
try

Sub changedayto1()
For Each c In Selection
c.Value = DateSerial(Year(c), Month(c), 1)
Next

--
Don Guillett
SalesAid Software

"Steph" wrote in message
...
Hello. Within the range of Sheet1 D4:D2500, how can I take a date that
is
entered into a cell and convert the DAY to a 1, keeping all else the
same?

So for example, if the user enters 12/15/2005 in cell D25, then I would
like
the change event to trigger and convert that cell into 12/1/2005.

Thank you!

Worksheet Change Event
 

One way:

Private Sub Worksheet_Change(ByVal Target As Excel.Range)
With Target
If .Count 1 Then Exit Sub
If Not Intersect(Range("D4:D2500"), .Cells) Is Nothing Then
Application.EnableEvents = False
If IsDate(.Value) Then _
.Value = DateSerial(Year(.Value), Month(.Value), 1)
Application.EnableEvents = True
End If
End With
End Sub


In article ,
"Steph" wrote:

Hello. Within the range of Sheet1 D4:D2500, how can I take a date that is
entered into a cell and convert the DAY to a 1, keeping all else the same?

So for example, if the user enters 12/15/2005 in cell D25, then I would like
the change event to trigger and convert that cell into 12/1/2005.

Thank you!

Worksheet Change Event
 

See my other post.

In article ,
"Steph" wrote:

It works, but it's throwing things into an eternal loop. I heard of this
happening to change events, but don't know how to fix it. Thanks.

Worksheet Change Event
 

Thanks so much!

"JE McGimpsey" wrote in message
...
One way:

Private Sub Worksheet_Change(ByVal Target As Excel.Range)
With Target
If .Count 1 Then Exit Sub
If Not Intersect(Range("D4:D2500"), .Cells) Is Nothing Then
Application.EnableEvents = False
If IsDate(.Value) Then _
.Value = DateSerial(Year(.Value), Month(.Value), 1)
Application.EnableEvents = True
End If
End With
End Sub


In article ,
"Steph" wrote:

Hello. Within the range of Sheet1 D4:D2500, how can I take a date that
is
entered into a cell and convert the DAY to a 1, keeping all else the
same?

So for example, if the user enters 12/15/2005 in cell D25, then I would
like
the change event to trigger and convert that cell into 12/1/2005.

Thank you!