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
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