How to push Excel to run the Worksheet code

[matoust]

I am running Excel 97 under NTs. I have a Private Sub Worksheet_Calculate() sub in the sheet object calling another sub which is in a module under Modules directory.
It all works fine if I call it from Debugger, step into and go step by step, but I need to run that subrutine from "Modules" anytime the value of the (4, 2) cell is changed.
Could someone help me. Please.

Here is the subrutine from Worksheet code:
Private Sub Worksheet_Calculate()
If (Worksheets("definitions").Cells(5, 2).Value <> Worksheets("definitions").Cells(4, 2).Value) Then
Worksheets("definitions").Cells(5, 2).Value = Worksheets("definitions").Cells(4, 2).Value
Call set_my_hr
End If
End Sub

And here is the subrutine from Modules:

Sub set_my_hr()
Dim hrval As Integer
Dim hrno
hrno = "3"
DDEPoke channel, "HREG " + hrno, "definitions!r4c2"
End Sub

 

[Mark O'Brien]

If you are simply changing the cell value manually, i.e. changing the value by typing a value into the cell, you may not be triggering the "Calculate Event".

A quick example for you:

1. Create a new workbook
2. On the "sheet1" code module, insert this code:

<pre>
Private Sub Worksheet_Calculate()
MsgBox "hi"
End Sub</pre>

3. Go back to the Sheet1, put "3" in cell A1 type "3" and put "4" in cell A2.
4. You've changed both of the values, but the Calculate event has not been triggered which is what I suspect that you are stuck with.
5. Type this formula in cell A3:
=A1+A2
6. You should see a message box saying "hi"
7. Change the value in A2 to be "5"
8. You should see the messagebox appear again. (if you don't, then press "F9" to force Excel to calculate)

If you don't see the message box in step 6 or, then go to "Tools|Options|Calculate" and make sure that the "Autocalculate" option is chosen.

Now a better suggestion would be to use the "Worksheet_Change" event instead of the "Calculate event. The "Change event will trigger whenever a cell value changes.


HTH