combo box on change causes function to execute - sort of...

[scroce]

Hi folks - I have something that almost works. here's the code


Private Sub cboDeductible_Change()
Dim TIVvalue
TIVvalue = Worksheets("EquipBreakdown2&quot.Range("c7&quot
Call PremCalc(TIVvalue)
End Sub



Function PremCalc(TIV) As Currency

Dim Deductible

Deductible = Sheet7.cboDeductible.Value

Select Case TIV

Case Is < 6000001
PremCalc = 500
Case Is < 15000000
If Deductible = 1000 Then
PremCalc = TIV * 0.0827
ElseIf Deductible = 2500 Then
PremCalc = TIV * 0.0735
ElseIf Deductible = 5000 Then
PremCalc = TIV * 0.661
End If
Case Is >= 15000000
If Deductible = 1000 Then
PremCalc = TIV * 0.0816
ElseIf Deductible = 2500 Then
PremCalc = TIV * 0.0735
ElseIf Deductible = 5000 Then
PremCalc = TIV * 0.661
End If

End Select

End Function


The PremCalc variable changes dependent on the TIV value someone enters in cell c7. Right next to that I have cboDeductible that can either be 1000, 2500, or 5000.

When I run the code in debug mode, everything calculates correctly. The PremCalc immediately updates on the spreadsheet when someone enters a someting in cell c7. However, the sheet DOES NOT update to the new PremCalc value whevever someone changes the deductible in the combo box EVEN THOUGH it calculates correctly in debug mode.

Why isn't excel updating to my new PremCalc value when I use the combo box?? I tried hitting f9 to recalc, but that didn't work.

HELP!!
[/color] Q: Why is my computer doing that?
A: Random Perversity of Inanimate
Objects

 

[RobBroekhuis]

Tell us more about what does/doesn't happen. Does your change event run? (put a breakpoint on the first line of the sub to try). Or is the deductible value not properly reflected in the new calculation? Rob

 

[scroce]

the change event runs, everything calculcates perfectly. the only thing that does not happen is that the answer does not change on the spreadsheet itself.

It's like the function runs, but the cell containing the function doesn't change.

I'm close to getting a workaround, but the crux of the question is why does the function update when I change the regular cell, but not when I change the value in the combo box.

Also - does anyone know how to populate a vba combo box with values that are not in a range on the sheet? right now I am using the ListFillRange property of the combo box to populate it. I would much rather just type the choices into the code (because they will never change) than to have them linked to a range where people can easily delete or change them.
Q: Why is my computer doing that?
A: Random Perversity of Inanimate
Objects

 

[RobBroekhuis]

But where is the value of the PremCalc function ASSIGNED to the cell? I don't see your code doing that. In your deductible_change sub, put something like

range(&quot;C5&quot=premcalc(TIValue)

Rob

 

[RobBroekhuis]

As for your follow-up question, depends on what kind of combo box you're using. You are probably using one from the &quot;forms&quot; toolbar, rather than the &quot;control toolbox&quot; toolbar. The latter are more flexible, and give you more control over the contents of the list.
Rob

 

[scroce]

Rob - I appreciate the discussion.

For the latter question,

I'm definitely using the &quot;control toolbox&quot; toolbar, and i still can't seem to figure out how to populate the combo box with anything other than a range on a sheet. I'd like to be able to use an array.

For the first question:

you're right - i'm not assigning the answer to a certain cell. I'm calling the function by calling:

=PremCalc(c7)

in the cell that I want to show the answer in.

The function will recalculate automatically if I change whatever is in cell c7, but if I change cboDeductible, it does not recalculate, and I have to re-enter something again in c7 for the function to recalculate.

My workaround does incorporate something like what you have said above, i.e. range(&quot;C5&quot=premcalc(TIValue) - but i was trying to avoid having to do that.

Q: Why is my computer doing that?
A: Random Perversity of Inanimate
Objects

 

[RobBroekhuis]

What you could do is have the combobox change event write its value to the cell underneath the actual box, say C9. Also, make your PremCalc function have TWO arguments - one for TIV, the other for deductible - and call it as

=premcalc(c7,c9)

You don't need to call the premcalc function from your change event - the fact that you change a cell that is referenced in your formula will automatically trigger an excel recalculation. You may need to put application.volatile as the first line of your PremCalc function.
Rob

 

[scroce]

Rob -

you wrote:


&quot;You don't need to call the premcalc function from your change event - the fact that you change a cell that is referenced in your formula will automatically trigger an excel recalculation&quot;


This is exactly what I thought initially. However when the recalculation didn't work automatically, I tried to force it by calling the premcalc function from the change event.

Also - I haven't heard of application.volatile before. I'll try that.

Again - I sincerely appreciate your input.


Q: Why is my computer doing that?
A: Random Perversity of Inanimate
Objects

 

[RobBroekhuis]

scroce,
Changing a combobox value is not enough to trigger the recalc. And calling the function (from the combobox change event) won't help to recalculate cells that use that function. That's why I suggest you put as your combobox_change event:

Private Sub cboDeductible_Change()
range(&quot;C9&quot=cboDeductable
End Sub

This will definitely trigger a recalc, if C9 is referenced in your PremCalc formula. Have you tried it yet?

Rob