timestamp

[tommyboy]

I have this code to enter a timestamp in say a2 when a1 is changed

As i am importing data, the worksheet_change doesnt work, i need to us
worksheet calculate but am unable to code it,

any help would be aprieciated

Private Sub Worksheet_Change(ByVal Target As Excel.Range)
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
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 Su

 

[Jim Rech]

As i am importing data

Can you describe exactly what you mean by this?


--
Jim Rech
Excel MVP
|I have this code to enter a timestamp in say a2 when a1 is changed
|
| As i am importing data, the worksheet_change doesnt work, i need to use
| worksheet calculate but am unable to code it,
|
| any help would be aprieciated
|
| Private Sub Worksheet_Change(ByVal Target As Excel.Range)
| 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
| 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
|
|
| ---
| Message posted
|

 

[tommyboy]

data is being imported from a text file, but as the worksheet_chang
doesnt work when importing data or when the cell is the result of
formula i cant use this cod

 

[Jim Rech]

Do you mean that you are doing a "File, Open"? If so the data comes into
Excel in a _new_ workbook. The worksheet_change event handler (or any event
handler) for another workbook or worksheet will not be invoked.

After you do the File, Open try copy/pasting the data into the worksheet
that has the worksheet_change event code.

--
Jim Rech
Excel MVP
| data is being imported from a text file, but as the worksheet_change
| doesnt work when importing data or when the cell is the result of a
| formula i cant use this code
|
|
| ---
| Message posted
|

 

[tommyboy]

I am using the get external data in excel so a cell will update
automatically into a new cell every time data is refreshed,

but as this is imported and not entered by user edit the
worksheet_change doesnt work,

 

[Jim Rech]

Have you tried the After Refresh event?:

http://support.microsoft.com/default.aspx?scid=kb;en-us;213187&Product=xlw

--
Jim Rech
Excel MVP
|I am using the get external data in excel so a cell will update
| automatically into a new cell every time data is refreshed,
|
| but as this is imported and not entered by user edit the
| worksheet_change doesnt work,
|
|
| ---
| Message posted
|

 

[tommyboy]

i basically want a worksheet_calculate to do thi