Why wont this work!

B

Bob

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Column = 2 And Target <> "" Then Target.Offset(, -1) = Date
End Sub

This script works fine but when I have a macro to paste something into B
column it wont bring up the date in A column, but if I manually enter in to
B it works..Confused!

--
Thanks in advance for your help....Bob Vance
..
..
..
..
 
S

SOS

Bob,

I tried your code having attached it to Worksheet 1 and inserted
module with the following code:

Sub Test()
Range("B1").Value = "Whatever"
End Sub

and it put the date into cell A1 just fine

Seamu
 
B

Bob Phillips

Bob,

It works fine for me (XL2000, XP). This suggests there is something else
going on as well.

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)
 
D

Dave Peterson

If I changed one cell or pasted into one cell, it worked ok for me:

But if you're pasting more than one cell:

Option Explicit
Private Sub Worksheet_Change(ByVal Target As Range)

Dim myCell As Range
Dim myRng As Range

Set myRng = Nothing
On Error Resume Next
Set myRng = Intersect(Target, Me.Range("b:b"))
On Error GoTo 0

If myRng Is Nothing Then Exit Sub

Application.EnableEvents = False
On Error Resume Next

For Each myCell In myRng.Cells
With myCell
If .Value <> "" Then
With .Offset(0, -1)
.Value = Date
.NumberFormat = "mm/dd/yyyy"
End With
End If
End With
Next myCell

On Error GoTo 0
Application.EnableEvents = True

End Sub
 
B

Bob

Yes Dave I was pasting into B and C so I will try your code thanks

--
Thanks in advance for your help....Bob Vance
..
..
..
..
 
B

Bob

Dave can it be changed to only paste the values as it is copying bold text
and I have to change it back to normal text on the other sheet,

--
Thanks in advance for your help....Bob Vance
..
..
..
..

Bob said:
Yes Dave I was pasting into B and C so I will try your code thanks

--
Thanks in advance for your help....Bob Vance
.
.
.
.

Dave Peterson said:
If I changed one cell or pasted into one cell, it worked ok for me:

But if you're pasting more than one cell:

Option Explicit
Private Sub Worksheet_Change(ByVal Target As Range)

Dim myCell As Range
Dim myRng As Range

Set myRng = Nothing
On Error Resume Next
Set myRng = Intersect(Target, Me.Range("b:b"))
On Error GoTo 0

If myRng Is Nothing Then Exit Sub

Application.EnableEvents = False
On Error Resume Next

For Each myCell In myRng.Cells
With myCell
If .Value <> "" Then
With .Offset(0, -1)
.Value = Date
.NumberFormat = "mm/dd/yyyy"
End With
End If
End With
Next myCell

On Error GoTo 0
Application.EnableEvents = True

End Sub
Click to expand...
in
Click to expand...
 
D

Dave Peterson

That routine doesn't actually do the pasting--it just reacts to your pasting.

Instead of just pasting, maybe you should do Paste Special|values.

Option Explicit
Private Sub Worksheet_Change(ByVal Target As Range)

Dim myCell As Range
Dim myRng As Range

Set myRng = Nothing
On Error Resume Next
Set myRng = Intersect(Target, Me.Range("b:b"))
On Error GoTo 0

If myRng Is Nothing Then Exit Sub

Application.EnableEvents = False
On Error Resume Next

myRng.Font.Bold = False 'Added formatting here

For Each myCell In myRng.Cells
With myCell
If .Value <> "" Then
With .Offset(0, -1)
.Value = Date
.NumberFormat = "mm/dd/yyyy"
End With
End If
End With
Next myCell

On Error GoTo 0
Application.EnableEvents = True

End Sub

If you wanted the whole range that you pasted changed to non-bold, you could
change that
myrng.font.bold = false
to
target.font.bold = false

(But if you leave it in the same spot in the code, it'll only do it if there's
at least one cell in B that's being pasted.)

If you want to unBold(?) any range--no matter where, just move:
target.font.bold = false
above this line: "If myRng Is Nothing Then Exit Sub"




Dave can it be changed to only paste the values as it is copying bold text
and I have to change it back to normal text on the other sheet,

--
Thanks in advance for your help....Bob Vance
.
.
.
.
Click to expand...
 
D

Dave Peterson

The code was modified to show how to change the bold to false if you couldn't do
the paste special|values.

<<snipped>>
 
Top