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