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

Question

I am using Excel 2010 and I want that the Excel sheet will be updated (calculated) automatically when the value of a cell (Range("B7")) is changed (The Range("B7") is a drop-down list). I am trying to use the intersect-target method like:

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

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 Range("B7"). It only updates when I save the Excel file.

Answer 1

The function should be in the sheet which B7 belongs to, e.g. if the B7 you are updating is in "Sheet1" then the code for the Worksheet_Change event should be under Sheet1 too. You can right click the tab for the sheet, and select show code to get to the right place in VBE.

Excel should be aware that any cell using B7 needs to be recalculated when changed, so that should be handled automatically.

Answer 2

Your code will work if you place it in the sheets event not in Modlue please find the below image for your reference.

Select the respective sheet in VB window and double click on it, it will open the worksheet event window (code window) where you need to copy paste the codes. Now try changing the validation cell it should work :)..