Running a macro in excel

Question

I don't think this is technically a macro but I don't know what else to call it:

Users want to print individual sections from a report on a sheet. These sections are just named ranges.

Some points:

• The file is an xlt file.
• It is used as a template to generate an xls file.
• I am using Excel 2007, but the users will run a mixture of 2007 and 2003.
• When the file loads into excel it asks if I want to enable all macros and I confirm.
• The function it is calling is public

I created a series of buttons down the edge of the sheet:

        ' in a loop    
        With ActiveSheet.Buttons.Add(rngCurrent.Left + 2, rngCurrent.Top + 1, rngCurrent.Width - 2, rngCurrent.Height - 1)
            .Caption = "Print"
            .OnAction = "PrintRange"
            .Font.Size = 7
            .Name = CStr(oSite.SiteID)
        End With

However when I click on the button it gives "Cannot run the macro 'filename.xls!PrintRange".

The PrintRange function is in the sheet shtPage while the loop is in a module called modPage.

Why can't I call the function I need and how can I make it work?

Answer 1

Even with your PrintRange sub declared as Public you still need to refer to it more specifically so that Excel can find it, because you have put it in the code section of a worksheet instead of a module.

Change:

.OnAction = "PrintRange"

to

.OnAction = "shtPage.PrintRange"

and it will work just fine.

A caution: If you have renamed your worksheet on the page tab to 'shtPage', but in the VBA project explorer, Excel still refers to the worksheet as 'Sheet1(shtPage)', you will need to use the 'Sheet1' name that Excel recognizes, not the 'shtPage' name that appears on the page tab.

Answer 2

Did you make the PrintRange macro public? It needs to be defined as follows:

Public Sub PrintRange
    '// ...'
End Sub