Auto-update (calculate) the Excel sheet when a cell is changed

[cercig]

I am using Excel 2010 and I want that the Excel sheet will be updated (calculated) automatically when the value of a cell (<code style="margin: 0px; padding: 1px 5px; border: 0px; font-size: 13px; font-family: Consolas, Menlo, Monaco, 'Lucida Console', 'Liberation Mono', 'DejaVu Sans Mono', 'Bitstream Vera Sans Mono', 'Courier New', monospace, sans-serif; white-space: pre-wrap; background-color: rgb(238, 238, 238);">Range("B7")</code>) is changed (The <code style="margin: 0px; padding: 1px 5px; border: 0px; font-size: 13px; font-family: Consolas, Menlo, Monaco, 'Lucida Console', 'Liberation Mono', 'DejaVu Sans Mono', 'Bitstream Vera Sans Mono', 'Courier New', monospace, sans-serif; white-space: pre-wrap; background-color: rgb(238, 238, 238);">Range("B7")</code> is a drop-down list). I am trying to use the intersect-target method like:
<code style="margin: 0px; padding: 0px; border: 0px; font-family: Consolas, Menlo, Monaco, 'Lucida Console', 'Liberation Mono', 'DejaVu Sans Mono', 'Bitstream Vera Sans Mono', 'Courier New', monospace, sans-serif; white-space: inherit;">Private Sub Worksheet_Change(ByVal target As Range)
If Not Intersect(target, Range("B7")) Is Nothing Then
ActiveSheet.Calculate
End If
End Sub</code>I have two questions about this:
Does it matter if I put this Private Sub under any module? Or am I supposed to write it in the exact sheet under the Microsoft Excel -object menu in VBA?
The second question is how I can make this work? The sheet doesn't update (calculate) automatically when I change the <code style="margin: 0px; padding: 1px 5px; border: 0px; font-size: 13px; font-family: Consolas, Menlo, Monaco, 'Lucida Console', 'Liberation Mono', 'DejaVu Sans Mono', 'Bitstream Vera Sans Mono', 'Courier New', monospace, sans-serif; white-space: pre-wrap; background-color: rgb(238, 238, 238);">Range("B7")</code>. It only updates when I save the Excel file.

 

[Fishboy]

I am using Excel 2010 and I want that the Excel sheet will be updated (calculated) automatically when the value of a cell (<code style="margin: 0px; padding: 1px 5px; border: 0px; font-size: 13px; font-family: Consolas, Menlo, Monaco, 'Lucida Console', 'Liberation Mono', 'DejaVu Sans Mono', 'Bitstream Vera Sans Mono', 'Courier New', monospace, sans-serif; white-space: pre-wrap; background-color: rgb(238, 238, 238);">Range("B7")</code>) is changed (The <code style="margin: 0px; padding: 1px 5px; border: 0px; font-size: 13px; font-family: Consolas, Menlo, Monaco, 'Lucida Console', 'Liberation Mono', 'DejaVu Sans Mono', 'Bitstream Vera Sans Mono', 'Courier New', monospace, sans-serif; white-space: pre-wrap; background-color: rgb(238, 238, 238);">Range("B7")</code> is a drop-down list). I am trying to use the intersect-target method like:
<code style="margin: 0px; padding: 0px; border: 0px; font-family: Consolas, Menlo, Monaco, 'Lucida Console', 'Liberation Mono', 'DejaVu Sans Mono', 'Bitstream Vera Sans Mono', 'Courier New', monospace, sans-serif; white-space: inherit;">PrivateSub Worksheet_Change(ByVal target As Range)
IfNot Intersect(target, Range("B7"))IsNothingThen
ActiveSheet.Calculate
EndIf
EndSub</code>I have two questions about this:
Does it matter if I put this Private Sub under any module? Or am I supposed to write it in the exact sheet under the Microsoft Excel -object menu in VBA?
The second question is how I can make this work? The sheet doesn't update (calculate) automatically when I change the <code style="margin: 0px; padding: 1px 5px; border: 0px; font-size: 13px; font-family: Consolas, Menlo, Monaco, 'Lucida Console', 'Liberation Mono', 'DejaVu Sans Mono', 'Bitstream Vera Sans Mono', 'Courier New', monospace, sans-serif; white-space: pre-wrap; background-color: rgb(238, 238, 238);">Range("B7")</code>. It only updates when I save the Excel file.

Hi cercig,

In order for this to work you need to add the code directly to the back end of the sheet in question, so if you want Sheet2 to recalculate when B7 of Sheet2 is updated or changed, right-click on the tab name of Sheet2 and select View Code. It is in this window that you need to paste your code.

Doing so should answer both of your questions above.

Finally, don't forget to ensure the syntax you use is correct (there appear to be a lot of missing spaces in your code)

Private Sub Worksheet_Change(ByVal Target As Range)
If Not Intersect(Target, Range("B7")) Is Nothing Then
ActiveSheet.Calculate
End If
End Sub

 

[cercig]

Hi cercig,

In order for this to work you need to add the code directly to the back end of the sheet in question, so if you want Sheet2 to recalculate when B7 of Sheet2 is updated or changed, right-click on the tab name of Sheet2 and select View Code. It is in this window that you need to paste your code.

Doing so should answer both of your questions above.

Finally, don't forget to ensure the syntax you use is correct (there appear to be a lot of missing spaces in your code)

Private Sub Worksheet_Change(ByVal Target As Range)
If Not Intersect(Target, Range("B7")) Is Nothing Then
ActiveSheet.Calculate
End If
End Sub

@fishboy Thanks for the tips. I also realized that calculation inside the Excel menu is adjusted to "Manual Calculation". That's why my sheet did't update even if I tried inside the VBA code.

 

[Fishboy]

@fishboy Thanks for the tips. I also realized that calculation inside the Excel menu is adjusted to "Manual Calculation". That's why my sheet did't update even if I tried inside the VBA code.

You're welcome. Glad to hear you got it sorted.