Trouble with ActiveX Control - Combo Box

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
 
P

Patrick Molloy

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

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?
 
J

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

Patrick Molloy

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

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top