Working with xla add-in and ActiveSheet.PageSetup

[NightZEN]

I am working on an Add-in that will (among other things)set the print area and other pageSetup options of the active calling worksheet. When I run the code I get an "Object Variable ro With Block Variable not set" error which is being caused by, I think, the add-in not referencing the Active Sheet of the Open worksheet but referencing the Add-in itself. How can I fix this so that the calling worksheet gets it's PageSetup options set correctly?

Thanks

Private Sub viewing_printing(str_lastrow As String)
Dim str_range As String

    'Freeze Panes
    Range("A5").Select
    ActiveWindow.FreezePanes = True
    
    'Setup printing options
    With ActiveSheet.PageSetup
        .PrintArea = "$A$1:$M$" & str_lastrow
        .LeftHeader = ""
        .CenterHeader = ""
        .RightHeader = ""
        .LeftFooter = ""
        .CenterFooter = ""
        .RightFooter = ""
        .LeftMargin = Application.InchesToPoints(0.25)
        .RightMargin = Application.InchesToPoints(0.25)
        .TopMargin = Application.InchesToPoints(0.5)
        .BottomMargin = Application.InchesToPoints(0.75)
        .HeaderMargin = Application.InchesToPoints(0.5)
        .FooterMargin = Application.InchesToPoints(0.5)
        .PrintHeadings = False
        .PrintGridlines = False
        .PrintComments = xlPrintNoComments
        .PrintQuality = 300
        .CenterHorizontally = False
        .CenterVertically = False
        .Orientation = xlPortrait
        .Draft = False
        .PaperSize = xlPaperLetter
        .FirstPageNumber = xlAutomatic
        .Order = xlDownThenOver
        .BlackAndWhite = False
        .Zoom = False
        .FitToPagesWide = 1
        .FitToPagesTall = 100
        .PrintErrors = xlPrintErrorsDisplayed
    End With
End Sub

 

[rmikesmith]

You are on the right track. You need to qualify ActiveSheet like this:

With [COLOR=red]ActiveWorkbook.[/color]ActiveSheet.PageSetup
  [COLOR=green]' rest of your existing code[/color]

Regards,
Mike

 

[NightZEN]

Perfect! That makes sence. Thanks!

 

[firefytr]

If it's an addin, you should add a check to all your routines ...


If ActiveWorkbook Is nothing then Exit Sub


This will prevent erroring out when no workbook is open.

HTH

Regards,
Zack Barresse