Excel: Increment Counter Button of Relative Cell

Question

In Excel, I want to have a button to click that will increment the value of a cell.

However, rather than increment an absolutely referenced cell, I want to increment the value of a cell relative to the button location.

This is the type of code I am working with so far, however the key question of finding the event object location is proving difficult...

Sub increment_value()
this.Offset(0, -2).Value = this.Offset(0, -2).Value + 1
End Sub

This is what I want it to do (example only -> button in D5 increment B5 by 1. Relative is the key though!):

Sub thought: Is there an Excel equivalent to the Javascript keyword: this?

Answer 1

If your button is an ActiveX one you could add the following code to it:

Private Sub CommandButton1_Click()
    Me.CommandButton1.TopLeftCell.Offset(0, -2) = _
            Me.CommandButton1.TopLeftCell.Offset(0, -2) + 1
End Sub

Where:

Me. refers to sheet where button is located.

.CommandButton1. refers to itself

.TopLeftCell is property which returns Range object of the cell where is located TopLeft corner of your button. So, be precise when you locate it in the sheet.

Tried and tested!