Apply vba function to cell directly above button-CodePudding

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.

Range("b7").Value = Range("b7").Value   500

CodePudding user response：

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.

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).