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

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

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

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

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

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