How to prevent worksheet change invoking System X Control change event

I have a worksheet ("A") with a System X Control (Combo Box). On this worksheet vbe the code for Private Sub ComboBox1_Change() is:
Worksheet("A").Range("A12").Value = Worksheet("A").Range("O12").Value

Range("O12") contains a formula to derive a value based on the Combo Box selection. The user-displayed Range("A12") adopts the value of "O12" when a new selection is made (this selection and copy works perfectly) - the purpose is to allow the user to subsequently edit its value without corrupting the source formula.

The problem is that when I edit Range("A12") Private Sub ComboBox1_Change() is invoked to return "A12" to the unedited value! Can anyone help, please?

David

Hi Rory
Thank you for your prompt reply.
Yes, the LinkedCell is also on the same worksheet as a reference for the formula (cell O12). The ListFillRange is a named range from another worksheet of the same workbook (this range static and unchanged during the editing of cell A12)

David

Hi Rory
Thank you for your prompt reply.
Yes, the LinkedCell is also on the same worksheet as a reference for the formula (cell O12). The ListFillRange is a named range from another worksheet of the same workbook (this range static and unchanged during the editing of cell A12)

David

Hi Rory
I have temporarily removed the named range from ListFillRange to discover that editing is possible without invoking the Control change event!
Now to find out why this is changing.

David

Hi Rory

I am using a dynamic named range "=OFFSET(Crops!$A$1,2,0,COUNTA(Crops!$A:$A)-1,3)" for the tabulated data on the "Crops" sheet to provide the ComboBox1 data. There are no formulae used on the Crops sheet, just numbers and names - so the dynamic named range should be constant. Any suggestions why the named range could invoke the Control change event?

David

It appears that when cell A12 is edited, the entire workbook recalculates (workbook set to Automatic Recalculation) - and perhaps the dynamic named range used for the Combobox ListFillRange refreshes also (though no change to actual values). As an experiment I set workbook calculation to manual and this allowed me to edit without invoking the Control change event. However, when I then returned the workbook to Automatic Recalculation, the Control change event was immediately invoked. I would appreciate any help in this matter as I do not know where to go from here.
Thanks

David

It appears that when cell A12 is edited, the entire workbook recalculates (workbook set to Automatic Recalculation) - and perhaps the dynamic named range used for the Combobox ListFillRange refreshes also (though no change to actual values). As an experiment I set workbook calculation to manual and this allowed me to edit without invoking the Control change event. However, when I then returned the workbook to Automatic Recalculation, the Control change event was immediately invoked. I would appreciate any help in this matter as I do not know where to go from here.
Thanks

David

Many thanks for your suggestion, shg. Unfortunately, first attempt using this form of formula (adapted for the Crops sheet) crashed the application, losing application data. Probably something I did wrong. Having to rebuild the file so unlikely to report back for a while.

Once again, thanks for your expert advice. If I can get this to work, I can live with a recalculation if/when there are changes to cells in A3:Cwhatever. I will report back to (hopefully) to claim 'success'.

David

The construct for the dynamic range worked well until I applied the named range to the Combobox ListFillRange. This corrupted the named range formula e.g. A:A became XYC:XYC. After correcting the named range formula, it corrupted again when I tried to use the combobox. Removing COUNTA from the formula worked OK - but now no longer a dynamic named range. I am now trying an alternative scheme i.e. a worksheet_activate macro to update the combobox ListFillRange with a fixed range; the range values constructed as a text string using a cell formula employing the COUNTA function. The macro code also disables the combobox change event macro during the update process to preserve any edited values the user has previously made, but enables the cbo change event macro whenever the user makes a new selection. This scheme works OK, though I am still experiencing a problem (see below).

The idea of the scheme is to fix all properties of the cbo to avoid triggering the cbo change event macro except on the following controlled occasions:
1. the worksheet_activate macro to update ListFillRange - this initiates the cbo change event macro (twice for some reason!) but exits immediately due to the code used to disable it
2. when the user makes a new cbo selection

So why do I get a Run-Time error 1004 message (Method 'Worksheets' of object '- Global' failed) with the debug pointing at a line of code in the cbo change event macro when I close the application?
Any suggestions appreciated.

My thanks to Rory and shg for your continued interest. My comments posted Sept 4 refer to results using a dynamic Named Range with a formula "=INDEX(Crops!$A:$A,3):INDEX(Crops!$C:$C,COUNTA(Crops!$A:$A)+1)". I have just tried this again to find that the range does not get corrupted, but that the original complaint remains:
"The problem is that when I edit Range("A12") Private Sub ComboBox1_Change() is invoked to return "A12" to the unedited value!"
The reason for this appears to be that the dynamic range recalculates with Automatic Calculation mode, which invokes the cbo change event which over-writes the edited data.
Column A contains numbers; B-C text or blank.

I have basically concluded that binding a dynamic Named Range to cbo ListFillRange and cbo change event macros are incompatable when working in Automatic Calculation mode, so as Rory has suggested I am using code to assign the values to the control.

Please see my posting today. Any help on the remaining problem would be much appreciated.

I am closing this thread. Some kind expert ought to write a 'hazards warning' with possible fixes for those people wishing to use System X Controls on worksheets. The basic question entitling this thread has not really been answered. I have deduced that the Change Event associated with a Combobox is triggered not just by making a new selection, but also by changes to the RowSource values. In my case, I was using a Dynamic Named Range for the RowSource. Although the conditions were stable (i.e. the range represented by the Dynamic Named Range remained the same), it transpired that each time a spreadsheet calculation occurred (in Automatic Calculation mode this will happen to all open workbooks when the user enters a value in a worksheet), this 'disturbance' initiated the ComboBox Change Event macro. My solution was to construct the RowSource range as a text string using a cell formula, then assigning this value to RowSource using a single line of code.