Static Date & Time Corresponding To Cell Change

  • Hi All,


    I have two related queries
    Don’t know whether this is possible


    REQUEST 1


    What I need is lets say I have 3 columns Name, Date & Time


    Name field has a drop down menu having a list of names


    Now what I need is - lets say from the dropdown menu someone selects a name - THEN at the same instance the Time & Date fields gets populated with the Date & Time of that update.


    and that Date & Time shouldn’t change if someone selects the same or different name in the row below


    Is it possible ?


    Kindly assist in lay man terms as I am a novice to Excel



    REQUEST 2


    In this format what I need is like above , the start date & start time should update real time ONLY when a name is selected


    and when the End date is selected the End time , Date Elapsed & Time Elapsed should Populate automatically.


    However there should be NO effect if data is entered or changed in the Request Id or Issue Cells - basically the triggers should only be Name field & End date Field


    Is this possible ?


    Can you attach an example sheet as well please



    Have enclosed an sample attachment



    Thanks in advance

  • Re: Static Date & Time Based On Update Sequence


    Right click on the sheet name tab, choose View Code and paste in;

    Code
    Private Sub Worksheet_Change(ByVal Target As Range)
        If Target.Column = 1 Then
            Target(1, 2) = Time
            Target(1, 3) = Date
            Columns("B:C").AutoFit
        End If
    End Sub
  • Re: Static Date & Time Corresponding To Cell Change


    Though the time is coming where date is and vice versa , its just a minor thing that I can fix - hey I need to use my brain some where right.


    Thanks a lot Dave - I dont know what I would have done without this site and people like you.


    Dave if its not too much can you give a solution for the Request 2


    I think its a wee bit more complicated - if you can create a code without any changes to the present format - example of request 2 is also entered in the above enclosed work sheet.


    Once again thanks mate

  • Re: Static Date & Time Corresponding To Cell Change


    Further to Dave's code:



    You will also need to format your columns:
    D & F as date
    E & G as time
    H as a number
    I as "[h]:mm" (or "[h]:mm:ss")


    This will work when anybody selects a cell in the end date column (F)


    HTH

  • Re: Static Date & Time Corresponding To Cell Change


    This is an awesome site - and such nice people willing to share - Thanks Cheeky Charlie


    And to think I was running around after so called experts in my work place for the last few weeks : :) thanks all


    I have few more queries and I want to ask them all togather but I wont flood one post a day



    Cheers guys[hr]*[/hr] Auto Merged Post Until 24 Hrs Passes;[dl]*[/dl]It works great Cheeky


    howevr instead of both date and time coming in one cell can I have them seperate as per my example


    I guess both are coming togather because of the CODE now


    Is it possible to break it up?


    Thanks[hr]*[/hr] Auto Merged Post Until 24 Hrs Passes;[dl]*[/dl]I found that I can isolate the time by formating the cell to Time


    however what can be done about the start date in the Request 2 example


    also


    couple of Added queries


    1) is it possible not to have an auto date fill at the " End Date: trigger " Instead to let the trigger be activated only after a date has been entered or after a prompt- to avoid any accidental trigger (eg the cell is clicked accidentally while browinsg through)


    2) if I want to create an alarm / alert so that I would be notified if the date elapsed exceeds say 2 days - using TODAY()>($A$1-2) in cell I or J how would I do it so that both the code & formulas dont clash.


    Refr Link : Creating An Alarm Based On Date Expiry[hr]*[/hr] Auto Merged Post Until 24 Hrs Passes;[dl]*[/dl]Is my last post a bit confusing ? if it is i'll be happy to rework on it


    Cheers[hr]*[/hr] Auto Merged Post Until 24 Hrs Passes;[dl]*[/dl]

  • Re: Static Date & Time Corresponding To Cell Change


    Crikey, you've a lot to say...
    First, thanks for your response, it makes all the difference when people are grateful.
    Second, If you read my post, I do tell you how to format the columns in your worksheet so it looks how you asked (I am very thorough...)



    If this doesn't make sense, please do ask the so called experts :wink: in your office - this is quite basic.


    Third...

    Quote


    and when the End date is selected the End time , Date Elapsed & Time Elapsed should Populate automatically.


    It's what you asked for!!!


    Anyway, a quick solution would be to insert a couple of lines like this:

    Code
    If Target.Column = 6 Then
        if target.value = "end" then 'this is an added line
            Target = Date 
            Target(1, 2) = Now 
            Target(1, 3) = "=RC[-2]-RC[-4]" 
            Target(1, 4) = "=RC[-2]-RC[-4]"
        end if  'so is this
        End If


    This would make your code trigger if someone types "end" (without quotes) in the end date column.


    Fourth: Your last question should absolutely be another post - remember how this forum is designed to help people to find solutions to their problems. I would also suggest you search on this topic heavily before you ask a question - as far as I can tell it has been covered quite thoroughly already.


    HTH

  • Re: Static Date & Time Corresponding To Cell Change


    yeah, delete the line


    Coding is generallyvery logical - it's not hard to understand because it's crazy or irrational, like a woman, it's hard to understand because you need to learn a language in order to use it - like... um, well, something else.

  • Re: Static Date & Time Corresponding To Cell Change


    Thanks again Cheeky


    I had tried removing only ".AutoFit" however gave an error.


    Thought "Columns("B:C")" more important because in the entire code thats the only place where column name is mentioned.


    Guess I was a tad stupid


    Thanks anyway for the propmt help.[hr]*[/hr] Auto Merged Post Until 24 Hrs Passes;[dl]*[/dl]Now I got it the Columns are represented by number (1,2,3) and not Char like A, B ,C.


    While at it can you clarify these queries as well or shall I create a new thread ?


    I'll ask in a new thread incase it helps someone else

  • Re: Static Date & Time Corresponding To Cell Change


    Sorry, I wasn't entirely clear (you have very responsibly put a low knowledge level down and I ignored it utterly! Sorry):


    Delete the line.


    The whole line with autofit on it.


    Not just .autofit


    HTH

  • Re: Static Date & Time Corresponding To Cell Change


    General postscript:


    For those reading this thread, the target(y,x) which confused bluecondor temporarily is effectively an abbreviated form of the offset property which can be very easily understood from the VBA help files.


    It has slightly different syntax in that target(y,x) has the target at 1,1 whereas offset starts from target = 0,0. This is probably easier to understand like this:

    Code
    Target(4, 5) = Target.Offset(3, 4)
    Target(0, 0) = Target.Offset(-1, -1) '!


    I could understand a justification for using the longer wording, it makes it slightly easier to "read" as you have the offset written explicitly, so helps you understand what the numbers are for. The difference in speed is minimal, but that would depend on what you're doing...


    HTH

  • Re: Static Date & Time Corresponding To Cell Change


    Just to be clear on this, it's not an abbreviated form of the offset property. It's an abbreviated form of the item property. The reason this syntax is permissable is that the default property of the range object is the item property....


    That might sound pedantic, but there are clear differences.


    As you mentioned, the item property is 1-based.


    Also, the item property will only ever return a single cell range, whereas the offset property may return a multi cell range. For example, compare these two:

    Code
    Sub foo()
        MsgBox Range("a1:c1")(2).Address
        MsgBox Range("a1:c1").Offset(1).Address
    End Sub


    Also, note that these two return different results:

    Code
    MsgBox Range("a1:d1")(2).Address
        MsgBox Range("a1:a4")(2).Address


    The differences are less apparent when working with a single-cell parent range, but I think that precise understanding is required to avoid confusion.

    Code
    MsgBox Target(1, 1).Address


    Is the same as/shorthand for:

    Code
    MsgBox Target.Item(1, 1).Address



    Colin

  • Re: Static Date & Time Corresponding To Cell Change


    hence

    Quote

    effectively

    in this example - people at a level for thisthread are not likely to follow the full explanation.

  • Re: Static Date & Time Corresponding To Cell Change


    I understand where you are coming from and I'm all for keeping things simple, but introducing the notion that it is effectively the same as the offset property only confuses the situation. People who are learning from this thread would be learning incorrectly and will run into problems later on. These two properties are different to each other, just as they are different to the cells property, and each should be treated separately.


    Taking a simple example:

    Code
    Private Sub Worksheet_Change(ByVal Target As Range)
        MsgBox "item " & Target(1).Address
        MsgBox "offset " & Target.Offset(0).Address
    End Sub


    If one pastes this into a sheet class module and then selects a multicell range, say A1:A10, types in the formula =1 and completes the entry with CTRL+SHIFT+ENTER, the two properties yield different results. They are not effectively the same.


    Colin

  • Re: Static Date & Time Corresponding To Cell Change


    Hey, let's not get grumpy with each other. In principle we agree; you are 100% right that offset is not the same. I think you understand that the point of my post was to explain to people who couldn't "decipher" the code how what is effectively an offset is being achieved. (That is why bluecondor could not easily tweak the code to his own spec).


    Anyone that gets this far will hopefully be able to understand the difference between "something" and "effectively achieving something". I thought I covered in my original post on this concept, but to be fair, I had left it a little ambiguous (even with the italics!)


    Ultimately, nobody will be any the worse off for reading your thorough dissection and I wouldn't remove it if it were up to me.


    As for all the examples proving the same point... I guess it makes it inescapably clear! :wink:

  • Re: Static Date & Time Corresponding To Cell Change


    No grumpiness here and I certainly didn't mean to convey any either... and who said anything about removing it? ;)

Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!