MrExcel Publishing
Your One Stop for Excel Tips & Solutions

functions....VBA


Posted by Ian on August 31, 2001 2:15 AM

Hi all

i'm trying to create a function that when an entry is made in one cell and leave that cell another cell will add the current time and remain thus.

effectively it would be like pressing Ctrl+Shft+:
thanks for any help

Ian


Posted by Ivan F Moala on August 31, 2001 5:15 AM

Hi Ian
To do this you will need to use the
application or sheets change event.

Right click the sheet tab and select view code
The paste this code in.
The code will place the time next to the cell that
is changed.
Alter as required or repost if this is not what
you were after.

Private Sub Worksheet_Change(ByVal Target As Range)

Application.EnableEvents = False

If Target.Text = "" Then GoTo Skip

Target.Offset(0, 1) = Format(Now, "hh:mm")

Skip: Application.EnableEvents = True

End Sub

Ivan

Posted by Ian on August 31, 2001 6:41 AM

Can't get it to work

I've copied and pasted this into VB and changed the offset to (0, 9)
which is the place i'd like the time to be.

also, the range to check is C7:C31 and place the time in L7:L31.

I was trying to put a formula in L that was something like:

=if(c7="","",AddTime)

AddTime being the macro

thanks again

Ian

Posted by Ian on August 31, 2001 6:44 AM

Re: Can't get it to work...forgot to mention

the range to check refers to if c7 then l7 changes
if c8 then l8 etc.
NOT if c7 the l7:l31 changes as it now reads to me.

Ian

Posted by Ivan F Moala on August 31, 2001 6:53 AM

Re: Can't get it to work

This should work.....changing cells c7:c31 will
put the time in column L.


Ivan

Private Sub Worksheet_Change(ByVal Target As Range)
'also, the range to check is C7:C31 and place the time in L7:L31.
Dim RangeToCheck As Range

Set RangeToCheck = Application.Intersect(Range("C7:C31"), Range(Target.Address))
If RangeToCheck Is Nothing Then Exit Sub

Application.EnableEvents = False
If Target.Text = "" Then GoTo Skip
Target.Offset(0, 9) = Format(Now, "hh:mm")
Skip: Application.EnableEvents = True

End Sub

Posted by Ian on August 31, 2001 7:27 AM

Re: Still....Can't get it to work

I've tried again (looks like the range thing's ok) and unfortunatly nothing is appearing in the L column???

what I'm doing is typing something in c7 (I've been typing my name) and nothing appears anywhere on the sheet???

with my very limited knowledge of VB it looks fine

Ian