Please help

[tommyboy]

please please someone help

i have tried posting on forums, calling 0906 numbers and spending £3
on a call to suposed excel support to answer this question, but stil
have got nowhere

I am willing to pay anyone who sorts this out for me, or just tell m
where to go to get the answer.


pleases someone


----------

i have this function in a work book and it works fine

Private Sub Worksheet_Change(ByVal Target As Range)
With Sheets("sheet17")
x = .Cells(Rows.Count, "a").End(xlUp).Row + 1
If Target.Address = "$B$4" Then .Cells(x, "a") = Target
End With
End Sub


It basically update sheet17 column a with any changed data on b
sheet1

The problem is when using the worksheet_change event, it will no
update sheet17 when the cell is changed by a calculation

I know i need to use worksheet_calculate, but canot figer out the cod
for this

"Private Sub Worksheet_calculate(ByVal Target As Range)
With Sheets("sheet17")
x = .Cells(Rows.Count, "a").End(xlUp).Row + 1
If Target.Address = "$B$4" Then .Cells(x, "a") = Target
End With
End Sub"

This gives me errors,
or

Private Sub Workbook_SheetCalculate(ByVal Sh As Object)
With Sheets("sheet17")
x = .Cells(Rows.Count, "a").End(xlUp).Row + 1
If ActiveCell.Address = "$B$4" Then .Cells(x, "a") = ActiveCell.Value
End With
End Sub

just doesnt work






:lease help:

 

[BrianB]

I think this will do what you want. It goes into the ThisWorkBook cod
module and runs each time a worksheet calculates. Change "Sheet1" t
your sheet name.


Code
-------------------
'---------------------------------------------------
Private Sub Workbook_SheetCalculate(ByVal Sh As Object)
If Sh.Name <> "Sheet17" Then
With Sheets("sheet17")
x = .Cells(Rows.Count, "a").End(xlUp).Row + 1
.Cells(x, "a").Value = _
Worksheets("Sheet1").Range("B4").Value
End With
End If
End Sub
'--------------------------------------------------

 

[Harlan Grove]

I am willing to pay anyone who sorts this out for me, or just tell me
where to go to get the answer.

....

Begging & pleading is unbecoming. Please desist.

i have this function in a work book and it works fine

Private Sub Worksheet_Change(ByVal Target As Range)
With Sheets("sheet17")
x = .Cells(Rows.Count, "a").End(xlUp).Row + 1
If Target.Address = "$B$4" Then .Cells(x, "a") = Target
End With
End Sub

This is equivalent to


Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Address <> "$B$4" Then Exit Sub

Application.EnableEvents = False
On Error GoTo CleanUp

With Sheets("sheet17")
.Cells(Rows.Count, "A").End(xlUp).Offset(1, 0).Value = Target.Value
End With

CleanUp:
Application.EnableEvents = True

End Sub

It basically update sheet17 column a with any changed data on b4
sheet1

The problem is when using the worksheet_change event, it will not
update sheet17 when the cell is changed by a calculation

....

Your Calculate handler *can't* have any arguments. That's one reason you're
getting errors. Also, if the value of the formula in Sheet1!B4 doesn't
change, should values be entered in Sheet17!A:A?

Anyway, try the following comprehensive set-up for sheet1.


Private prev As Variant


Private Sub Worksheet_Calculate()
Static init As Boolean
Dim v As Variant

Application.EnableEvents = False
On Error GoTo CleanUp

v = Me.Range("B4").Value

If init And v <> prev Then
Sheets("sheet17").Cells(Rows.Count, "A"). _
End(xlUp).Offset(1, 0).Value = v

prev = v

ElseIf Not init Then
init = True
prev = Range("B4").Value

End If

CleanUp:
Application.EnableEvents = True
End Sub


Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Address <> "$B$4" Then Exit Sub

Application.EnableEvents = False
On Error GoTo CleanUp

prev = Target.Value

Sheets("sheet17").Cells(Rows.Count, "A"). _
End(xlUp).Offset(1, 0).Value = prev

CleanUp:
Application.EnableEvents = True
End Sub

 

[Harlan Grove]

I think this will do what you want. It goes into the ThisWorkBook code
module and runs each time a worksheet calculates. Change "Sheet1" to
your sheet name. ...
Private Sub Workbook_SheetCalculate(ByVal Sh As Object)
If Sh.Name <> "Sheet17" Then
With Sheets("sheet17")
x = .Cells(Rows.Count, "a").End(xlUp).Row + 1
.Cells(x, "a").Value = _
Worksheets("Sheet1").Range("B4").Value
End With
End If
End Sub

This potentially gives rise to an infinite loop if any worksheet other than
sheet17 contains *either* references to cells in sheet17!A:A *or* any volatile
functions (such as TODAY() or NOW()). In such situations, if calculation
triggers this event handler, then this event handler enters the current value of
sheet1!B4 into sheet17!A:A, triggering another recalc, firing this event handler
again, which makes another entry in sheet17!A:A, triggering another recalc, yada
yada yada. The good news is that at some point these implicitly nested calls
will blow through the call stack and effectively halt further calls. This is not
good.

You should *ALWAYS* bracket *ANY* cell entries in that *ANY* [Sheet]Change or
[Sheet]Calculate event handlers between

Application.EnableEvents = False

and

Application.EnableEvents = True

There is *NEVER* any excuse not to unless you *SPECIFICALLY* want infinite
(well, unpredictably terminating on system error) looping. And that should be,
um, basically never.

 

[tommyboy]

thanks that worked a treat

When updating colum A with new data is it possible to also update th
coresponding column B, with the curent data in F4 and update th
coresponding colum C with the current time.

I have looked at your code but cant see how to duplicate for othee
line

 

[Dave Peterson]

The original posts have aged off (for me anyway). You may want to include
enough information if no one recognizes your question.