Home Menu

Menu


ExcelBanter » Excel Newsgroups » Excel Programming > Need to modify code
Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 30
Default Need to modify code
I am using the following code to track when i make a change to a cell value.
It works great but i need to make a modification i dont know how to do. If i
type a value in any cell in the range of D9:D374, the date gets populated.
If i copy and paste a value in to that range, the date is not populated. I
need it to be able to work with the copy/paste. Any help would be greatly
appreciated.

Private Sub Worksheet_Change(ByVal Target As Excel.Range)
With Target
If .Count 1 Then Exit Sub
If Not Intersect(Range("D9:D374"), .Cells) Is Nothing Then
Application.EnableEvents = False
With .Offset(0, 9)
'.NumberFormat = "dd mmm yyyy hh:mm:ss"
.Value = Date
End With
End If
Application.EnableEvents = True
End With
End Sub

Reply With QuoteReply With Quote
  #2   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 449
Default Need to modify code
Hi

When pasting then target may be multiple cells. Your code exits if
target.count is 1.

Try something like

Private Sub Worksheet_Change(ByVal Target As Excel.Range)
Dim Cel as Range
For each Cel in Target
If Not Intersect(Range("D9:D374"), .Cel) Is Nothing Then
Application.EnableEvents = False
With Cel.Offset(0, 9)
'.NumberFormat = "dd mmm yyyy hh:mm:ss"
.Value = Date
End With
End If
Next

HTH. Best wishes Harald


"Greg H." wrote in message
...
I am using the following code to track when i make a change to a cell
value.
It works great but i need to make a modification i dont know how to do.
If i
type a value in any cell in the range of D9:D374, the date gets populated.
If i copy and paste a value in to that range, the date is not populated.
I
need it to be able to work with the copy/paste. Any help would be greatly
appreciated.

Private Sub Worksheet_Change(ByVal Target As Excel.Range)
With Target
If .Count 1 Then Exit Sub
If Not Intersect(Range("D9:D374"), .Cells) Is Nothing Then
Application.EnableEvents = False
With .Offset(0, 9)
'.NumberFormat = "dd mmm yyyy hh:mm:ss"
.Value = Date
End With
End If
Application.EnableEvents = True
End With
End Sub


Reply With QuoteReply With Quote
  #3   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 30
Default Need to modify code
When i try this i get the error Compile Error: Invalid or unqualified
reference.

Any ideas?

"Harald Staff" wrote:

Hi

When pasting then target may be multiple cells. Your code exits if
target.count is 1.

Try something like

Private Sub Worksheet_Change(ByVal Target As Excel.Range)
Dim Cel as Range
For each Cel in Target
If Not Intersect(Range("D9:D374"), .Cel) Is Nothing Then
Application.EnableEvents = False
With Cel.Offset(0, 9)
'.NumberFormat = "dd mmm yyyy hh:mm:ss"
.Value = Date
End With
End If
Next

HTH. Best wishes Harald


"Greg H." wrote in message
...
I am using the following code to track when i make a change to a cell
value.
It works great but i need to make a modification i dont know how to do.
If i
type a value in any cell in the range of D9:D374, the date gets populated.
If i copy and paste a value in to that range, the date is not populated.
I
need it to be able to work with the copy/paste. Any help would be greatly
appreciated.

Private Sub Worksheet_Change(ByVal Target As Excel.Range)
With Target
If .Count 1 Then Exit Sub
If Not Intersect(Range("D9:D374"), .Cells) Is Nothing Then
Application.EnableEvents = False
With .Offset(0, 9)
'.NumberFormat = "dd mmm yyyy hh:mm:ss"
.Value = Date
End With
End If
Application.EnableEvents = True
End With
End Sub



Reply With QuoteReply With Quote
  #4   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 5,939
Default Need to modify code
This line has an extra dot
If Not Intersect(Range("D9:D374"), .Cel) Is Nothing Then
change it to
If Not Intersect(Range("D9:D374"), Cel) Is Nothing Then
--
HTH...

Jim Thomlinson


"Greg H." wrote:

When i try this i get the error Compile Error: Invalid or unqualified
reference.

Any ideas?

"Harald Staff" wrote:

Hi

When pasting then target may be multiple cells. Your code exits if
target.count is 1.

Try something like

Private Sub Worksheet_Change(ByVal Target As Excel.Range)
Dim Cel as Range
For each Cel in Target
If Not Intersect(Range("D9:D374"), .Cel) Is Nothing Then
Application.EnableEvents = False
With Cel.Offset(0, 9)
'.NumberFormat = "dd mmm yyyy hh:mm:ss"
.Value = Date
End With
End If
Next

HTH. Best wishes Harald


"Greg H." wrote in message
...
I am using the following code to track when i make a change to a cell
value.
It works great but i need to make a modification i dont know how to do.
If i
type a value in any cell in the range of D9:D374, the date gets populated.
If i copy and paste a value in to that range, the date is not populated.
I
need it to be able to work with the copy/paste. Any help would be greatly
appreciated.

Private Sub Worksheet_Change(ByVal Target As Excel.Range)
With Target
If .Count 1 Then Exit Sub
If Not Intersect(Range("D9:D374"), .Cells) Is Nothing Then
Application.EnableEvents = False
With .Offset(0, 9)
'.NumberFormat = "dd mmm yyyy hh:mm:ss"
.Value = Date
End With
End If
Application.EnableEvents = True
End With
End Sub



Reply With QuoteReply With Quote
  #5   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 30
Default Need to modify code
Good catch ... now one more request, can this new code be combined to update
regardless if you copy/paste or manually type a value. Right now with the
new code it only updates if it is copy/paste.

Thanks for being patient with me.

"Jim Thomlinson" wrote:

This line has an extra dot
If Not Intersect(Range("D9:D374"), .Cel) Is Nothing Then
change it to
If Not Intersect(Range("D9:D374"), Cel) Is Nothing Then
--
HTH...

Jim Thomlinson


"Greg H." wrote:

When i try this i get the error Compile Error: Invalid or unqualified
reference.

Any ideas?

"Harald Staff" wrote:

Hi

When pasting then target may be multiple cells. Your code exits if
target.count is 1.

Try something like

Private Sub Worksheet_Change(ByVal Target As Excel.Range)
Dim Cel as Range
For each Cel in Target
If Not Intersect(Range("D9:D374"), .Cel) Is Nothing Then
Application.EnableEvents = False
With Cel.Offset(0, 9)
'.NumberFormat = "dd mmm yyyy hh:mm:ss"
.Value = Date
End With
End If
Next

HTH. Best wishes Harald


"Greg H." wrote in message
...
I am using the following code to track when i make a change to a cell
value.
It works great but i need to make a modification i dont know how to do.
If i
type a value in any cell in the range of D9:D374, the date gets populated.
If i copy and paste a value in to that range, the date is not populated.
I
need it to be able to work with the copy/paste. Any help would be greatly
appreciated.

Private Sub Worksheet_Change(ByVal Target As Excel.Range)
With Target
If .Count 1 Then Exit Sub
If Not Intersect(Range("D9:D374"), .Cells) Is Nothing Then
Application.EnableEvents = False
With .Offset(0, 9)
'.NumberFormat = "dd mmm yyyy hh:mm:ss"
.Value = Date
End With
End If
Application.EnableEvents = True
End With
End Sub



Reply With QuoteReply With Quote
  #6   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 30
Default Need to modify code
Any help on the below question?

"Greg H." wrote:

Good catch ... now one more request, can this new code be combined to update
regardless if you copy/paste or manually type a value. Right now with the
new code it only updates if it is copy/paste.

Thanks for being patient with me.

"Jim Thomlinson" wrote:

This line has an extra dot
If Not Intersect(Range("D9:D374"), .Cel) Is Nothing Then
change it to
If Not Intersect(Range("D9:D374"), Cel) Is Nothing Then
--
HTH...

Jim Thomlinson


"Greg H." wrote:

When i try this i get the error Compile Error: Invalid or unqualified
reference.

Any ideas?

"Harald Staff" wrote:

Hi

When pasting then target may be multiple cells. Your code exits if
target.count is 1.

Try something like

Private Sub Worksheet_Change(ByVal Target As Excel.Range)
Dim Cel as Range
For each Cel in Target
If Not Intersect(Range("D9:D374"), .Cel) Is Nothing Then
Application.EnableEvents = False
With Cel.Offset(0, 9)
'.NumberFormat = "dd mmm yyyy hh:mm:ss"
.Value = Date
End With
End If
Next

HTH. Best wishes Harald


"Greg H." wrote in message
...
I am using the following code to track when i make a change to a cell
value.
It works great but i need to make a modification i dont know how to do.
If i
type a value in any cell in the range of D9:D374, the date gets populated.
If i copy and paste a value in to that range, the date is not populated.
I
need it to be able to work with the copy/paste. Any help would be greatly
appreciated.

Private Sub Worksheet_Change(ByVal Target As Excel.Range)
With Target
If .Count 1 Then Exit Sub
If Not Intersect(Range("D9:D374"), .Cells) Is Nothing Then
Application.EnableEvents = False
With .Offset(0, 9)
'.NumberFormat = "dd mmm yyyy hh:mm:ss"
.Value = Date
End With
End If
Application.EnableEvents = True
End With
End Sub



Reply With QuoteReply With Quote
  #7   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 35,218
Default Need to modify code
What part are you having trouble with?

Post your current code. It may help.

Greg H. wrote:

Any help on the below question?

"Greg H." wrote:

Good catch ... now one more request, can this new code be combined to update
regardless if you copy/paste or manually type a value. Right now with the
new code it only updates if it is copy/paste.

Thanks for being patient with me.

"Jim Thomlinson" wrote:

This line has an extra dot
If Not Intersect(Range("D9:D374"), .Cel) Is Nothing Then
change it to
If Not Intersect(Range("D9:D374"), Cel) Is Nothing Then
--
HTH...

Jim Thomlinson


"Greg H." wrote:

When i try this i get the error Compile Error: Invalid or unqualified
reference.

Any ideas?

"Harald Staff" wrote:

Hi

When pasting then target may be multiple cells. Your code exits if
target.count is 1.

Try something like

Private Sub Worksheet_Change(ByVal Target As Excel.Range)
Dim Cel as Range
For each Cel in Target
If Not Intersect(Range("D9:D374"), .Cel) Is Nothing Then
Application.EnableEvents = False
With Cel.Offset(0, 9)
'.NumberFormat = "dd mmm yyyy hh:mm:ss"
.Value = Date
End With
End If
Next

HTH. Best wishes Harald


"Greg H." wrote in message
...
I am using the following code to track when i make a change to a cell
value.
It works great but i need to make a modification i dont know how to do.
If i
type a value in any cell in the range of D9:D374, the date gets populated.
If i copy and paste a value in to that range, the date is not populated.
I
need it to be able to work with the copy/paste. Any help would be greatly
appreciated.

Private Sub Worksheet_Change(ByVal Target As Excel.Range)
With Target
If .Count 1 Then Exit Sub
If Not Intersect(Range("D9:D374"), .Cells) Is Nothing Then
Application.EnableEvents = False
With .Offset(0, 9)
'.NumberFormat = "dd mmm yyyy hh:mm:ss"
.Value = Date
End With
End If
Application.EnableEvents = True
End With
End Sub




--

Dave Peterson
Reply With QuoteReply With Quote
  #8   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 30
Default Need to modify code
Code 1 updates when you copy vales from one cell to another but i want to be
able to combine that with Code 2 so that if you copy or enter manually it
gets updated. I can not get them to work together.

CODE 1
Private Sub Worksheet_Change(ByVal Target As Excel.Range)
Dim Cel as Range
For each Cel in Target
If Not Intersect(Range("D9:D374"), Cel) Is Nothing Then
Application.EnableEvents = False
With Cel.Offset(0, 9)
'.NumberFormat = "dd mmm yyyy hh:mm:ss"
.Value = Date
End With
End If
Next
End Sub

CODE 2
Private Sub Worksheet_Change(ByVal Target As Excel.Range)
With Target
If .Count 1 Then Exit Sub
If Not Intersect(Range("D9:D374"), .Cells) Is Nothing Then
Application.EnableEvents = False
With .Offset(0, 9)
'.NumberFormat = "dd mmm yyyy hh:mm:ss"
.Value = Date
End With
End If
Application.EnableEvents = True
End With
End Sub


"Dave Peterson" wrote:

What part are you having trouble with?

Post your current code. It may help.

Greg H. wrote:

Any help on the below question?

"Greg H." wrote:

Good catch ... now one more request, can this new code be combined to update
regardless if you copy/paste or manually type a value. Right now with the
new code it only updates if it is copy/paste.

Thanks for being patient with me.

"Jim Thomlinson" wrote:

This line has an extra dot
If Not Intersect(Range("D9:D374"), .Cel) Is Nothing Then
change it to
If Not Intersect(Range("D9:D374"), Cel) Is Nothing Then
--
HTH...

Jim Thomlinson


"Greg H." wrote:

When i try this i get the error Compile Error: Invalid or unqualified
reference.

Any ideas?

"Harald Staff" wrote:

Hi

When pasting then target may be multiple cells. Your code exits if
target.count is 1.

Try something like

Private Sub Worksheet_Change(ByVal Target As Excel.Range)
Dim Cel as Range
For each Cel in Target
If Not Intersect(Range("D9:D374"), .Cel) Is Nothing Then
Application.EnableEvents = False
With Cel.Offset(0, 9)
'.NumberFormat = "dd mmm yyyy hh:mm:ss"
.Value = Date
End With
End If
Next

HTH. Best wishes Harald


"Greg H." wrote in message
...
I am using the following code to track when i make a change to a cell
value.
It works great but i need to make a modification i dont know how to do.
If i
type a value in any cell in the range of D9:D374, the date gets populated.
If i copy and paste a value in to that range, the date is not populated.
I
need it to be able to work with the copy/paste. Any help would be greatly
appreciated.

Private Sub Worksheet_Change(ByVal Target As Excel.Range)
With Target
If .Count 1 Then Exit Sub
If Not Intersect(Range("D9:D374"), .Cells) Is Nothing Then
Application.EnableEvents = False
With .Offset(0, 9)
'.NumberFormat = "dd mmm yyyy hh:mm:ss"
.Value = Date
End With
End If
Application.EnableEvents = True
End With
End Sub




--

Dave Peterson

Reply With QuoteReply With Quote
  #9   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 35,218
Default Need to modify code
First, you only get one worksheet_Change event per worksheet.


Option Explicit
Private Sub Worksheet_Change(ByVal Target As Excel.Range)

Dim Cel As Range
Dim RngToInspect As Range
Dim myIntersect As Range

Set RngToInspect = Me.Range("d9:d374")

Set myIntersect = Intersect(Target, RngToInspect)

If myIntersect Is Nothing Then
Exit Sub
End If

Application.EnableEvents = False
For Each Cel In myIntersect.Cells
With Cel.Offset(0, 9)
'.NumberFormat = "dd mmm yyyy hh:mm:ss"
.Value = Date
End With
Next Cel
Application.EnableEvents = True
End Sub

Greg H. wrote:

Code 1 updates when you copy vales from one cell to another but i want to be
able to combine that with Code 2 so that if you copy or enter manually it
gets updated. I can not get them to work together.

CODE 1
Private Sub Worksheet_Change(ByVal Target As Excel.Range)
Dim Cel as Range
For each Cel in Target
If Not Intersect(Range("D9:D374"), Cel) Is Nothing Then
Application.EnableEvents = False
With Cel.Offset(0, 9)
'.NumberFormat = "dd mmm yyyy hh:mm:ss"
.Value = Date
End With
End If
Next
End Sub

CODE 2
Private Sub Worksheet_Change(ByVal Target As Excel.Range)
With Target
If .Count 1 Then Exit Sub
If Not Intersect(Range("D9:D374"), .Cells) Is Nothing Then
Application.EnableEvents = False
With .Offset(0, 9)
'.NumberFormat = "dd mmm yyyy hh:mm:ss"
.Value = Date
End With
End If
Application.EnableEvents = True
End With
End Sub

"Dave Peterson" wrote:

What part are you having trouble with?

Post your current code. It may help.

Greg H. wrote:

Any help on the below question?

"Greg H." wrote:

Good catch ... now one more request, can this new code be combined to update
regardless if you copy/paste or manually type a value. Right now with the
new code it only updates if it is copy/paste.

Thanks for being patient with me.

"Jim Thomlinson" wrote:

This line has an extra dot
If Not Intersect(Range("D9:D374"), .Cel) Is Nothing Then
change it to
If Not Intersect(Range("D9:D374"), Cel) Is Nothing Then
--
HTH...

Jim Thomlinson


"Greg H." wrote:

When i try this i get the error Compile Error: Invalid or unqualified
reference.

Any ideas?

"Harald Staff" wrote:

Hi

When pasting then target may be multiple cells. Your code exits if
target.count is 1.

Try something like

Private Sub Worksheet_Change(ByVal Target As Excel.Range)
Dim Cel as Range
For each Cel in Target
If Not Intersect(Range("D9:D374"), .Cel) Is Nothing Then
Application.EnableEvents = False
With Cel.Offset(0, 9)
'.NumberFormat = "dd mmm yyyy hh:mm:ss"
.Value = Date
End With
End If
Next

HTH. Best wishes Harald


"Greg H." wrote in message
...
I am using the following code to track when i make a change to a cell
value.
It works great but i need to make a modification i dont know how to do.
If i
type a value in any cell in the range of D9:D374, the date gets populated.
If i copy and paste a value in to that range, the date is not populated.
I
need it to be able to work with the copy/paste. Any help would be greatly
appreciated.

Private Sub Worksheet_Change(ByVal Target As Excel.Range)
With Target
If .Count 1 Then Exit Sub
If Not Intersect(Range("D9:D374"), .Cells) Is Nothing Then
Application.EnableEvents = False
With .Offset(0, 9)
'.NumberFormat = "dd mmm yyyy hh:mm:ss"
.Value = Date
End With
End If
Application.EnableEvents = True
End With
End Sub




--

Dave Peterson


--

Dave Peterson
Reply With QuoteReply With Quote
Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes
Linear Mode Linear Mode

Posting Rules
Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On

Similar Threads
Thread Thread Starter Forum Replies Last Post
help modify code Don Doan Excel Programming 4 February 15th 08 04:33 PM
Modify existing code to dynamic code Ixtreme Excel Programming 5 August 31st 07 11:42 AM
almost there ... code modify help Ray Excel Programming 1 March 29th 07 12:58 PM
Modify code in UDF Biff Excel Programming 9 August 31st 05 04:41 AM
Help me modify this VBA code please: sanmisds1 Excel Programming 4 July 16th 05 04:48 AM


All times are GMT +1. The time now is 07:47 AM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"

 

Contact Us ExcelBanter forum home Privacy Statement