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!!!

 

[sijpie]

Why are you adding the current min and max to the value you have set?

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

and

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

 

[detangler]

I'm adding those values because they are supposed to be user inputs. Please see below (the SpinButton is located on cell B11 but it does not show with this converter):

Excel Workbook
	A	B
1	SpinButton Problem
2
3	Basic Instructions:
4	1. User can define the lower limit (min), upper limit (max), the incremental value (step).
5	2. Then they can either use the spinbutton to change the value in the input cell (B10) or type one in directly.
6
7	Min	0.50%
8	Max	4.50%
9	Step	0.50%
10	Input	1.00%
11
12
Spinbutton

 

[sijpie]

Yes, but every time you use the spinbutton the value gets increased. Or am I barking completey up the wrong tree?

What happens if you just set them to min and max and step.? Give it a try.

 

[detangler]

What happens if you just set them to min and max and step.? Give it a try.

OK, I see what you're saying, I now have it changed to this:


Private Sub SpinButton1_SpinUp()

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

MyMax = Range("B8").Value
MyStep = 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 = Range("B7").Value
MyStep = 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


I also changed this line:

If (MyInput - MyStep) <= MyMin Then MsgBox....

to: If (MyInput - MyStep) < MyMin Then MsgBox....


But it still didn't solve the weird problem I have. Let me be specific.

TO REPLICATE THE PROBLEM: Enter "1.0" in the Input box (B10) then click on the lower spinbutton. For some reasons, it does not want to go all the way to the defined lower limit (0.5%). At some point, I was able to see in the formula bar that the "1.00%" in cell B10 is actually "0.9999999999....%" and that would be the problem but I (1) don't how to fix that and (2) can't replicate that problem now either.

 

[sijpie]

Where does the code initialise the spin button, ie where /when does the spinbutton get the information that the step is this much, and the max and min are that much? (I never use these controls in the sheet, I have them on userforms which are setup differently.)

 

[detangler]

My previous post contains all of the codes I have for the SpinButton.

I thought these lines initialize the SpinButton:

MyMax = Range("B8").Value
MyStep = Range("B9").Value
MyInput = Range("B10").Value

 

[sijpie]

No, they just set some variable you have called MyMax, etc. The spinbutton doesn't do anything with them, you need to set the spinbutton parameters with

spinbutton1.Min = MyMin
spinbutton1.Max = MyMax
Spinbutton1.Step = MyStep

once you have set your MyMin, MyMax and MyStep.

 

[detangler]

I did what you said but it still have the same problem. ;(

 

[sijpie]

I suggest to look up the user VoG and write him a private message with a link to this thread. see if he can shed some light.