Worksheet_Change Event

  • Thread starter Thread starter Alectrical
  • Start date Start date
A

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
 
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
 
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"
 
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
 
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.
 
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.
 
Back
Top