Worksheet_Change Event

[Alectrical]

Hi
I have a link in A1 in sheet 1, pasted from A1 in sheet 2.
I need to record the data in column B, where when A1 value changes the new
value is then put in a new row in column B. The following code works only
when I Double click A1, then click on any other cell. It does not
automatically update column B as I would expect. I have sheet calculation set
to automatic and pressing F9 has no effect. Any ideas, I'm desperate to get
this working, I know it works because I had it working a few years ago, but I
cannot find the code.
Thanks in Advance
Alec

Private Sub Worksheet_Change(ByVal Target As Range)
Dim X As Long, LR As Long
If Target.Address = "$A$1" Then
LR = Cells(Rows.Count, "B").End(xlUp).Row
If Not (LR = 1 And Len(Cells(LR, "B").Value) = 0) Then LR = LR + 1
Application.EnableEvents = False
Cells(LR, "B").Value = Range("A1").Value
Application.EnableEvents = True
End If
End Sub

 

[Patrick Molloy]

this continues from an earlier thread doesn't it?

anyway, set another cell as =1*A1
that means it will calculate when A1's value changes.
Instead of using the CHANGE event, use the CALCULATE event:

Private Sub Worksheet_Calculate()
' your code here
End Sub

 

[Alectrical]

Thanks for your reply Patrick.
I've tried the Calculate event but get the error message - Variable not
Defined with Target highlighted. Also tried changing Private Sub
Worksheet_Calculate() to Private Sub Worksheet_Calculate(ByVal Target As
Range) but get the error message "procedure declaration does not match
description of event or procedure having the same name"

 

[Don Guillett]

This simple macro will take ANY entry, when entered, in col A and copy to
the next available row in col B

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Row < 2 Or Target.Column <> 1 Then Exit Sub
Cells(Rows.Count, 2).End(xlUp).Offset(1) = Target
End Sub

 

[Alectrical]

Thanks for your reply Don
Tried that code and nothing is happening, it appears the Worksheet_Change
Event is not working as it should, All I have done is selected the tab at the
bottom of the sheet, and selected view code and pasted. Do I have to declare
the event anywhere else.

 

[Don Guillett]

Maybe you inadvertently caused events to be false. Run this from anywhere
sub fixit()
application.enableevents=true
end sub

The worksheet_change macro depends on you entering something in a cell in
col A below row 1
If desired, send your file to my address below along with this msg and
a clear explanation of what you want and before/after examples.