Drop Down forces calculate

**demuro1** · 10-12-2008, 02:07 PM

I am using a spreadsheet with a user defined function that contains an if and case statements fromthis thread. I am using a combo box to change the dependent cells but need something to automatically calculate the sheet. is there a way I can have a calculation occur every time a selection is made in the combo box or is there a macro that will do it. I'm fairly new to VBA so some direction with the solution would be greatly appreciated

thanks

**Richard Buttrey** · 10-12-2008, 02:15 PM

How about

Please Login or Register  to view this content.

But why are you having to calculate at all? Have you turned Automatic calc off?

**demuro1** · 10-12-2008, 02:39 PM

no I have automatic calculate on but for some reason it's not doing it. not only that but when I click calc now it doesn't calculate either. Give it a try in the spreadsheet.

how would I implement your suggested code?

**Richard Buttrey** · 10-12-2008, 03:09 PM

Originally Posted by demuro1

no I have automatic calculate on but for some reason it's not doing it. not only that but when I click calc now it doesn't calculate either. Give it a try in the spreadsheet.

how would I implement your suggested code?

Hi,

I'd missed the point that you were using a UDF - which often causes problems for the reasons that are very well explained in the link below.

You can use the

Please Login or Register  to view this content.

instruction at the top of your UDF code, but please note the downsides explained in the link.

The other thing you could do to avoid the potential problems with using the Application.Volatile is to force a recalculation by copying and pasting one of your cells.

e.g.

Please Login or Register  to view this content.

http://www.decisionmodels.com/calcsecretsj.htm
HTH

**mikerickson** · 10-12-2008, 03:13 PM

Your function adjust takes values from A16 and A29 directly, not as arguments. Thus changeing the values in those cells doesn't trigger a Calculation. Plus, it makes editing the spreadsheet (eg. adding a row) more difficult. If you pass those as an argumnent to your UDF, it will calculate when their values are changed.
The formula =adjust(C11) becomes =adjustMark2(C11,$A$16,$A$29)

Please Login or Register  to view this content.

**demuro1** · 10-12-2008, 03:35 PM

Originally Posted by mikerickson

Your function adjust takes values from A16 and A29 directly, not as arguments. Thus changeing the values in those cells doesn't trigger a Calculation. Plus, it makes editing the spreadsheet (eg. adding a row) more difficult. If you pass those as an argumnent to your UDF, it will calculate when their values are changed.
The formula =adjust(C11) becomes =adjustMark2(C11,$A$16,$A$29)

it works perfectly except that when $a$16=2 and $a$29= anything but 1 it returns zero "0"

any suggestions. other than that it's perfect

thanks so much everyone

I fixed it I just added one more elseif statement for the given condition. Man you guys are so awesome thanks so much for the help

**mikerickson** · 10-12-2008, 06:18 PM

I noticed that behaviour, its the same behaviour as the function Adjust.
Just as each of the Select Cases has a Case Else, the If...ElseIF...ElseIf...End If needs an final Else clause to deal with the unspecified situations.