The combo box change event fires only when the combo box changes. It is not
fired by sheet changes. Since your combo box is populated by a dynamic named
range is it possible that your named range is changing there-by changing the
values of the combo box? How are you implimenting populating the combo box?
--
HTH...

Jim Thomlinson


"Neil" wrote:

Hi All,

I have a painful combo box that I'd appreciate some assistance with.

The combo box in question looks up a (dynamic named range) list of
'scenarios' located on another sheet.
Once the preferred scenario is selected from the drop down list, my macro
runs, transferring a number of values from the scenarios sheet using:
worksheets("Analysis").cells(x,y).value = worksheets("Scenario
Inputs").cells(a,b).value

Simple stuff really.
Problem is, whenever I change any of the transferred values on the
"Analysis" sheet manually, the value resets to the one transferred by the
combo box macro.

I gather the macro runs because a change has occurred, ie. it runs when the
worksheet changes...

The code below:

Private Sub ComboBox1_Change()

Application.ScreenUpdating = False

Worksheets("Analysis").Cells(1, 16).Value = Worksheets("Scenario
Inputs").Cells(9, 2).Value
Worksheets("Analysis").Cells(8, 2).Value = Worksheets("Scenario
Inputs").Cells(9, 3).Value
Worksheets("Analysis").Cells(8, 3).Value = Worksheets("Scenario
Inputs").Cells(9, 4).Value
Worksheets("Analysis").Cells(8, 5).Value = Worksheets("Scenario
Inputs").Cells(9, 6).Value
Worksheets("Analysis").Cells(8, 7).Value = Worksheets("Scenario
Inputs").Cells(9, 7).Value
Worksheets("Analysis").Cells(9, 5).Value = Worksheets("Scenario
Inputs").Cells(9, 9).Value
Worksheets("Analysis").Cells(9, 7).Value = Worksheets("Scenario
Inputs").Cells(9, 10).Value
Worksheets("Analysis").Cells(19, 5).Value = Worksheets("Scenario
Inputs").Cells(9, 12).Value
Worksheets("Analysis").Cells(19, 7).Value = Worksheets("Scenario
Inputs").Cells(9, 13).Value
Worksheets("Analysis").Cells(20, 8).Value = Worksheets("Scenario
Inputs").Cells(9, 15).Value
Worksheets("Analysis").Cells(30, 17).Value = Worksheets("Scenario
Inputs").Cells(9, 17).Value
Worksheets("Analysis").Cells(31, 17).Value = Worksheets("Scenario
Inputs").Cells(9, 18).Value
Worksheets("Analysis").Cells(32, 17).Value = Worksheets("Scenario
Inputs").Cells(9, 20).Value
Worksheets("Analysis").Cells(4, 19).Value = Worksheets("Scenario
Inputs").Cells(9, 22).Value
Worksheets("Analysis").Cells(5, 19).Value = Worksheets("Scenario
Inputs").Cells(9, 23).Value
Worksheets("Analysis").Cells(6, 19).Value = Worksheets("Scenario
Inputs").Cells(9, 24).Value

Application.ScreenUpdating = True

End Sub

lives in Sheet7 (Analysis) in the VBAProject window.

.......I thought that by virtue of the ComboBox1_Change() status, that this
macro would only run when I caused a change in the combobox.

Do I need to scope thie behaviour of this control tighter still.

I can of course run the scenario using a separate macro form button after
each change, this kind of defeats the purpose of being able to run scenarios
close together to show differences....

I appreciate the assistance.

Neil



.