How to recalculate a worksheet when a selection is made

[Guest]

I use data validation to allow selection from a short list.

The data validation is in cell C2.

I am trying to use the Worksheet_SelectionChange event to trigger
recalculation of the worksheet, but the code below doesn't seem to be working.

Could someone point out the syntax error please?

Private Sub Worksheet_SelectionChange(ByVal Target As Excel.Range)
If Intersect(Me.Range("B2"), Target) Is Nothing Then Exit Sub
Me.Calculate
End Sub

Regards

Phil Smith

 

[Norman Jones]

Hi Philip,

Your code works for me in that the selection of cell B2 triggers the
recalculation of the sheet.

However, how is this related to the use of Data Validation in cell C2?

 

[Norman Jones]

Hi Philip,

Your code works for me in that the selection of cell B2 triggers the
recalculation of the sheet.

If, in these circumstances, the recalculation is not being triggered, it is
possible that Events have been turned off.

To eliminate this possibility, in the immediate window type:

Application.EnableEvents = True

and hit Enter.

 

[Guest]

Hi Norman.

The selection of an element from a valid list in cell B2 causes the content
to change in another cell. I know that the code is not working because that
other cell does not change.

Thanks for your updated response and sorry for being thick but what does

"in the immediate window" mean?

Do I insert this into the code in the macro in "This Workbook" module like
the following,

Private Sub Worksheet_SelectionChange(ByVal Target As Excel.Range)
'This macro recalculates the worksheet whenever a
'selection is made from the dropdown list.
Application.EnableEvents = True
If Intersect(Me.Range("B2"), Target) Is Nothing Then Exit Sub
Me.Calculate
End Sub


or should it go somewhere else?

Hi Philip,

Your code works for me in that the selection of cell B2 triggers the
recalculation of the sheet.

If, in these circumstances, the recalculation is not being triggered, it is
possible that Events have been turned off.

To eliminate this possibility, in the immediate window type:

Application.EnableEvents = True

and hit Enter.

 

[Norman Jones]

Hi Philip,

Thanks for your updated response and sorry for being thick but what
does "in the immediate window" mean?

Alt-F11 to go to the VBE (Visual Basic Editor)
Ctrl-G to go to the immediate window.

However...

Do I insert this into the code in the macro in "This Workbook" module like
the following,

Your (original) code should be placed in the worksheet module - *not* the
ThisWorkbook module!

To access the sheet module:

Right-click the sheet tab
View Code

---
Regards,
Norman

Hi Norman.

The selection of an element from a valid list in cell B2 causes the
content
to change in another cell. I know that the code is not working because
that
other cell does not change.

Thanks for your updated response and sorry for being thick but what does

"in the immediate window" mean?

Do I insert this into the code in the macro in "This Workbook" module like
the following,

Private Sub Worksheet_SelectionChange(ByVal Target As Excel.Range)
'This macro recalculates the worksheet whenever a
'selection is made from the dropdown list.
Application.EnableEvents = True
If Intersect(Me.Range("B2"), Target) Is Nothing Then Exit Sub
Me.Calculate
End Sub


or should it go somewhere else?

 

[Guest]

Thanks.

Your responses have been very helpful and I have marked them as such.

This now works if I press [enter] after making the selection from the data
validation list, but not otherwise.