Want to call Macro when cell changes as a result of Combo Box Choice

I have a combo Box that is tied to E6 - so based on the combo box choice E6 becomes 1-4

I have a macro that changes the value of a cell based on E6 (running fine)

Please Login or Register  to view this content.

I need macro that runs MpsCalc whenever the number in E6 changes. This is what I am trying:

Please Login or Register  to view this content.

I have both of these in Sheet1 (the sheet that changes)

What is going wrong with calling the macro?


Thanks

I can't see anything here that would prevent this from working. I would be happy to look at it if you could attach your workbook. Have you tried to walk through it with the debugger to see if it's getting executed the way you think it is? For example, if you did some debugging and ended up calling

Please Login or Register  to view this content.

but an error occurred and you never got to the line of code setting it back to True, then that setting "sticks" and has to be reset manually by making the call in the Immediate window (or by closing and re-opening Excel).

I do have a couple of suggestions. First, using strings for arithmetic is not a particularly good practice. I would use the form below:

Please Login or Register  to view this content.

Second, your test for what cell is changing is lengthier than necessary to check for a single cell, here is one alternative. The bracketed expression [x] is equivalent to

Range("x")

This is really more for readability than anything else and is a matter of personal taste, just wanted to offer it:

Please Login or Register  to view this content.

Change MpsCalc from Private to Public. Now go into Design mode and right-click on the combobox, and select "Assign Macro...". Select MspCalc from the list. Now whenever the value selected in the combobox changes, it will automatically call MpsCalc.