Static Date & Time Corresponding To Cell Change

Re: Static Date & Time Based On Update Sequence

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

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

Further to Dave's code:

Private Sub Worksheet_Change(ByVal Target As Range)
    If Target.Column = 1 Then
        Target(1, 4) = Date
        Target(1, 5) = Now
        Columns("B:C").AutoFit
    End If
End Sub


Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Application.EnableEvents = False
    If Target.Column = 6 Then
        Target = Date
        Target(1, 2) = Now
        Target(1, 3) = "=RC[-2]-RC[-4]"
        Target(1, 4) = "=RC[-2]-RC[-4]"
    End If
Application.EnableEvents = True
End Sub

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

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...)

Quote

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")

Display More

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:

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

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:

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:

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:

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.

MsgBox Target(1, 1).Address

Is the same as/shorthand for:

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:

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?