VBA referencing drop down menu, and calulating based on choice in menu

[rheinevetter]

I am new to macros/VBA so bear with me.

I have made two drop down menus that will influence which calculations to perform.

for simplicity,
Drop down 1 has options: "one" or "two"
Drop down 2 has options: "summer" or "fall"

So if the user chooses "one", and "summer" then after the button click, it will preform one set of calculations and display the results in a column of cells (D).
different combinations of the drop downs will calculate using other formulas.

So how can I have the code read the drop down string, then based on what it is, execute the calculations and display them in column D.


for ex.

If Dropdown 1 = one and Dropdown 2 = summer Then
D1 = A1+A2
D2 = A3/A4

ElseIf Dropdown 1 = two and Dropdown 2 = fall Then
D1 = A1-A2
D2 = A3*A4
....
ect

Thanks

[ajryan88]

Hi,

Are the drop-down lists ActiveX objects, or are they data validation lists within a cell?

Could you please upload an example workbook so that I may look at your code in context?

Thanks

[rheinevetter]

Capture.PNGCapture2.PNG

So here is a very simplified version. The drop downs are in text validation. The two drop down options are resi, and smooth. I would like the calculations to change in cell B7 as shown in the pictures.

I did come up with a work around to this problem by using VLOOKUP and 1's and 0's. B7 had both equations in it and each equation was multiplied by a hidden set of operator cells, which change between a 1 and a 0 depending on the drop down menu selection.

so if resi is selcected, D1 = 0 and D2 = 1, so the resi equation is multiplied by D2 and the smooth equation by D1.

Seems to be working?

[ajryan88]

Hi again,

Try this formula in cell B7 (this should work for both "smooth" and "resi"):

Please Login or Register  to view this content.

or alternatively use:

Please Login or Register  to view this content.

Both of these formulae will produce the same output, it is simply up to you which one you choose to use.

Hope this helps

P.S. If this is the solution that you were after, please don't forget to mark this thread as solved and click on the * next to my post to say thanks