Trouble with ActiveX Control - Combo Box

[Neil]

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

 

[Patrick Molloy]

Its the reason we don't use ActiveX controls any more. Can yuo switch to the
Forms combobox?

 

[Jim Thomlinson]

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?

 

[Jim Thomlinson]

Define we. That is an awfully general statement. I use the ActiveX objects
all the time. When used properly they are very effective and flexible. There
is a time and place for them the same as there is a time and place for forms
controls. Personally I rearely the the forms controls but I started out in
VB6 so the active x controls were a more natural transition for me.

 

[Patrick Molloy]

my company. I don't disagree with you overall. we all have different
experiences.