PDA

View Full Version : Button updates cells with Spin Button



David1976
11-06-2016, 12:20 AM
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
11-06-2016, 04:04 AM
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
11-06-2016, 04:13 PM
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
11-07-2016, 03:44 AM
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]