Customize ActiveX SpinButton Value - Not Decrementing (or Incrementing) Correctly

[detangler]

Please help me with this!! I'm not a vba programmer and have exhausted very means and have done tons of research and came up with the attached code.

Basically, I have an Active X SpinButton control directly on a spreadsheet that changes the input cell. (The idea is that later on, there'll be a bunch of these input cells, accompanied by their respective SpinButton controls).

I'm allowing the user to define the Min, Max, and Step (SmallChange) value of the SpinButton because (1) I need it to do decimal points and negative numbers and (2) that's what my boss would have wanted.

So I set the Min, Max, SmallChange value in the SpinButton properties to "0" then try to manipulate them in the VBA code.

What went wrong with the code is hard to explain. But basically, when I use the SpinButton to increment or decrement, it didn't quite do it correctly. If I fix the increment part of code, then the decrement part of the code goes wrong and vice versa.

An example: I have Min as "0.5%" in cell B7; Max as "4.5%" in cell B8; and Step (SmallChange) as "0.5%" in cell B9. The Input cell is in cell B10 where user can either type in a value within the defined range (enforced by data validation) or use the SpinButton to change the value.

To replicate the problem: I type in "1.0" in the Input cell and click on the left SpinButton and it would not allow me to go to the defined lower limit (which is 0.5%) in this case.

Here are my codes:

Private Sub SpinButton1_SpinUp()

Dim MyMax As Variant
Dim MyStep As Variant
Dim MyInput As Variant

MyMax = SpinButton1.Max + Range("B8").Value
MyStep = SpinButton1.SmallChange + Range("B9").Value

MyInput = Range("B10").Value

If (MyInput + MyStep) > MyMax Then
MsgBox ("Input not changed because the value would have exceeded the upper limit with this increment.")
Exit Sub
Else
MyInput = MyInput + MyStep
End If

Range("B10").Value = MyInput

End Sub


Private Sub SpinButton1_SpinDown()

Dim MyMin As Variant
Dim MyStep As Variant
Dim MyInput As Variant

MyMin = SpinButton1.Min + Range("B7").Value
MyStep = SpinButton1.SmallChange + Range("B9").Value

MyInput = Range("B10").Value

If (MyInput - MyStep) <= MyMin Then
MsgBox ("Input not changed because the value would have exceeded the lower limit with this decrement.")
Exit Sub
Else
MyInput = MyInput - MyStep
End If

Range("B10").Value = MyInput

End Sub


If someone is willing to look at it, I have a workbook that is all ready to email. Hopefully it's clear what I'm desperately trying to accomplish here and someone can really help me out!! Thank you!!!

 

[SeaDragon]

I made my own sheet with your format, and used your code, and it seems to be working fine.

(Sending you a PM with my e-mail so I can see your workbook.)

He doesn't need to set the spinbutton's properties, as he's using the spinup/spindown events and referencing the sheet.

If your input value actually is 0.999999% then it's definitely not going to let you get to 0.5%, because 0.499999999%<0.5%. Re-set that value to a multiple of 0.5% if you can and try that.

 

[detangler]

Hi sijpie,

First of all, thank you so very much for helping out!!! I really appreciate it!

In-between posts, I've considered banging my head really hard on the wall or engaging in other generally self-destructive and unhelpful behavior, but I thought better. And I've discovered something new.

I have both Excel 2003 and 2007 installed on my PC so I've decided to recreate the same setup in 2003.

Lo and behold, it works like a charm in 2003 but keeps craping out at the same point in 2007. So at least I have something to turn in after having spent 7 grueling days trying so hard to figure this out.

I do sincerely appreciate your responses. I will also look up VoG for his help.

Hi SeaDragon, I'd love to send you my workbook. But the problem seems to be happening in Excel 2007 only... it looks like you have 2003 so that won't replicate the problem that I have. Do you still want me to send it?

 

[SeaDragon]

Sure. I've got 2007 at home.