Buttons that selectively add or subtract 1 from cells based on selections from a list.

[moosetracks]

I'm trying to create a user friendly worksheet where an employee can be selected from a list and then values for the selected employee can be added or subtracted by one integer using buttons. In other words, a spin button that selectively increases or decreases the value of the cell of the selected employee. Then when you select a different employee from the list, the cell that is influenced is changed to that employee selected.

Regular form controls don't seem to allow for any dynamic control. I used a long string of if statements in VBA but it was way too long and didn't work smoothly. I also had problems with the spin button keeping its prior value rather than adding/subtracting from the value of the next cell I wanted to update.

I hope this explanation is clear. Let me know if you need clarification.

 

[moosetracks]

Clarification: so this is for quality tracking. We track errors of employees and units reviewed. I need buttons to increase the numbers of errors and units reviewed for the selected employee.

 

[moosetracks]

Anyone? I know there's someone here who can help me.

 

[mikerickson]

If you create a SpinButton from the Forms menu and attach this macro to that button, then column B of the select row will be incremented or decrimented depending on how you change the Spin Button.

Sub Spinner1_Change()
    Dim ChangeCell As Range
    
    Set ChangeCell = ActiveCell.EntireRow.Cells(1, 2)
    
    With ActiveSheet.Shapes(Application.Caller)
        MsgBox .ControlFormat.Value
        If .ControlFormat.Value > 2 Then
            ChangeCell.Value = Val(CStr(ChangeCell.Value)) + 1
        Else
            ChangeCell.Value = Val(CStr(ChangeCell.Value)) - 1
        End If
        .ControlFormat.Value = 2
    End With
End Sub

A completely different approach would be to put this in the sheet's code module of the sheet in question.

This will automatically set the cell in column C of the selected row to be the linked cell for Spinner 2.

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    Dim ChangeCell As Range
    
    Set ChangeCell = Target.Cells(1, 1).EntireRow.Cells(1, 3)
    
    With ActiveSheet.Shapes("Spinner 2").ControlFormat
        .LinkedCell = vbNullString
        .Value = ChangeCell.Value
        .LinkedCell = ChangeCell.Address
    End With
End Sub