| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Trouble with ActiveX Control - Combo Box
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 |
|
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Trouble with ActiveX Control - Combo Box
Its the reason we don't use ActiveX controls any more. Can yuo switch to the
Forms combobox? "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 . |
|
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Trouble with ActiveX Control - Combo Box
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 . |
|
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Trouble with ActiveX Control - 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. -- HTH... Jim Thomlinson "Patrick Molloy" wrote: Its the reason we don't use ActiveX controls any more. Can yuo switch to the Forms combobox? "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 . |
|
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Trouble with ActiveX Control - Combo Box
my company. I don't disagree with you overall. we all have different
experiences. "Jim Thomlinson" wrote in message ... 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. -- HTH... Jim Thomlinson "Patrick Molloy" wrote: Its the reason we don't use ActiveX controls any more. Can yuo switch to the Forms combobox? "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 . |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| 2007 Form Control/ActiveX Control font difference | Excel Discussion (Misc queries) | |||
| ActiveX Control Combo Box | Excel Programming | |||
| publish activeX combobox and other activeX control | Excel Programming | |||
| Trouble call VB ActiveX dll from Excel | Excel Programming | |||
| Trouble w/ ActiveX Controls (no userform) Excel 2002. | Excel Programming | |||
![Neil[_30_] is offline](https://www.excelbanter.com/images/SultanThemeVB3R/statusicon/user_offline.gif)
Linear Mode
