Why wont this work!

  • Thread starter Thread starter Bob
  • Start date Start date
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
..
..
..
..
 
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
 
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)
 
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
 
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 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...
 
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...
 
The code was modified to show how to change the bold to false if you couldn't do
the paste special|values.

<<snipped>>
 
Back
Top