I have a very simple Excel 2003 workbook that has been constructed as follows:
Save the workbook and reopen (or just set the calculation mode to manual).
When selecting a different value on the ComboBox on Sheet1, notice that calc's fire for both sheets and during the second sheets calc, the calculation mode is now automatic!
Note this does not happen if there is no code in the event for the combo box on Sheet2 (can realize with breakpoints). This behavior also does not occur when changing the combobox on Sheet2, only Sheet1.
Here is the output:
Sheet1 code:
Sheet2 code:
Workbook code:
- Create a ComboBox OLE Control
- Create a list (mine is years)
- Set the ListFillRange to the list using Sheet1!<range></range>
- Set the LinkedCell to another cell on the sheet using Sheet1!<cell></cell>
- Add a click event for the combo box
- Now, create a copy of Sheet1 called Sheet2
- The LinkedCell and ListFillRange of Sheet2 should point to Sheet1 (by default)
- Edit the code in Sheet2 to remove the Me.Calculate call
- In the open of the workbook, set the calculation mode to xlCalculationManual
Save the workbook and reopen (or just set the calculation mode to manual).
When selecting a different value on the ComboBox on Sheet1, notice that calc's fire for both sheets and during the second sheets calc, the calculation mode is now automatic!
Note this does not happen if there is no code in the event for the combo box on Sheet2 (can realize with breakpoints). This behavior also does not occur when changing the combobox on Sheet2, only Sheet1.
Here is the output:
Any ideas why? I need to ensure that it stays manual as I have add-ins that are adversely affected by this behavior.Sheet1 Click [Calculation = -4135]
Sheet2 Click [Calculation = -4105]
Sheet1 code:
Code:
Private Sub ComboBox1_Click()
Debug.Print Me.Name & " Click [Calculation = " & Application.Calculation & "]"
Me.Calculate
End Sub
Code:
Private Sub ComboBox1_Click()
Debug.Print Me.Name & " Click [Calculation = " & Application.Calculation & "]"
End Sub
Code:
Private Sub Workbook_Open()
Application.Calculation = xlCalculationManual
End Sub
Last edited: