How to push Excel to run the Worksheet code

matoust

New Member
Joined
Sep 26, 2002
Messages
2
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
 

Excel Facts

How to total the visible cells?
From the first blank cell below a filtered data set, press Alt+=. Instead of SUM, you will get SUBTOTAL(9,)

Mark O'Brien

MrExcel MVP
Joined
Feb 15, 2002
Messages
3,530
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
 

Forum statistics

Threads
1,144,058
Messages
5,722,269
Members
422,419
Latest member
Havok390

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top