Apply vba function to cell directly above button

user_14012677 0
Viewed 70

In my vba code the goal is to add 500 to the cell above the button. Right now the button in question is in cell "c7". I want the cell directly above it which is cell be "b7" to add 500 every time the button is pressed. I would like to copy the button to add for cells throughout the worksheet. So the code has to be something like cell.above +500 or something like that. I added my current code below.

enter image description here

Range("b7").Value = Range("b7").Value + 500
button copy vba excel
1 Answers

If you're using a form button, it's like this:

Sub FormButtonClick()
With Shapes(Application.Caller).TopLeftCell.Offset(-1, 0)
    .Value = IIF(Not IsNumeric(.Value),500,.Value + 500)
End With
End Sub

You can add the IIF to see if it's blank and "initialize" it at 500 (or whatever) if that's you're desire.

Demo

Make sure you're looking at the right sheet - this was added in the Sheet1 object code module.

Otherwise, you may need to specify a sheet.

Also, when you're copying them, you need to rename them (by default they are copied with the same name).

user_12748200
Related