STILL NOT THERE - Spin Decimal - NOT INTERGERS

[tanyagi]

Hi,

Can someone HELP!!! We're trying to get a spin button to increase a cell by 0.01 however it will only allow an interger eg 1, 2 etc. How can we fix this so we can either have a figure override the spin button (or use it) and get it to create .00 values. Fixed decimals seem to work but then mess every other value up..... HELP

[ This Message was edited by: tanyagi on 2002-05-05 09:10 ]

[lenze]

You can use code for this

Sub Spinner1_Change()
Range("YourCell").Value = ActiveSheet.Spinners("Spinner 1").Value * 0.01
End Sub

[IML]

I'm sure Lenze's answer is the superior one. But if you (like me) wouldn't know what to do with that code, why not tie the spinner to a hidden cell, say a1, and then use =a1/100 in your visable cell? Just a band-aid, but it may work for you.

[lenze]

Actually, it depends on what type of Spinner you are using. If from the forms toolbar, place the code in a regular module and assign the macro to the spinner. If from the Controls Toolbar, right click and choose View Code to insert it in the SpinButton Module

[tanyagi]

HITTING PROBLEMS

Basically I need to make sure people either use the spinner which looks up a formula as suggested by IML. But can't think how to stop someone being able to input there own value.


I've tried lenze formula (Spinner from control toolbar - not forms)

Trying to spin B50 using

Private Sub Alpha2_Change()
Range("B50").Value = ActiveSheet.Spinners("Alpha2").Value * 0.01
End Sub

But this creates the Run time error 1004, that says unable to get the spinner property of the worksheet class. I might have made a stupid error, any ideas please??


[ This Message was edited by: Tanyagi on 2002-05-04 07:33 ]

[ This Message was edited by: tanyagi on 2002-05-04 07:47 ]

[Tom Schreiner]

This works fine for me...

Private Sub Alpha2_Change()

Range("B50").Value = Alpha2.Value * 0.01

End Sub

Tom

[tanyagi]

Sooooooooooo Close,

Tom your formula works great until I try and use the spinner to create a value above 50. so 51 etc makes it go back to 1. Also it won't let me start on 0, has to be 0.01 - not such a problem, can put a comment in. The max value is set on 100 and min in 1 - won't work if min 0.

Any ideas?? it seems so close.


Not sure if any of these could be messing it up
Locked True. Autoload False. Enabled True.
Placement 2. Delay 100

[Tom Schreiner]

I'm kinda guessing at your range of values?
0 to 100
Counting in One-Hundedths .01

Use the following code:

Private Sub Alpha2_SpinDown()

If Range("B50").Value = 0 Then Exit Sub

Range("B50").Value = Range("B50").Value - 0.01

End Sub



Private Sub Alpha2_SpinUp()

If Range("B50").Value = 100 Then Exit Sub

Range("B50").Value = Range("B50").Value + 0.01

End Sub

OR

Option Explicit

Dim Num As Range



Private Sub Alpha2_Change()

Set Num = Range("B50")

End Sub



 

Private Sub Alpha2_SpinDown()

If Num = 0 Then Exit Sub

Num = Num - 0.01

End Sub



Private Sub Alpha2_SpinUp()

If Num = 100 Then Exit Sub

Num = Num + 0.01

End Sub

Tom


[ This Message was edited by: TsTom on 2002-05-05 05:27 ]

[tanyagi]

Getting closer still, but no jackpot and not sure about you but my head is starting to hurt, even though your doing the hard code part.

Couldn't make the second code work but the first now goes down to 0, but will not let the spinner create values above 50, without reseting the count to 0.01. It is VERY STRANGE.

I even moved it to cell 100 incase the fact it was in B 50 was causing the prob, no joy.

Code as stand at the mo is...


Private Sub Alpha3_Change()
Range("B100").Value = Alpha3.Value * 0.01
End Sub

Private Sub Alpha3_SpinDown()
If Range("B100").Value = 0 Then Exit Sub
Range("B100").Value = Range("B100").Value - 0.01
End Sub

Private Sub Alpha3_SpinUp()
If Range("B100").Value = 100 Then Exit Sub
Range("B100").Value = Range("B100").Value + 0.01
End Sub



Tanya.



[ This Message was edited by: tanyagi on 2002-05-05 07:34 ]

[Tom Schreiner]

Delete this and double-chech my previous reply...
We are not going to use the value of your spin button at all....

Private Sub Alpha3_Change()
Range("B100").Value = Alpha3.Value * 0.01
End Sub

Delete it and your code will work.

Tom