Excel

Change page setup (and printers) with userform interface

Ease of Use

Intermediate

Version tested with

2000, 2003 

Submitted by:

mdmackillop

Description:

Simple userform to change paper size and orientation to suit your spreadsheet. Changing printers in a network situation reqires additional code which can be found here http://www.vbaexpress.com/kb/getarticle.php?kb_id=528 

Discussion:

If you have access to different paper sizes, this userform lets you switch easily between the options and preview the results. 

Code:

instructions for use

			

'To open userform Option Explicit Sub ShowPageSetUp() PageSetUp.Show False End Sub 'All Userform code is contained in the attached example 'Determine the Constants for the paper sizes you wish to use by searching 'xlPaperSize in the Object Browser (see picture in sample file). 'Set the UserForm Captions to suit. 'Set the Constants in the UserForm_Initialize code section 'Set Buttons 3 & 4 Click events to the Paper Names 'Caution: Opening the userform from the VBE or as Modal will freeze Excel in 'Preview mode. Use the Command Button or Show macro to avoid this problem. 'Alternatively, unload the form prior to Previewing.

How to use:

  1. Open an the example Excel workbook
  2. Select Tools/Macro/Visual Basic Editor
  3. In the VBE window, select VBAProject(Personal.xls)
  4. Select Insert/Module
  5. Copy and paste the code above into the Module
  6. Drag the PageSetup userform from PageSetup to Personal.xls
  7. Now select File/Close and Return To Microsoft Excel
  8. Create a shortcut to run ShowPageSetup
  9. Save the workbook
 

Test the code:

  1. Open the userform
  2. Change the settings
  3. Open File/PageSetup and the new settings should be reflected there
  4. or
  5. Preview/Print with the new settings
 

Sample File:

PageSetup.zip 62.7KB 

Approved by mdmackillop


This entry has been viewed 276 times.

Please read our Legal Information and Privacy Policy
Copyright @2004 - 2020 VBA Express