run macro from userform button

Question

Hello,

I wrote a macro and it runs when a shape is clicked (in sheet 1). But I want to also run it from a userform button, how do I call the macro in the module?

Get an overflow error.

Thanks

Answer 1

That is probably because the Named Ranges:

 Range("Form_Year_Range")

and

Range("Chart_range")

probably exist only on Sheet1.

You need to be very careful to specify ranges in a way that the macro can run on any sheet.

Answer 2

The button on the userform runs fine now, I renamed the module to something different than the name of the macro. Can I not name a module the same as a macro?

My last problem is that  I can only run the macro if I am in sheet 1, I get an error othrwise.

Answer 3

I have tried

Private Sub Update_Click()

Call Summary

End Sub

but I get

compile error: Expected variable or procedure, not module.

I wanted to add that the workbook has 4 sheets, the macro works fine when run from the shape in sheet 1, but if I draw another shape in any other sheet (and run it from there) and assign the same macro it does not work. A command button in the userform gives me the same results. I was wondering if something is missing in the macro as I am not specifying any worksheets?

Sub Summary()

  iYear = Year(Date)

  Dim M As Range

  Dim N As Range

  Dim T As Range

  Set M = Range("G6:G377").Offset(0, Application.Match(iYear, Range("Form_Year_Range"), 0))

  Set N = Range("D381").Offset(Application.Match(iYear, Range("Chart_range"), 0))

  Set T = Range("D382:D430")

    'Sums & Writes year totals to cells in "Data" sheet

  With N

        .Offset(0, 1).Value = Application.Sum(M.Offset(0, 0))                                

        .Offset(0, 2).Value = Application.Sum(M.Offset(0, 1))                                 

        .Offset(0, 3).Value = Application.Sum(M.Offset(0, 2))                             

        .Offset(0, 4).Value = Application.Max(M.Offset(0, 3))                                

        .Offset(0, 5).Value = Application.Convert(N.Offset(0, 4), "mi", "km")                 

        .Offset(0, 6).Value = Application.Max(M.Offset(0, 0))                                 

        .Offset(0, 7).Value = Application.Convert(N.Offset(0, 6), "mi", "km")                 

        .Offset(0, 8).Value = Application.Max(M.Offset(0, 4))                                 

        .Offset(0, 9).Value = Application.Convert(N.Offset(0, 8), "mi", "km")            

        .Offset(0, 10).Value = Application.Sum(M.Offset(0, 3)) / Application.CountIf(M.Offset(0, 0), ">0") 

        .Offset(0, 11).Value = Application.Convert(N.Offset(0, 10), "mi", "km")               '

        .Offset(0, 12).Value = Application.CountIf(M.Offset(0, 0), ">=100")                   

        .Offset(0, 13).Value = Application.CountIf(M.Offset(0, 1), ">=100") - N.Offset(0, 12) 

        .Offset(0, 14).Value = Application.CountIf(M.Offset(0, 0), ">0")                     

        .Offset(0, 15).Value = Application.Sum(M.Offset(0, 5))                               

  End With

End Sub

Thanks

Answer 4

Say the Shape has been asigned to run a macro called StandardMacro in a standard module.

Open the userform in VBA and right click the button.  Select view code and enter the following:

Private Sub CommandButton1_Click()

Call StandardMacro

End Sub