Results 1 to 8 of 8
  1. #1
    Star Lounger
    Join Date
    Mar 2001
    Posts
    85
    Thanks
    0
    Thanked 0 Times in 0 Posts

    getting a date to stick (97)

    If I populate cell A1, I want the current date to appear automatically in B1.
    The date in B1 has to stick.

    A simple solution (macro free) would be great.

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 28 Times in 28 Posts

    Re: getting a date to stick (97)

    I assume you mean that you don't want the date in B1 to change after it has been set.

    I'm afraid I don't know how to accomplish what you want without using VBA code.

  3. #3
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: getting a date to stick (97)

    This can not be done with "A simple solution (macro free)." The following code, placed in the Worksheet Change event routine in the module behind the sheet in question will do what you asked.

    <pre>Option Explicit

    Private Sub Worksheet_Change(ByVal Target As Range)
    If Not Intersect(Target, Range("A1")) Is Nothing Then
    If Range("B1").Value = "" Then
    Application.EnableEvents = False
    Range("B1").Value = Date
    Application.EnableEvents = True
    End If
    End If
    End Sub
    </pre>

    Legare Coleman

  4. #4
    Gold Lounger
    Join Date
    Feb 2004
    Location
    Cape Town, RSA
    Posts
    3,444
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: getting a date to stick (97)

    Hi Legare,
    Can I ask why you use Application.EnableEvents in you code. I know this allows you to continue working in Excel while the macro is running in the background...and I assumed that is is useful to use in big looping macros that may take long to run. But your code is just putting a date in the cell. Why are you using EnableEvents? Can you explain please?
    Thanx
    Regards,
    Rudi

  5. #5
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 28 Times in 28 Posts

    Re: getting a date to stick (97)

    One event can trigger others. For example, setting a cell value in the Worksheet_Change event may cause other cells to be recalculated, thus firing the Worksheet_Calculate event. Cascading events may cause serious slowdown. Therefore it is a good idea to turn off the handling of other events temporarily within an event procedure, by setting EnableEvents to False, and to turn it on again at the end of the code.

  6. #6
    Gold Lounger
    Join Date
    Feb 2004
    Location
    Cape Town, RSA
    Posts
    3,444
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: getting a date to stick (97)

    Wow, I have now learned something new and fairly important. This is going straight into my archive!
    I only thought of ........... oh, wait a minute!!!!!!!!!!!!!!! I seem to be confusing this with "DoEvents". This allows a macro to run while allowing you to continue with soething else in excel.
    EnableEvents is something different, and thanks to Legares example and your explanation I understand how to use it.

    Many many thanx!
    Regards,
    Rudi

  7. #7
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: getting a date to stick (97)

    In addition to what Hans said: In this particular case, the code is in the Change Event routine. My code changes a cell on the same sheet that this Change Event routine is for. If I don't disable events, then immediately when I change that cell, the routine will be called again. Since the cell I am changing is not one that the routine is interested in, that is a useless execution of code. Disabeling events before changing the cell makes it more efficient.
    Legare Coleman

  8. #8
    Gold Lounger
    Join Date
    Feb 2004
    Location
    Cape Town, RSA
    Posts
    3,444
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: getting a date to stick (97)

    Thanx for informing me of that Legare. Your code has certainly enlightened me with new info!
    Cheers
    Regards,
    Rudi

Posting Permissions

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