Button linked to counter VBA code

[sconchiti]

I'm a VBA newb, and although I've searched and searched, I can not find an answer to what seems like a simple question.

I have added buttons to several cells and clicking that button runs a counter macro. Clicking on the button in cell c5 runs the following macro which I found online:

Sub Macro1()
mycount = Range("c5") + 1
Range("c5") = mycount
End Sub

The value in c5 goes up by 1 as expected.

The problem is that when I insert a new row above row 5, the button moves down so it stays in the same cell, but when the button is pressed it adds 1 to the "new" c5. How do I get the macro to add the amount to the "old" cell which is now c6?

Thanks,
Dan

 

[mikerickson]

You could link all of your buttons to this code. Perhaps add an .Offset to the With line.

Sub increaseByOne()
    With ActiveSheet.Shapes(Application.Caller).TopLeftCell
        .Value = Val(CStr(.Value)) + 1
    End With
End Sub

 

[sconchiti]

Wow. That was a ridiculously fast answer. And it works! No .Offset needed.

I really appreciate the help. I am only beginning to understand how powerful macros and VBA can be. Thanks for the help. You saved me a ton of time.

Dan

 

[mikerickson]

You're welcome.