Spin Button / Macro

**nogoodnames** · 03-09-2010, 05:20 AM

Hello all, I've been using the forum for awhile for answers, but just registered as I can't seem to find a solution to my problem.

I have a spreadsheet which has user defined input, which works just fine as it is. However, counter to the 'if it ain't broke, don't fix it' idealogy, I want to fix it. I want to include either a spin button or a macro button to increase the cell value by 1. Easy enough to do with a spin button, but there's a total of 84 different cells which need it... and there will be roughly 50 separate spreadsheets (1 for each user). I can create a spin button and link it to a cell and it works perfectly, or I can write a macro, and assign it to a button, and it works perfectly.

But I'm trying to avoid having to do this 84 times for every user. Is there a way to have the spin button automatically linked to the cell it's in? Or, alternatively, is there a way to write a macro so that it can select the cell in which the button is placed, then increase/decrease the value by 1. At least then it'd be one macro, and I could copy/paste a bunch of them.

Thanks in advance!

**dominicb** · 03-09-2010, 06:33 AM

Good morning nogoodnames

Your first choice is achievable, but not easliy. Your alternative choice is very easy - which is why I'm going to go with that one

.

Draw a spin buttonusing your Control Toolbox toolbar. Make sure it is called SpinButton1.
The put the code below in your sheet module (right click the worksheet tab, click View Code, then paste the code into the empty window that opens).

Private Sub SpinButton1_SpinDown()
SpinButton1.Value = ActiveCell.Value
ActiveCell.Value = ActiveCell.Value - 1
End Sub

Private Sub SpinButton1_SpinUp()
SpinButton1.Value = ActiveCell.Value
ActiveCell.Value = ActiveCell.Value + 1
End Sub

HTH

DominicB

**Palmetto** · 03-09-2010, 06:52 AM

Just building a bit further on dominic's suggestion:
If you want the spin button to be visible and applied only to a specific range of cells then you can use this code. The spinner will be visible when the activecell is in the range(A2:A10) and hidden if not.

Option Explicit

Private Sub SpinButton1_SpinDown()
    With ActiveCell
        .Value = .Value - 1
    End With
End Sub

Private Sub SpinButton1_SpinUp()
    With ActiveCell
        .Value = .Value + 1
    End With
End Sub

Private Sub Worksheet_SelectionChange(ByVal Target As Range)

    If Not Intersect(Target, Range("A2:A10")) Is Nothing Then
        With Me
            .SpinButton1.Visible = True
            .SpinButton1.Left = Target.Offset(0, 1).Left
            .SpinButton1.Top = Target.Top
        End With
    Else
        Me.SpinButton1.Visible = False
    End If

End Sub