Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Excel copying heading to another cell in a selection is made | Excel Worksheet Functions | |||
Combo box disappears after selection made | Excel Discussion (Misc queries) | |||
Choices made available dependent on another selection? | Excel Worksheet Functions | |||
Having A Macro Run When A Selection Is Made In A List Box | Excel Programming | |||
Macro to change list box input range based on selection made in another cell | Excel Programming |