SOLVED
Home

Chart sheet default paper size

%3CLINGO-SUB%20id%3D%22lingo-sub-677907%22%20slang%3D%22en-US%22%3EChart%20sheet%20default%20paper%20size%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-677907%22%20slang%3D%22en-US%22%3E%3CP%3EHi%2C%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EWhen%20I%20select%20data%20in%20an%20excel%20365%20worksheet%20and%20press%20F11%20to%20chart%20it%2C%20the%20chart%20sheet%20creates%20using%20the%20'Letter'%20paper%20size...%20I%20can%20manually%20change%20the%20chart%20sheet%20to%20use%20'Legal'%20paper%20size%2C%20but%20I'd%20like%20it%20to%20create%20using%20'Legal'%20by%20default.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EIs%20there%20any%20way%20to%20change%20the%20default%20paper%20size%20for%20chart%20sheets%3F%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3ENote%20that%20when%20I%20open%20a%20new%20workbook%20and%20click%20File%20%26gt%3B%20Print%2C%20the%20paper%20size%20is%20shown%20as%20Legal.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThanks%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-677907%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-678009%22%20slang%3D%22en-US%22%3ERe%3A%20Chart%20sheet%20default%20paper%20size%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-678009%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F356617%22%20target%3D%22_blank%22%3E%40_jon_%3C%2FA%3E%26nbsp%3Bhello%20there.%20There%20is%20no%20way%20to%20change%20this%20by%20default.%20IIRC%20this%20is%20tied%20to%20your%20regional%20%5BOS%5D%20settings.%20You%20can%20circumvent%20this%20with%20code.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3COL%3E%3CLI%3EPress%20ALT%2BF11%20to%20open%20the%20Visual%20Basic%20Editor%20(VBE)%3COL%3E%3CLI%3EAlternatively%20you%20can%20click%20on%20the%20Developer%20tab%20(must%20be%20showing)%20and%20click%20'Visual%20Basic'%3C%2FLI%3E%3C%2FOL%3E%3C%2FLI%3E%3CLI%3EShow%20Project%20Explorer%20(CTRL%2BR)%3C%2FLI%3E%3CLI%3EExpand%20your%20file%2Fproject%3C%2FLI%3E%3CLI%3EExpand%20'Microsoft%20Excel%20Objects'%3C%2FLI%3E%3CLI%3EDouble%20click%20on%20'ThisWorkbook'%3C%2FLI%3E%3CLI%3EPaste%20the%20below%20code%3C%2FLI%3E%3CLI%3EClose%20code%20window%3C%2FLI%3E%3C%2FOL%3E%3CP%3EPrivate%20Sub%20Workbook_NewChart(ByVal%20Ch%20As%20Chart)%3CBR%20%2F%3E%26nbsp%3B%20%26nbsp%3B%20If%20Ch.Type%20%3D%203%20And%20Ch.Parent.Name%20%3D%20ThisWorkbook.Name%20Then%3CBR%20%2F%3E%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20Ch.PageSetup.PaperSize%20%3D%20xlPaperLegal%3CBR%20%2F%3E%26nbsp%3B%20%26nbsp%3B%20End%20If%3CBR%20%2F%3EEnd%20Sub%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-680167%22%20slang%3D%22en-US%22%3ERe%3A%20Chart%20sheet%20default%20paper%20size%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-680167%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F19137%22%20target%3D%22_blank%22%3E%40Zack%20Barresse%3C%2FA%3Ethanks%20for%20this%20solution%20-%20works%20well!%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EIf%20I%20understand%20correctly%2C%20I%20need%20to%20create%20this%20macro%20in%20each%20new%20workbook%20I%20create%2C%20in%20order%20for%20it%20to%20be%20active%20in%20that%20workbook...%20Do%20you%20know%20any%20way%20I%20can%20make%20excel%20create%20new%20workbooks%20with%20this%20macro%20already%20present%3F%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-681055%22%20slang%3D%22en-US%22%3ERe%3A%20Chart%20sheet%20default%20paper%20size%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-681055%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F356617%22%20target%3D%22_blank%22%3E%40_jon_%3C%2FA%3E%26nbsp%3Byou'd%20have%20to%20add%20the%20solution%20to%20a%20file%2C%20then%20save%20it%20as%20a%20template%2C%20and%20open%20a%20Workbook%20from%20that%20template%20every%20time.%20It's%20probably%20something%20I'd%20create%20a%20macro%20for%20and%20store%20it%20in%20your%20Personal.xlsb%20file%2C%20then%20assign%20it%20to%20a%20Ribbon%2FQAT%20button.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-682731%22%20slang%3D%22en-US%22%3ERe%3A%20Chart%20sheet%20default%20paper%20size%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-682731%22%20slang%3D%22en-US%22%3EThanks%20Zack%3C%2FLINGO-BODY%3E
_jon_
New Contributor

Hi,

 

When I select data in an excel 365 worksheet and press F11 to chart it, the chart sheet creates using the 'Letter' paper size... I can manually change the chart sheet to use 'Legal' paper size, but I'd like it to create using 'Legal' by default.

 

Is there any way to change the default paper size for chart sheets?

 

Note that when I open a new workbook and click File > Print, the paper size is shown as Legal.

 

Thanks

 

 

4 Replies

@_jon_ hello there. There is no way to change this by default. IIRC this is tied to your regional [OS] settings. You can circumvent this with code.

 

  1. Press ALT+F11 to open the Visual Basic Editor (VBE)
    1. Alternatively you can click on the Developer tab (must be showing) and click 'Visual Basic'
  2. Show Project Explorer (CTRL+R)
  3. Expand your file/project
  4. Expand 'Microsoft Excel Objects'
  5. Double click on 'ThisWorkbook'
  6. Paste the below code
  7. Close code window

Private Sub Workbook_NewChart(ByVal Ch As Chart)
    If Ch.Type = 3 And Ch.Parent.Name = ThisWorkbook.Name Then
        Ch.PageSetup.PaperSize = xlPaperLegal
    End If
End Sub

@Zack Barressethanks for this solution - works well!

 

If I understand correctly, I need to create this macro in each new workbook I create, in order for it to be active in that workbook... Do you know any way I can make excel create new workbooks with this macro already present?

Solution

@_jon_ you'd have to add the solution to a file, then save it as a template, and open a Workbook from that template every time. It's probably something I'd create a macro for and store it in your Personal.xlsb file, then assign it to a Ribbon/QAT button.

Thanks Zack