How to invoke a formula on DropDown_Change?

[bartk]

Dear members,


When a user makes a new selection in either Combo box DropDown1 or DropDown3, I want the following formula to be called upon to place a value in cell O2.

{=INDEX(lstMinQty,MATCH(valSize&valDirection&valUps,lstSize&lstDirection&lstUps,0))}


The resulting value in O2 informs the user that this is the minimum quantity based on his/her selection.

The user can then over-write the value in O2 by entering a value above the default minimum within the range set by Data Validation:

-Allow: Whole number

-Data: Between

-Minimum: =500*E31

-Maximum: =5000*E31-1

However, if the user then returns to the drop down lists and makes a new selection in DropDown 1 or 3, I need the minimum quantity as defined by the formula to re-appear in cell O2.

I've unsuccessfully attempted to do this as follows:


Macro name: DropDown1_Change


Sub DropDown1_Change()

Range("O2").Formula = "{=INDEX(lstMinQty,MATCH(valSize&valDirection&valUps,lstSize&lstDirection&lstUps,0))}"

End Sub


Macro name: DropDown3_Change


Sub DropDown3_Change()

Range("O2").Formula = "{=INDEX(lstMinQty,MATCH(valSize&valDirection&valUps,lstSize&lstDirection&lstUps,0))}"

End Sub


Any suggestions would be much appreciated.


Thank you.


Bart

 

[NARAYANK991]

Hi Bart ,


The syntax is :


Range("O2").FormulaArray = "=INDEX(lstMinQty,MATCH(valSize&valDirection&valUps,lstSize&lstDirection&lstUps,0))"


since the formula you wish to enter in O2 is an array formula.


Narayan

 

[bartk]

Thanks Narayan -the only problem now is that the macro doesn't run automatically when a new selection is made in Dropdown 1 or 3. It does if I go to Tools menu >Macro >Macros > Run


I've conducted a search to find a solution, but without success.


Any ideas?

 

[NARAYANK991]

Hi Bart ,


The macro will run automatically , since it has been assigned to the combo box ; the formula is dependent on the following :


valSize , valDirection , valUps


If the combo box selection does not change any one of the above three cells , then you will not see any change in the value of cell O2. This does not mean that the macro is not getting executed.


The formula inserted by the macro is a normal Excel formula ; you can manually enter the same formula in any other cell , and whenever any of the above three cells changes , you should see the value in the cell which has the formula change , assuming that the lstMinQty range has different values for different combinations of valSize , valDirection and valUps.


Narayan

 

[bartk]

Hi Narayan,


Thanks for your comments.


A selection in a combo box does change the value in one or two of valSize, valDirection, valUps.


Initially, the formula in 02 is =INDEX(lstMinQty,MATCH(valSize&valDirection&valUps,lstSize&lstDirection&lstUps,0)) and any selection change in Dropdown1 or 3 updates the value in 02 as required to display the minimum quantity.


However, when one manually enters a higher quantity in 02 the formula is replaced by the entered number. It is at this point that the system is breaking down. A combo box selection from then on does not re-instate the formula in 02 despite changes occurring for one or two of valSize, valDirection, valUps. If I Run the macro from the Tools menu the formula returns to 02 and it works again.


Bart

 

[NARAYANK991]

Hi Bart ,


I don't understand ; suppose you delete the contents of O2 , and change the combo box selection , does the formula get entered in O2 ?


Narayan

 

[bartk]

Hi Narayan,


Thank you for your patience.


Cell O2 is never empty. When the Workbook is opened the default value there is set by the formula and represents the minimum quantity allowed. {=INDEX(lstMinQty,MATCH(valSize&valDirection&valUps,lstSize&lstDirection&lstUps,0))}


Note, I've also got the following formula in the drop down macros.

Range("O2").FormulaArray = "=INDEX(lstMinQty,MATCH(valSize&valDirection&valUps,lstSize&lstDirection&lstUps,0))"


Selections in the drop downs change valSize, valDirection or valUps and updates the minimum quantity value in O2 -not a problem.


If the user then over writes the value in O2 by entering a higher value, the formula which set the minimum quantity disappears -not a problem.


However, when the user then goes back to the drop downs to change their selections I want the minimum value for that selection combination to appear in O2, thus over writing their manual entry in O2. At this point, O2 should now be back to the same state as when the Wookbook was opened. However, changing drop down selections does not then place the formula back in O2 to return the minimum quantity value. Nothing happens.


Regards,


Bart


The aim is that the value calculated by the formula is automatically

 

[NARAYANK991]

Hi Bart ,


My point in asking was to know whether the macro is getting executed or not ; if you delete the contents of O2 , at any time after the workbook is opened , and change the combo box selection , the formula in O2 should appear if the macro gets executed ; if it doesn't , then O2 will remain blank.


Have you tried this ? What was the result ?


Narayan

 

[bartk]

Hi Narayan,


If I delete the contents of O2 and leave it blank then I get a message stating "Microsoft Excel cannot calculate a formula" -probably because other cells use the value of O2.


However, if I enter the formula in a blank cell such as E50 and change the range to E50 in the drop down macros, then I can perform your test. Once I delete the value in E50 leaving it blank, then a change in a combo box selection changes one or two of the values for valSize , valDirection or valUps, BUT E50 remains blank.


Bart

 

[NARAYANK991]

Hi Bart ,


I'll put down all that I did , and you can see where we differ :


1. I created the following 7 named ranges :


a. lstSize : =Sheet1!$J$16:$J$22

b. lstDirection : =Sheet1!$K$16:$K$22

c. lstUps : =Sheet1!$L$16:$L$22

d. lstMinQty : =Sheet1!$M$16:$M$22


e. valSize : =Sheet1!$C$16

f. valDirection : =Sheet1!$D$16

g. valUps : =Sheet1!$E$16


2. I inserted 3 Forms control combo boxes , by default they were named Dropdown 1 , Dropdown 2 and Dropdown 3.


3. The Input ranges for the 3 combo boxes were set to lstSize , lstDirection and lstUps.


4. The Cell Links for the 3 combo boxes were set to A1 , B1 and C1.


5. In the cell named valSize , the following formula was entered : =INDEX(lstSize,A1)


Similarly , in valDirection , the following formula was entered : =INDEX(lstDirection,B1)


In valUps , the following formula was entered : =INDEX(lstUps,C1)


6. After right-clicking the Dropdown 1 combo box , a macro was assigned to it , which on clicking New , automatically created the following macro in a new module :


Sub DropDown1_Change()

End Sub


7. In this blank procedure , the following statement was inserted :


Range("O2").FormulaArray = "=INDEX(lstMinQty,MATCH(valSize&valDirection&valUps,lstSize&lstDirection&lstUps,0))"


8. I manually deleted the contents of cell O2 , and changed the selection in Dropdown 1 ; the above formula was inserted in O2 , and the correct value was displayed. Whenever I changed the selection using Dropdown 1 , the formula changed its value correctly.


Narayan

 

[bartk]

Hi Narayan,


Thanks for going to that effort. There are differences and your method definitely works, so I'll consider this further before responding.


Regards,


Bart

 

[bartk]

Hi Narayan,


I'm so grateful for your last contribution which has set me straight. Your method is much more direct and not prone to the problems I'd unnecessarily caused myself due to my inexperience. I've now restructured my worksheet entirely.


I finally discovered that the macro wasn't being automatically run on a dropdown change -not sure why. The solution was to instead record a macro, hit stop without actually doing anything, then edit it by highlighting the green text and pasting in my instruction.


Thanks very much for all of your assistance.


Sincerely,

Bart