Home |
Search |
Today's Posts |
#1
|
|||
|
|||
using now() as static function
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 |
#3
|
|||
|
|||
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 "JE McGimpsey" wrote: Take a look he http://www.mcgimpsey.com/excel/timestamp.html In article , "MINAL ZUNKE" <MINAL wrote: 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 |
#4
|
|||
|
|||
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 In article , "MINAL ZUNKE" wrote: 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. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
I NEED HELP with the SPELLNUMBER Function | Excel Worksheet Functions | |||
Excel option to store trendline's coefficients in cells for use | Charts and Charting in Excel | |||
Date & Time | New Users to Excel | |||
Automatically up date time in a cell | Excel Discussion (Misc queries) | |||
clock | Excel Worksheet Functions |