Forcing a sheet to calculate when automatic calculation is turned off for the workbook

Posted by Nate Hamilton on February 08, 2002 12:00 PM

Does anyone know how to force a worksheet or even just a column where formulas are located to recalculate in VBA? I can't get the sheet to recalculate for the life of me. Any help would be greatly appreciated. Thanks

Posted by DK on February 08, 2002 12:38 PM

You can use the Calculate method of the worksheet or a range e.g.

Sub CalcSheet()
Sheets("sheet1").Calculate
End Sub

Sub CalcRange()
Range("A1:A10").Calculate
End Sub

HTH,
D

Posted by Nate Hamilton on February 08, 2002 12:50 PM

DK,

I tried that. Here is what I have:

Private Sub Worksheet_SelectionChange(ByVal Target As Excel.Range)
iRow = ActiveCell.Row
Worksheets("Formula Master").Range("C" & iRow).Calculate
End Sub

I need it to calculate as soon as something changs on the worksheet. It doesn't do it automatically. I have to click the cell again and then the calculation takes place. Any suggestions.

Posted by DK on February 08, 2002 1:01 PM

I'm not sure what you want to do. What you've got now will recalculate column C in the active row whenever the cell cursor is moved. Maybe you need to use the Worksheet_change event which is fired when the contents of a cell are actually changed. Please elaborate,