activecell.value - change from sub

[Zeus]

Hello

Please can someone tell me why the following sub works when run from vb (using F8 or F5), but not when called from an excel formulae in a worksheet i.e. =IF(C8=D8,setvalue(),)

thanks

Zeus

Public Sub SetValue()
Worksheets("HitCounter").Activate
Worksheets("HitCounter").Range("F8").Activate
ActiveCell.Value = 88
End Sub

[mdmackillop]

Simply, you cannot trigger code in this fashion to change other than the active cell.


You could add a formula in the target cell
=IF(Sheet1!C8=Sheet1!D8,88,"")

You can add an event code to call another macro

[vba]
Private Sub Worksheet_Change(ByVal Target As Range)
Dim tgt As Range
Set tgt = Range("C88")
If Not Intersect(Target, tgt) Is Nothing Then
If tgt(1) = tgt(2) Then SetValue
End If
End Sub

Public Sub SetValue()
Worksheets("HitCounter").Activate
Worksheets("HitCounter").Range("F8").Activate
ActiveCell.Value = 88
End Sub

[/vba]

Or you can use the Event code directly


[vba]
Private Sub Worksheet_Change(ByVal Target As Range)
Dim tgt As Range
Set tgt = Range("C88")
If Not Intersect(Target, tgt) Is Nothing Then
If tgt(1) = tgt(2) Then Sheets("HitCounter").Range("F8") = 88
End If
End Sub
[/vba]

Note that the Event code goes in the WorkSheet module of the monitored cells.

[Zeus]

Tks for the help.

The code below works if i manually input a value into cell C7, however the value will actually come from a DDE feed. If i simulate the feed using a separate workbook and link the cells the code no longer works.

Is there a way around this? Or is there a more efficient procedure in vb for counting the number of times one cell=another?

rgds

Zeus

[VBA]
Private Sub Worksheet_Change(ByVal Target As Range)
Dim tgt As Range
Set tgt = Range("C77")
If Not Intersect(Target, tgt) Is Nothing Then
If tgt(1) = tgt(2) Then
'Sheets("HitCounter").Range("F7") = 88
Counter
End If
End If
End Sub

Sub Counter()
Dim Temp As Integer
Worksheets("HitCounter").Activate
Temp = Range("H7").Value + 1 'get the previous count and increase by 1
Worksheets("HitCounter").Range("H7").Activate
ActiveCell.Value = Temp 'post new count value
End Sub
[/VBA]

[mdmackillop]

Can you zip and post your test workbooks? Possibly a Public Variable would be a better way to store your counter.