ComboBoxes with shared LinkedCell's and click/change event code on two sheets set calculation to automatic

rw712

New Member
Joined
Mar 18, 2009
Messages
3
I have a very simple Excel 2003 workbook that has been constructed as follows:

  1. Create a ComboBox OLE Control
  2. Create a list (mine is years)
  3. Set the ListFillRange to the list using Sheet1!<range></range>
  4. Set the LinkedCell to another cell on the sheet using Sheet1!<cell></cell>
  5. Add a click event for the combo box
  6. Now, create a copy of Sheet1 called Sheet2
  7. The LinkedCell and ListFillRange of Sheet2 should point to Sheet1 (by default)
  8. Edit the code in Sheet2 to remove the Me.Calculate call
  9. 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:
Sheet1 Click [Calculation = -4135]
Sheet2 Click [Calculation = -4105]
Any ideas why? I need to ensure that it stays manual as I have add-ins that are adversely affected by this behavior.

Sheet1 code:
Code:
Private Sub ComboBox1_Click()
  Debug.Print Me.Name & " Click [Calculation = " & Application.Calculation & "]"
  Me.Calculate
End Sub
Sheet2 code:
Code:
Private Sub ComboBox1_Click()
  Debug.Print Me.Name & " Click [Calculation = " & Application.Calculation & "]"
End Sub
Workbook code:
Code:
Private Sub Workbook_Open()
  Application.Calculation = xlCalculationManual
End Sub
 
Last edited:

Excel Facts

Difference between two dates
Secret function! Use =DATEDIF(A2,B2,"Y")&" years"&=DATEDIF(A2,B2,"YM")&" months"&=DATEDIF(A2,B2,"MD")&" days"
I agree that the debug.print shows that calculation has been switched to automatic, BUT a recalculation is not actually triggered.

You can check by adding =Now() formatted to hh:mm:ss to both sheets, and by adding another workbook also with =Now() in: only the sheet1 NOW() gets updated when in Manual calc mode.
 
Upvote 0
Thanks for the response, Charles. I agree that NOW is not updated in the other sheet. The problem is that the _Click event in the other sheet runs (causing the debug.print to execute) and I don't think it should.

Any ideas what I can check in the _Click (and _Change) events to see if an IF block should not really execute as a result of this behavior?
 
Upvote 0
Even more interesting, if I add Me.Calculate to the second sheet, NOW() does not recalculate even though Me.Calculate executes. I wonder if something special occurs in the Worksheet functions to work around this behavior.

Revised sheet1 code:
Code:
Private Sub ComboBox1_Click()
  Debug.Print Me.Name & " Click [Calculation = " & Application.Calculation & "]"
  Sheet1.Calculate
  Debug.Print "calculated sheet1"
End Sub
Revised sheet2 code:
Code:
Private Sub ComboBox1_Click()
  Debug.Print Me.Name & " Click [Calculation = " & Application.Calculation & "]"
  Sheet2.Calculate
  Debug.Print "calculated sheet2"
End Sub
New output:
Sheet1 Click [Calculation = -4135]
Sheet2 Click [Calculation = -4105]
calculated sheet2
calculated sheet1
 
Upvote 0
I am not sure, but I suspect there is special behaviour for stuff embedded in the display layer rather than the formula layer.

For instance UDFs used in conditional formats get executed whenever any piece of screen showing the cell gets refreshed, even in manual mode, but breakpoints inside the UDF do not fire. And a UDF that references the .TEXT property tends to get the value that was displayed before the calculation, because the display layer has not yet been refreshed.

Personally I try to avoid embedding controls on worksheets ...
 
Upvote 0

Forum statistics

Threads
1,213,538
Messages
6,114,218
Members
448,554
Latest member
Gleisner2

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top