Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
date format static
Hello I copied the following macro and I entred the macro n the worksheet
module this is what I'm looking for. If one of the following cells is not blank B59, B 60 B 61 B 63 but has a name that has been selected from a drop down box then in column c I want a static current date and time Macro does not work thanks for your help Private Sub Worksheet_Change(ByVal Target As Excel.Range) With Target If .Count 1 Then Exit Sub If Not Intersect(Range("B59:B63"), .Cells) Is Nothing Then Application.EnableEvents = False If IsEmpty(.Value) Then .Offset(0, 1).ClearContents Else With .Offset(0, 1) .NumberFormat = "dd mmm yyyy hh:mm:ss" .Value = Now End With End If Application.EnableEvents = True End If End With End Sub |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
date format static
When you say that the macro doesn't work, what do you mean? What is happening
when you run it? Lynn Trapp "Wanna Learn" wrote: Hello I copied the following macro and I entred the macro n the worksheet module this is what I'm looking for. If one of the following cells is not blank B59, B 60 B 61 B 63 but has a name that has been selected from a drop down box then in column c I want a static current date and time Macro does not work thanks for your help Private Sub Worksheet_Change(ByVal Target As Excel.Range) With Target If .Count 1 Then Exit Sub If Not Intersect(Range("B59:B63"), .Cells) Is Nothing Then Application.EnableEvents = False If IsEmpty(.Value) Then .Offset(0, 1).ClearContents Else With .Offset(0, 1) .NumberFormat = "dd mmm yyyy hh:mm:ss" .Value = Now End With End If Application.EnableEvents = True End If End With End Sub |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
date format static
Thanks Lynn
Example If I select a name from the drop down box in B59 C59 remains blank instead of giving me the date and time "Lynn Trapp" wrote: When you say that the macro doesn't work, what do you mean? What is happening when you run it? Lynn Trapp "Wanna Learn" wrote: Hello I copied the following macro and I entred the macro n the worksheet module this is what I'm looking for. If one of the following cells is not blank B59, B 60 B 61 B 63 but has a name that has been selected from a drop down box then in column c I want a static current date and time Macro does not work thanks for your help Private Sub Worksheet_Change(ByVal Target As Excel.Range) With Target If .Count 1 Then Exit Sub If Not Intersect(Range("B59:B63"), .Cells) Is Nothing Then Application.EnableEvents = False If IsEmpty(.Value) Then .Offset(0, 1).ClearContents Else With .Offset(0, 1) .NumberFormat = "dd mmm yyyy hh:mm:ss" .Value = Now End With End If Application.EnableEvents = True End If End With End Sub |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
date format static
It works OK for me. Did you put the code in the right place?
Select the sheet where you want this to happen Right click the sheet tab and select View Code Put the code in the window that opens -- Biff Microsoft Excel MVP "Wanna Learn" wrote in message ... Thanks Lynn Example If I select a name from the drop down box in B59 C59 remains blank instead of giving me the date and time "Lynn Trapp" wrote: When you say that the macro doesn't work, what do you mean? What is happening when you run it? Lynn Trapp "Wanna Learn" wrote: Hello I copied the following macro and I entred the macro n the worksheet module this is what I'm looking for. If one of the following cells is not blank B59, B 60 B 61 B 63 but has a name that has been selected from a drop down box then in column c I want a static current date and time Macro does not work thanks for your help Private Sub Worksheet_Change(ByVal Target As Excel.Range) With Target If .Count 1 Then Exit Sub If Not Intersect(Range("B59:B63"), .Cells) Is Nothing Then Application.EnableEvents = False If IsEmpty(.Value) Then .Offset(0, 1).ClearContents Else With .Offset(0, 1) .NumberFormat = "dd mmm yyyy hh:mm:ss" .Value = Now End With End If Application.EnableEvents = True End If End With End Sub |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
date format static
--Quit the application and restart Excel OR from the immediate window paste
the below code and enter which will enable the events if it has been disabled earlier Application.EnableEvents = True --Set the Security level to low/medium in (Tools|Macro|Security). Select the work sheet tab (where you would like to have this implemented). Right click View Code and in the code pane paste the code...Now get back to workbook and try selecting data in *** the *** sheet B59:B63 If this post helps click Yes --------------- Jacob Skaria "Wanna Learn" wrote: Hello I copied the following macro and I entred the macro n the worksheet module this is what I'm looking for. If one of the following cells is not blank B59, B 60 B 61 B 63 but has a name that has been selected from a drop down box then in column c I want a static current date and time Macro does not work thanks for your help Private Sub Worksheet_Change(ByVal Target As Excel.Range) With Target If .Count 1 Then Exit Sub If Not Intersect(Range("B59:B63"), .Cells) Is Nothing Then Application.EnableEvents = False If IsEmpty(.Value) Then .Offset(0, 1).ClearContents Else With .Offset(0, 1) .NumberFormat = "dd mmm yyyy hh:mm:ss" .Value = Now End With End If Application.EnableEvents = True End If End With End Sub |
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
date format static
Are you using xl97 and data|Validation?
Debra Dalgleish shares this: http://contextures.com/xlDataVal08.html#Change (The easiest fix may be typing the names into the data|validation dialog.) Wanna Learn wrote: Thanks Lynn Example If I select a name from the drop down box in B59 C59 remains blank instead of giving me the date and time "Lynn Trapp" wrote: When you say that the macro doesn't work, what do you mean? What is happening when you run it? Lynn Trapp "Wanna Learn" wrote: Hello I copied the following macro and I entred the macro n the worksheet module this is what I'm looking for. If one of the following cells is not blank B59, B 60 B 61 B 63 but has a name that has been selected from a drop down box then in column c I want a static current date and time Macro does not work thanks for your help Private Sub Worksheet_Change(ByVal Target As Excel.Range) With Target If .Count 1 Then Exit Sub If Not Intersect(Range("B59:B63"), .Cells) Is Nothing Then Application.EnableEvents = False If IsEmpty(.Value) Then .Offset(0, 1).ClearContents Else With .Offset(0, 1) .NumberFormat = "dd mmm yyyy hh:mm:ss" .Value = Now End With End If Application.EnableEvents = True End If End With End Sub -- Dave Peterson |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
How do I set a static date? | Excel Worksheet Functions | |||
Static date | Excel Discussion (Misc queries) | |||
static date | Excel Worksheet Functions | |||
Static Date | Excel Discussion (Misc queries) | |||
DATE STATIC | Excel Worksheet Functions |