How to recalculate a worksheet when a selection is made
 

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

How to recalculate a worksheet when a selection is made
 

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?


---
Regards,
Norman



"Philip J Smith" wrote in message
...
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

How to recalculate a worksheet when a selection is made
 

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.


---
Regards,
Norman



"Norman Jones" wrote in message
...
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?


---
Regards,
Norman

How to recalculate a worksheet when a selection is made
 

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?



"Norman Jones" wrote:

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.


---
Regards,
Norman



"Norman Jones" wrote in message
...
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?


---
Regards,
Norman

How to recalculate a worksheet when a selection is made
 

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



"Philip J Smith" wrote in message
...
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?



"Norman Jones" wrote:

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.


---
Regards,
Norman



"Norman Jones" wrote in message
...
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?


---
Regards,
Norman

How to recalculate a worksheet when a selection is made
 

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.

"Norman Jones" wrote:

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



"Philip J Smith" wrote in message
...
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?



"Norman Jones" wrote:

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.


---
Regards,
Norman



"Norman Jones" wrote in message
...
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?


---
Regards,
Norman