using now() as static function

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

MINAL ZUNKE - 28 Jun 2005 13:11 GMT

Hi
Experts

I am preparing excel worksheet. I have developed macro in that sheet. The
purpose of that macro is " When programmer finishes programming, runs macro
using shortut key. The result is that particular cell changes colour to
green and 1 gets typed in it. No problem with that. In addition to that I
have used NOW() function to get the particular date and time in the cell just
adjacent to it on right hand side. When Programmer finishes programming for
different jobs and try to update the worksheet for particularjob by running
macro, THE WHOLE WORKSHEET GETS UPDATED to current date and time, which is
actually a problem.

My question is How can I make NOW() to work so it just puts specific date
and time and doesn't update entire worksheet?

Instead I try to use Ctrl+(;) and Ctrl +Shift+(:) to get the specific date
and time.

The problem here is that "It is acting static and whenever I try to Update
the programming cell for a particular job it gives the previous date and
time".

I will be thankful if somebody can give me solution to both these problems!!!

HAPPY PROBLEM SOLVING

Minal

Reply to this Message

JE McGimpsey - 28 Jun 2005 13:17 GMT

Take a look here:

http://www.mcgimpsey.com/excel/timestamp.html

> Hi
> Experts
[quoted text clipped - 24 lines]
>
> Minal

Reply to this Message

MINAL ZUNKE - 29 Jun 2005 12:27 GMT

hi Je Mc Gimpsey

The solution you gave me is working. thanks a lot.

one small problem, What I am trying to do is if I press digit 1 then only it
should automatically enter date in the adjacent column.

with your solution it works if I type any digit which is not my requirement.
In my requirement it should work only when I type "1" and for anyother
digit is should show "NA".
if you can give me a solution for that I will be very thankful.

thanks

> Take a look here:
>
[quoted text clipped - 28 lines]
> >
> > Minal

Reply to this Message

JE McGimpsey - 29 Jun 2005 17:04 GMT

One way:

Private Sub Worksheet_Change(ByVal Target As Excel.Range)
Dim bValid As Boolean
With Target
If .Count > 1 Then Exit Sub
If Not Intersect(Range("A2:A10"), .Cells) Is Nothing Then
Application.EnableEvents = False
If IsEmpty(.Value) Then
.Offset(0, 1).ClearContents
Else
If IsNumeric(.Value) Then _
If .Value = 1 Then _
bValid = True
With .Offset(0, 1)
If bValid Then
.NumberFormat = "dd mmm yyyy hh:mm:ss"
.Value = Now
Else
.NumberFormat = "General"
.Value = "NA"
End If
End With
End If
Application.EnableEvents = True
End If
End With
End Sub

> The solution you gave me is working. thanks a lot.
>
[quoted text clipped - 5 lines]
> digit is should show "NA".
> if you can give me a solution for that I will be very thankful.

Reply to this Message

sike11 - 20 Apr 2007 12:15 GMT

Hi JE,

Would this still work if it is a full column as the range rather than a
specified range - i.e G:G instead of G2:G10?
I am also getting an additional error of "Argument not optional". Any
thoughts on this?

Thanks in advance.

>One way:
>
[quoted text clipped - 30 lines]
>> digit is should show "NA".
>> if you can give me a solution for that I will be very thankful.

Reply to this Message

Rate this thread: