Print Macro Question

[SteveT57]

Hi I am using Excel 2007 & I have recorded a macro to format my spreadsheets for printing, however I would like it to pause & allow the user to input the print titles (as these can be different form spreadsheet to spreadsheet ). Any suggestion on how I can do this is appreciated my current macro is below

Sub Print()
'
' Print Macro
' Macro recorded 13/04/2006 by Townsend
'
'
With ActiveSheet.PageSetup
.PrintTitleRows = "$2:$4"
End With
ActiveSheet.PageSetup.PrintArea = ""
With ActiveSheet.PageSetup
.LeftHeader = ""
Rem:.CenterHeader = "&""Arial,Bold""&16&F"
.RightHeader = ""
.LeftFooter = ""
.CenterFooter = ""
.RightFooter = "&Z&F"
.LeftMargin = Application.InchesToPoints(0)
.RightMargin = Application.InchesToPoints(0)
.TopMargin = Application.InchesToPoints(0.8)
.BottomMargin = Application.InchesToPoints(0.275590551181102)
.HeaderMargin = Application.InchesToPoints(0.511811023622047)
.FooterMargin = Application.InchesToPoints(0)
.PrintHeadings = False
.PrintGridlines = True
.PrintComments = xlPrintNoComments
.PrintQuality = 600
.CenterHorizontally = True
.CenterVertically = False
.Orientation = xlLandscape
.Draft = False
.PaperSize = xlPaperA4
.FirstPageNumber = xlAutomatic
.Order = xlDownThenOver
.BlackAndWhite = False
.Zoom = False
.FitToPagesWide = 1
.FitToPagesTall = 20
.PrintErrors = xlPrintErrorsDisplayed
End With
ActiveWindow.SelectedSheets.PrintPreview
End Sub

 

[RSXchin]

something like

PrintName = inputbox("What do you want to call it?")

then use the variable "PrintName" somewhere.

 

[SteveT57]

I am a bit of a simpleton. I am not sure what you mean.

 

[RSXchin]

Try this (by the way, you can't call it "Print" because that is a reserved word)

Sub Print_it()
'
' Print Macro
' Macro recorded 13/04/2006 by Townsend
'
'
    With ActiveSheet.PageSetup
        .PrintTitleRows = "$2:$4"
    End With
    ActiveSheet.PageSetup.PrintArea = ""
    With ActiveSheet.PageSetup
        .LeftHeader = InputBox("What do you want to call it?")
        Rem:.CenterHeader = "&""Arial,Bold""&16&F"
        .RightHeader = ""
        .LeftFooter = ""
        .CenterFooter = ""
        .RightFooter = "&Z&F"
        .LeftMargin = Application.InchesToPoints(0)
        .RightMargin = Application.InchesToPoints(0)
        .TopMargin = Application.InchesToPoints(0.8)
        .BottomMargin = Application.InchesToPoints(0.275590551181102)
        .HeaderMargin = Application.InchesToPoints(0.511811023622047)
        .FooterMargin = Application.InchesToPoints(0)
        .PrintHeadings = False
        .PrintGridlines = True
        .PrintComments = xlPrintNoComments
        .PrintQuality = 600
        .CenterHorizontally = True
        .CenterVertically = False
        .Orientation = xlLandscape
        .Draft = False
        .PaperSize = xlPaperA4
        .FirstPageNumber = xlAutomatic
        .Order = xlDownThenOver
        .BlackAndWhite = False
        .Zoom = False
        .FitToPagesWide = 1
        .FitToPagesTall = 20
        .PrintErrors = xlPrintErrorsDisplayed
    End With
    ActiveWindow.SelectedSheets.PrintPreview
End Sub

 

[SteveT57]

Thanks RSXCHIN,
However I don't think I explained my self properly in the begining it is the row titles I want to pause and have user inpout for.

With ActiveSheet.PageSetup
.PrintTitleRows = "$2:$4" - this is the section I would like the user to input

 

[SteveT57]

RSXCHIN,
I found if I insert this piece of Code it will display the page layout box
Application.Dialogs(xlDialogPageSetup)


Thnaks agian for your help