Macro to calculate columns relative to button location

Excel 2013,

I need a macro that I can assign to a button that will subsequently make columns adjacent to the button calculate when the button is clicked.

I have a very large document and don't want every equation on the sheet to calculate.

I will copy and paste this button to several locations so I'd like the the macro to be universal and therefore applicable to any button.

I've gone in a few circles and figure there's an expert who can cut to the chase.

Thank you in advance!!!

Hi there,

Take a look at the attached workbook and see if it does what you need.

The following code is called from each of the various "Calculate" buttons, and recalculates the cells in the column immediately to the left of the "Calculate" button which was clicked:

Option Explicit


Sub CalculateColumn()

    Const iOFFSET   As Integer = -1

    Dim rColumn     As Range
    Dim wks         As Worksheet
    Dim shp         As Shape

    Set wks = ActiveSheet

    On Error Resume Next
        Set shp = wks.Shapes(Application.Caller)
    On Error GoTo 0

    If Not shp Is Nothing Then

        Set rColumn = shp.TopLeftCell.Offset(0, iOFFSET).EntireColumn

        rColumn.Select      '   <<  Just for demonstration - delete from final version
        rColumn.Calculate

    End If

End Sub

Hope this helps - please let me know how you get on.

Regards,

Greg M

Thank you for the response! Last night I did a little tinkering (I know nothing John Snow) and I came up with the following that allows me to accomplish the same task you've accomplished, but for a range (i.e. multiple columns).

Formula:

Sub VERSION_REFRESH()

Dim r As Range

Set r = ActiveSheet.Buttons(Application.Caller).TopLeftCell

Range(Cells(r.Row, r.Column), Cells(r.Row + 5000, r.Column + 10)).Calculate



End Sub

Mind you, it's a little less elegant b/c it requires I define the depth (not just automatically calculating the whole column) but it works.

Now the problem I'm having with either macro is that it must be run several times to capture iterative calculations. In my columns I have simple calculations and then multiple cells summing the results of those calculations in the same column. The macro works on the first simple calcs, but must be run several more times to make the summation calculations run. Any idea how to address this? Any thoughts on the macro I've inserted? Thank you for your time and expertise!