Error Checking in Excel
Thanks Thanks:  0
Likes Likes:  0
Page 1 of 2 12 LastLast
Results 1 to 10 of 11

Thread: STILL NOT THERE - Spin Decimal - NOT INTERGERS

  1. #1
    New Member
    Join Date
    May 2002
    Posts
    5
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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 ]

  2. #2
    MrExcel MVP lenze's Avatar
    Join Date
    Feb 2002
    Location
    Helena, MT
    Posts
    13,690
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    You can use code for this

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

  3. #3
    MrExcel MVP
    Join Date
    Feb 2002
    Location
    Denver, CO
    Posts
    1,743
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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.

  4. #4
    MrExcel MVP lenze's Avatar
    Join Date
    Feb 2002
    Location
    Helena, MT
    Posts
    13,690
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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

  5. #5
    New Member
    Join Date
    May 2002
    Posts
    5
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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 ]

  6. #6
    Board Regular
    Join Date
    Mar 2002
    Location
    Cincinnati, Ohio, USA
    Posts
    6,824
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    This works fine for me...



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



    Tom

  7. #7
    New Member
    Join Date
    May 2002
    Posts
    5
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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

  8. #8
    Board Regular
    Join Date
    Mar 2002
    Location
    Cincinnati, Ohio, USA
    Posts
    6,824
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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 ]

  9. #9
    New Member
    Join Date
    May 2002
    Posts
    5
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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 ]

  10. #10
    Board Regular
    Join Date
    Mar 2002
    Location
    Cincinnati, Ohio, USA
    Posts
    6,824
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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

Some videos you may like

User Tag List

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •