Button updates cells with Spin Button

[David1976]

This is my first time programming in VBA using Excel. I am trying to use a spin button to increase or decrease values to cells individually in the same row. The sub btnNext_Click in meant to go down one cell in the row, after dropping down to the next cell the spin button increases or deceases the values in that cell. I am stuck with how to get spin button and the button to work together.

Private Sub btnNext_Click()
ActiveCell.Offset(0, 0).Select
End Sub

'Spin button
Private Sub M1_Change()
M1.Max = 100
M1.Min = 0
M1.SmallChange = 1
Range("B2").Value = M1.Value

[MickG]

Try this:-

Option Explicit
Dim R As Range
Private Sub BtnNext_Click()
If R Is Nothing Then
    Set R = [b2]
Else
    Set R = R.Offset(1) 'NB:- Use "R.Offset(,1)"  If you want to go Horizontally
End If
End Sub
 'Spin button
Private Sub M1_Change()
  If R Is Nothing Then Set R = [b2]
    M1.Max = 100
    M1.Min = 0
    M1.SmallChange = 1
    R.Value = M1.Value
End Sub

[p45cal]

or just minimally changing your code:

Private Sub btnNext_Click()
    ActiveCell.Offset(1).Select
    M1.Value = ActiveCell.Value
End Sub

'Spin button
Private Sub M1_Change()
'you only need to run the next three lines once, or set these values manually in the properties of the spinbutton.
'M1.Max = 100
'M1.Min = 0
'M1.SmallChange = 1
ActiveCell.Value = M1.Value
End Sub

It will error if the cell you move onto with the button contains a number outside the range 0 to 100, or contains text.

[David1976]

To MickG and p45Cal,

Thank you for your speedy replies. At this stage in development I am going with p45Cal's approach.

'you only need to run the next three lines once, or set these values manually in the properties of the spinbutton.

yes p45Cal, the Min and Max values are set in properties window.

Private Sub M1_Change() 
    If R Is Nothing Then Set R = [b2] 
    M1.Max = 100 
    M1.Min = 0 
    M1.SmallChange = 1 
    R.Value = M1.Value 
End Sub

I haven't been able to understand this bit.

    If R Is Nothing Then Set R = [b2]