Trouble with ActiveX Control - Combo Box

  • Thread starter Thread starter Neil
  • Start date Start date
N

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
 
Its the reason we don't use ActiveX controls any more. Can yuo switch to the
Forms combobox?
 
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?
 
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.
 
my company. I don't disagree with you overall. we all have different
experiences.
 
Back
Top