Cell linked to spinbutton resetting!

[DaddyWeg]

Probably an easy one for you pros, but it's got me confused!

I have a spinbutton (1) linked to cell "g16" which has an initial value of 60 and is set to scroll up to 92. I have another spinbutton (2) linked to cell "g25" which simply scrolls between 0 and 8.5. If I use spinbutton1 to change "g16" to 70 (or any value), as soon as I use spinbutton2 "g16" changes back to the initial value of 60. How can I prevent this from happening? (Both spinbuttons have code written to control their ranges and values)

[bydesign]

YO DaddyWeg

Sounds like some code is crossed somewhere... How about you post the code for those spinbutton procedures...

[Yogi Anand]

Hi DaddyWeg:

I installed the two SpinButton controls in a woksheet, assigned them the values as you posted, linked those to cells G16 and G25 -- and I could not reproduce the error you are experiencing.

How about if you post the code you are using and describe where the controls have been installed -- so the viewers can see what you are working with, and where the problem might lie.

[DaddyWeg]

Spinbutton3 is linked to "g16"
Spinbutton5 is linked to "g27"
Both reset to the "value " assigned in the code whenever spinbutton4 or 6 are used.

Here is everything I have going on in this worksheet!



Private Sub ComboBox2_Change()

Range("G14") = Val(ComboBox2.Value)

End Sub

Private Sub ComboBox3_Change()

Range("G25") = Val(ComboBox3.Value)

End Sub

Private Sub CommandButton1_Click()
clearform
End Sub

Private Sub SpinButton4_SpinDown()

If Range("G19").Value = 0 Then Exit Sub
Range("G19").Value = Range("G19").Value - 0.1



End Sub



Private Sub SpinButton4_SpinUp()

If Range("G19").Value = 8.5 Then Exit Sub
Range("G19").Value = Range("G19").Value + 0.1



End Sub



Private Sub SpinButton6_SpinDown()

If Range("G30").Value = 0 Then Exit Sub
Range("G30").Value = Range("G30").Value - 0.1


End Sub



Private Sub SpinButton6_SpinUp()

If Range("G30").Value = 8.5 Then Exit Sub
Range("G30").Value = Range("G30").Value + 0.1

End Sub

Private Sub Worksheet_Change(ByVal Target As Range)

If Range("G14").Value = 75 Then
SpinButton3.Min = 25
SpinButton3.Max = 57
SpinButton3.Value = 25
ElseIf Range("G14").Value = 125 Then
SpinButton3.Min = 60
SpinButton3.Max = 92
SpinButton3.Value = 60
ElseIf Range("G14").Value = 0 Then
SpinButton3.Min = 0
SpinButton3.Max = 0
SpinButton3.Value = 0
End If

If Range("G25").Value = 60 Then
SpinButton5.Min = 0
SpinButton5.Max = 32
SpinButton5.Value = 0
ElseIf Range("G25").Value = 0 Then
SpinButton5.Min = 0
SpinButton5.Max = 100
SpinButton5.Value = 0
End If

End Sub





Thanks for the help guys!

[DaddyWeg]

bump

[Yogi Anand]

Hi DaddyWeg:

I am sorry, I can not get a clear picture of what you are trying to do. Where are the SpinButton Controls? If they are on UserForm1, in the code for the Worksheet_Change event, in working with SpinButton Controls, I had to use the associated UserForm, such as ...

With UserForm1
     .SpinButton3 .....
     .SpinButton3 .....
End With

I also did not follow what you are trying to do with the CommandButton1 code ...
ClearForm

I am sorry, I could not be of more help.

[DaddyWeg]

Hey Yogi!

Thanks for the response! Sorry I'm not being clear enough. Maybe this will help. I started a new worksheet. Created a spinbutton and linked it to a cell. I pasted the following as my code for sheet 1. The spinbutton works the way the code says by setting the value correctly and limiting it's upper and lower values. When I select any other cell, enter a number and hit enter, the cell linked to my spinbutton changes back to SpinButton1.Value. How do I get it to stay at whatever I spun it up or down to.

Private Sub Worksheet_Change(ByVal Target As Range) 

If Range("G14").Value = 75 Then 
SpinButton1.Min = 25 
SpinButton1.Max = 57 
SpinButton1.Value = 25 
ElseIf Range("G14").Value = 125 Then 
SpinButton1.Min = 60 
SpinButton1.Max = 92 
SpinButton1.Value = 60 
ElseIf Range("G14").Value = 0 Then 
SpinButton1.Min = 0 
SpinButton1.Max = 0 
SpinButton1.Value = 0 
End If

[parry]

Hi DW, Im coming into this thread a bit late but what control did you use when you placed it on the sheet. A control from the Forms toolbar works differently from a control from the Visual Basic toolbar.

[DaddyWeg]

Parry,

The spinbutton is from the visual basic toolbar.

[parry]

Hi, theres a bit going on here and would take some time for me to recreate it. Its a pity you cant upload files on MrExcel as it would be much easier to understand in seeing the actual sheet.

If the problem is only occuring when you change a cell then it must be your Worksheet_Change event thats causing it.

There are 3 conditions where G14 is either 75,125 or 0. What happens if G14 is neither of these values?

The Worksheet_Change event runs whenever there is a change to any cell in the sheet, so maybe you want to amend this with an additional condition test so it only changes the spin min/max if there is a change to cell G14? The following amendment should fix that...

Private Sub Worksheet_Change(ByVal Target As Range)
Dim Rng As Range
Set Rng = Application.Intersect(Range(Target.Address), Range("G14"))

If Not Rng Is Nothing Then 'do this only if cell changed was in G14
    If Range("G14").Value = 75 Then
        SpinButton1.Min = 25
        SpinButton1.Max = 57
        SpinButton1.Value = 25
    ElseIf Range("G14").Value = 125 Then
        SpinButton1.Min = 60
        SpinButton1.Max = 92
        SpinButton1.Value = 60
    ElseIf Range("G14").Value = 0 Then
        SpinButton1.Min = 0
        SpinButton1.Max = 0
        SpinButton1.Value = 0
    End If
End If
End If

End Sub

Has this helped or you still have a problem?