+ Reply to Thread
Results 1 to 6 of 6

Worksheet Change Event-change event to trigger

  1. #1
    Steph
    Guest

    Worksheet Change Event-change event to trigger

    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!



  2. #2
    Don Guillett
    Guest

    re: Worksheet Change Event-change event to trigger

    try

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

    --
    Don Guillett
    SalesAid Software
    donaldb@281.com
    "Steph" <noreply@nowhere.com> wrote in message
    news:ecIsO0B1FHA.2072@TK2MSFTNGP14.phx.gbl...
    > 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!
    >
    >




  3. #3
    Steph
    Guest

    re: Worksheet Change Event-change event to trigger

    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" <donaldb@281.com> wrote in message
    news:%23MsK%23$B1FHA.2008@TK2MSFTNGP10.phx.gbl...
    > try
    >
    > Sub changedayto1()
    > For Each c In Selection
    > c.Value = DateSerial(Year(c), Month(c), 1)
    > Next
    >
    > --
    > Don Guillett
    > SalesAid Software
    > donaldb@281.com
    > "Steph" <noreply@nowhere.com> wrote in message
    > news:ecIsO0B1FHA.2072@TK2MSFTNGP14.phx.gbl...
    > > 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!
    > >
    > >

    >
    >




  4. #4
    JE McGimpsey
    Guest

    re: Worksheet Change Event-change event to trigger

    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 <ecIsO0B1FHA.2072@TK2MSFTNGP14.phx.gbl>,
    "Steph" <noreply@nowhere.com> 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!


  5. #5
    JE McGimpsey
    Guest

    re: Worksheet Change Event-change event to trigger

    See my other post.

    In article <u3U#3HC1FHA.1040@TK2MSFTNGP14.phx.gbl>,
    "Steph" <noreply@nowhere.com> 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.


  6. #6
    Steph
    Guest

    re: Worksheet Change Event-change event to trigger

    Thanks so much!

    "JE McGimpsey" <jemcgimpsey@mvps.org> wrote in message
    news:jemcgimpsey-533ABA.14342818102005@msnews.microsoft.com...
    > 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 <ecIsO0B1FHA.2072@TK2MSFTNGP14.phx.gbl>,
    > "Steph" <noreply@nowhere.com> 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!




+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1