Excel – Duplicating Excel buttons with relative reference

microsoft excelvba

I am building an inventory workbook. On each row is information related to a part number including count. I wish to add a couple of buttons:

  • One that will ADD one to the inventory count
  • One that will SUBTRACT one from the inventory count

As a result, there will be two buttons controlling the inventory count on the same row. I would like to be able to duplicate these buttons retaining relative reference in the workbook. For example, the button copied/duplicated to Row 3 will control the inventory count for Row 3.

There will be 100's of rows and changing the control manually is not practical. Ideally, the solution will create 1000 rows with the command buttons created.

Best Answer

Here's an idea. How about creating only 2 buttons (instead of hundreds) and have them move to follow the selection?

EDIT: After some discussion and clarification, OP Phil asked for the inventory update buttons to appear adjacent to the selected cell when the user clicks in column F, and be hidden otherwise.

The video below shows the operation of the tagalong buttons:

enter image description here

The Worksheet has a simple macro that displays and moves the buttons:

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    If Target.Column = 6 Then
        ActiveSheet.Shapes("Picture 1").Visible = True
        ActiveSheet.Shapes("Picture 2").Visible = True
        With ActiveSheet.Shapes("Picture 1")
            .Left = Target.Offset(, 2).Left
            .Top = Target.Offset(0).Top
        End With
        With ActiveSheet.Shapes("Picture 2")
            .Left = Target.Offset(, 3).Left
            .Top = Target.Offset(0).Top
        End With
    Else
        ActiveSheet.Shapes("Picture 1").Visible = False
        ActiveSheet.Shapes("Picture 2").Visible = False
    End If    
End Sub

I couldn't change the color of regular Excel command buttons, so I inserted pictures, and assigned macros to the picture objects. That's why they're referenced as Shapes("Picture 1") in the code above. If you use normal Excel command buttons, the reference would change to the name of the button.

The buttons themselves have even simpler code:

Sub Button1_Click()
    ActiveCell.Value = ActiveCell.Value + 1
End Sub

Sub Button2_Click()
    ActiveCell.Value = ActiveCell.Value - 1
End Sub

I hope this helps, and good luck.

Related Question