2 Numbers, 1 Spin Button

[Evalis]

Okay so the background is a bit of the following, I'm trying to edit 1 number by an incriment value of 1, while another number is also edited using a backwards calculation dependant upon the value of the first number being edited. The problem here is two-fold: first the number in the second column doesn't change unless I click 'run', and secondly, the number it keeps returning is wrong (I think it's just doing it the once).

The code I'm using is the following:

Sub Spinner13_Change()

End Sub

Private Sub SpinButton13_SpinDown()
    Dim incrimentVal As Integer
    
    With Range("D3")
        Select Case Range("D3").Value
            Case Is < 10
                incrimentVal = -1
            Case Is < 12
                incrimentVal = -2
            Case Is < 15
                incrimentVal = -3
            Case Is < 17
                incrimentVal = -4
            Case Is < 100
        End Select
        Range("M4") = Range("M4") + incrimentVal
    End With
End Sub
Private Sub SpinButton13_SpinUp()
    Dim incrimentVal As Integer
    
    With Range("D3")
        Select Case Range("D3").Value
            Case Is <= 10
                incrimentVal = -1
            Case Is <= 12
                incrimentVal = -2
            Case Is <= 15
                incrimentVal = -3
            Case Is <= 17
                incrimentVal = -4
            Case Is <= 100
        End Select
        Range("M4") = Range("M4") + incrimentVal
    End With
End Sub

The first number, and the number the spinner is currently tied to is "D3", the second number that is supposed to change based on that value is M4. I also want to eventually tie a restriction that M4 cannot be less than zero for changes to occur in D3, but that might take a bit longer.

The second code I have is trying to edit the minimum value of the spinbutton when the user clicks a checkbox (hence declaring they are done with this portion of the edit). The code I'm using is:

Sub CheckBox12_Click()
         Spinbutton13.Min = Range("D3")
End Sub

But it keeps returning 424 error. Any help with either of these things would be appreciated. Thanks. By the way.. if there is a list of functions that can be run in excel using VB that would be extremely helpful ^^

[snb]

A spinbutton is meant for situations in which you want to increase/decrease a number with a constant amount.
If you want something else you'd better use a commandbutton:

Private Sub increaseButton_Click()
   [M4]=[M4] + (([D3]-10)\2) 
End Sub

Private Sub decreaseButton_Click()
   [M4]=[M4] - (([D3]-10)\2) 
End Sub

[Ryan Murtagh]

Hi Evalis,

Try this...

Please note that this is untested

Private Sub SpinButton13_SpinDown()
    Dim incrimentVal As Integer
    Dim iTest As Integer
        iTest = Range("M4")
        If iTest = 0 Then
            MsgBox "The Value in Cell M4 Cannot Be Less Than 0", vbCritical
            Exit Sub
        End If
        With Range("D3")
            Select Case Range("D3").Value
                Case Is < 10
                    incrimentVal = -1
                Case Is < 12
                    incrimentVal = -2
                Case Is < 15
                    incrimentVal = -3
                Case Is < 17
                    incrimentVal = -4
                Case Is < 100
            End Select
            Range("M4") = Range("M4") + incrimentVal
        End With
End Sub


Private Sub SpinButton13_SpinUp()
    Dim incrimentVal As Integer
        With Range("D3")
            Select Case Range("D3").Value
                Case Is <= 10
                    incrimentVal = 1
                Case Is <= 12
                    incrimentVal = 2
                Case Is <= 15
                    incrimentVal = 3
                Case Is <= 17
                    incrimentVal = 4
                Case Is <= 100
            End Select
            Range("M4") = Range("M4") + incrimentVal
        End With
End Sub

[Ryan Murtagh]

...and this

Private Sub CheckBox12_Click()
    With Me
        Select Case .CheckBox12
            Case True
            .SpinButton13.Min = .Range("D3")
            ' below is a msgbox to validate the min.  you can delete it
            MsgBox .SpinButton13.Min
        End Select
    End With
End Sub

[Evalis]

The above code possesses the same problem as the original posting, in that the data does not update when I press the spin button. As far as using a command button is concerned, I fail to see the benefit, as I would essentially still have to create two command buttons that both perform 2 functions (which would be the same as using 'spinup' and 'spindown'), though I might give this a try if it will solve the fact that the numbers don't update.

[Ryan Murtagh]

It's ok...the code I gave you doesn't do what you want anyway. I did test the code below and it worked for me, with the exception of the fact that you didn't specify what you wanted to happen when the value in D3 is greater than 17 but less than 100.

Now, as for it not working when you press the spin button. Make sure that this code is placed in the class module for the worksheet in which the spin button is embedded and that you are not in design mode. These events will not be triggered if the code is placed in a standard code module

Updated Code:

Private Sub SpinButton13_SpinDown()
    Dim incrimentVal As Integer
    Dim iTest As Integer
        iTest = Range("M4")
        If iTest = 0 Then
            MsgBox "The Value in Cell M4 Cannot Be Less Than 0", vbCritical
            Exit Sub
        End If
        With Range("D3")
            Select Case Range("D3").Value
                Case Is < 10
                    incrimentVal = -1
                Case Is > 10 < 12
                    incrimentVal = -2
                Case Is > 12 < 15
                    incrimentVal = -3
                Case Is > 15 < 17
                    incrimentVal = -4
                Case Is < 100
            End Select
            Range("M4") = Range("M4") + incrimentVal
        End With
End Sub
Private Sub SpinButton13_SpinUp()
    Dim incrimentVal As Integer
        With Range("D3")
            Select Case Range("D3").Value
                Case Is <= 10
                    incrimentVal = 1
                Case Is > 10 <= 12
                    incrimentVal = 2
                Case Is > 12 <= 15
                    incrimentVal = 3
                Case Is > 15 <= 17
                    incrimentVal = 4
                Case Is <= 100
            End Select
            Range("M4") = Range("M4") + incrimentVal
        End With
End Sub

[Evalis]

I must be retarded. What do you mean by embedded?

[Ryan Murtagh]

Your not retarded. What I should have said was that any time you place a control (like a spinner or a textbox) onto a worksheet, the macros that you use to drive that control have to live on that worksheet's class module. Each sheet has it's own class module, and you can access the class module by going into VBA (hit Alt+F11) and double clicking on the sheet's name in the Project window, or by just double clicking on the control (your spinner).

If you want to attach an example workbook of your project, I'd be happy to take a look at it for you.

[shg]

Ryan,

This syntax is not valid, and if it were, it would have gaps at the whole numbers:

           Select Case Range("D3").Value
                Case Is < 10
                    incrimentVal = -1
                Case Is > 10 < 12
                    incrimentVal = -2
                Case Is > 12 < 15
                    incrimentVal = -3
                Case Is > 15 < 17
                    incrimentVal = -4
                Case Is < 100
            End Select

[Evalis]

I still don't quite understand. The macro's exist on both the module and on the sheet. Maybe there's something I'm missing though. The copy of the worksheet is here. The stats sheet is the one I'm attempting to manipulate. Thanks again for your patience ^^

[Ryan Murtagh]

shg you are quite right. I'm not sure why I didn't get a syntax error when I ran it, but I definitely should have caught the fact that the cell was not updating correctly.

Nevertheless...Evalis, I downloaded your workbook and the reason that your macros aren't working is because you are using the wrong kind of spinner. There are two types of controls that you can put onto a worksheet: Forms Controls and ActiveX controls. You can go into Excel's help documentation for a pretty good explanation of the difference between the two.

I think there may be a non-VBA solution here. I'm going to take your workbook home and play with it for a bit, free of distraction, so that I don't post any more junk solutions for you. If someone comes up with a suitable fix for you in the meantime, then good on them. I will still post a solid solution for you to ease my karma after leading you in the wrong direction earlier.

[Evalis]

No this works now, thanks! I'm not sure about the me. command but that may have given me an error simply because it also was a form control. I'm still working on this, and have a lot to go, but the spinners are working now =D

[Ryan Murtagh]

Best of luck with your project!