Results 1 to 5 of 5

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

This is a discussion on ComboBoxes with shared LinkedCell's and click/change event code on two sheets set calculation to automatic within the Excel Questions forums, part of the Question Forums category; I have a very simple Excel 2003 workbook that has been constructed as follows: Create a ComboBox OLE Control Create ...

  1. #1
    New Member
    Join Date
    Mar 2009
    Posts
    3

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

    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!
    4. Set the LinkedCell to another cell on the sheet using Sheet1!
    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 by rw712; Mar 18th, 2009 at 09:53 PM. Reason: More detail

  2. #2
    Board Regular
    Join Date
    May 2004
    Posts
    379

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

    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.

  3. #3
    New Member
    Join Date
    Mar 2009
    Posts
    3

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

    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?

  4. #4
    New Member
    Join Date
    Mar 2009
    Posts
    3

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

    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

  5. #5
    Board Regular
    Join Date
    May 2004
    Posts
    379

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

    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 ...

Tags for this Thread

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  


DMCA.com