Results 1 to 9 of 9
  1. #1
    4 Star Lounger
    Join Date
    Jun 2001
    Location
    Sacramento, California, USA
    Posts
    491
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Macro to format sheet (WIN2000-XL97)

    I have searched the forum using 'Macro format sheet' and could not find what I am looking for.

    This is what I would like.

    Many of my spreadsheets are exported from Access to Excel. Unfortunately, the template(personal.xls) that
    I use for new spreadsheets is not activated.
    What I would like, is to format a sheet with the following, and create a macro using this formatting.
    Then, when I need to format the sheet, I just have to run the macro.

    Formatting:
    PAGE:
    Orientation - Landscape
    Paper size - Legal
    MARGINS:
    Margins - Left 0 Right 0
    Center on page - Horizontally
    SHEET:
    Rows to repeat at top - $1:$1

    Can anyone help?

    As always - Much, much appreciated !!

    Michael

  2. #2
    3 Star Lounger
    Join Date
    Dec 2000
    Location
    Vancouver, Br. Columbia, Canada
    Posts
    268
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Macro to format sheet (WIN2000-XL97)

    Open your "personal.xls" workbook, and include the following macro:
    <pre>Sub SetPrint()
    '
    With ActiveSheet.PageSetup
    .PrintTitleRows = "$1:$1"
    .LeftMargin = Application.InchesToPoints(0)
    .RightMargin = Application.InchesToPoints(0)
    .CenterHorizontally = True
    .Orientation = xlLandscape
    .PaperSize = xlPaperLegal
    End With
    End Sub
    </pre>


    Assign it to a button on the toolbar, or to a ctrl-key combination, depending on how you like to work. The next time you import information from Access, running the SetPrint macro should set the page up the way you like.

  3. #3
    4 Star Lounger
    Join Date
    Jun 2001
    Location
    Sacramento, California, USA
    Posts
    491
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Macro to format sheet (WIN2000-XL97)

    OUTSTANDING!! Exactly what I wanted.

    Thank you so much!!
    I really appreciate you taking time to help.

    Michael

  4. #4
    4 Star Lounger
    Join Date
    Jun 2001
    Location
    Sacramento, California, USA
    Posts
    491
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Macro to format sheet (WIN2000-XL97)

    OK - Minor problem (I think!!)

    Now, whenever I open any spreadsheet, the Personal.xls sheet appears behind whatever
    spreadsheet I open.
    Also, when I open a 'new' spreadsheet, the title bar reads "Personal.xls'

    What did I do?? I believe I had the personal.xls sheet "hidden", but now when I go to XlsStart folder and
    try ti hide the sheet when I open the personal.xls spreadsheet, it says I must have at least one open
    sheet etc. But prior to this, when I opened the personal.xls there was no sheet !! And there were no sheets
    behind new spreadsheets!!

    I know I am missing something, but I cannot see it.

    Please help one last time <img src=/S/please.gif border=0 alt=please width=31 height=23>

    Thank you!!!

    Michael

  5. #5
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Macro to format sheet (WIN2000-XL97)

    Don't hide the worksheet in Personal.xls using Format/Sheet/Hide, but instead hide its window using Window/Hide. Don't forget to save Personal.xls when you quit Excel (or go to the Visual Basic Editor and save it from there.)

  6. #6
    4 Star Lounger
    Join Date
    Jun 2001
    Location
    Sacramento, California, USA
    Posts
    491
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Macro to format sheet (WIN2000-XL97)

    <img src=/S/blush.gif border=0 alt=blush width=15 height=15>

    <img src=/S/stupidme.gif border=0 alt=stupidme width=30 height=30>

    Thank you Hans - Everything is wonderful... again! I really appreciate your help.

    Thanks to all !

    Michael

  7. #7
    4 Star Lounger
    Join Date
    Jun 2001
    Location
    Sacramento, California, USA
    Posts
    491
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Macro to format sheet (WIN2000-XL97)

    OK - so maybe now I am getting greedy ! <img src=/S/smile.gif border=0 alt=smile width=15 height=15>

    I did search the Excel Help files for some of the other properties (Format header - footer, etc) and I may
    just be using the wrong 'key words'.

    Where can I find such a list? Example of what I'm looking for:
    .Orientation = xlLandscape

    I would need both sides - the property and the way Excel needs to see it.

    Is this information in the Excel Help files or do I need to search elsewhere?

    Thanks again everyone !

    Michael

  8. #8
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Macro to format sheet (WIN2000-XL97)

    The Object Browser is a good place to learn about the available properties and methods, and the online help files are quite useful in Excel 97.
    <UL><LI>In the Visual Basic Editor, activate the Object Browser (select View/Object Browser or press F2).
    <LI>In the Search box, type the name of an object, for example PageSetup, then press Enter.
    <LI>In the 'Members' pane, you'll see a list of all properties and methods of the PageSetup object.
    <LI>If you select one of the properties or methods, the bottom pane will provide information about it.
    <LI>Sometimes, this information contains a link. For example, if you select Orientation, the bottom page reads
    Property Orientation As XlPageOrientation
    Click the underlined term to see what values XlPageOrientation has.
    <LI>Click the Back button to return to Orientation. Press F1 to get context-sensitive help, with an example.
    <LI>You can also look up the PageSetup object in the online help; the subject has links to see all properties and methods.[/list]HTH

  9. #9
    4 Star Lounger
    Join Date
    Jun 2001
    Location
    Sacramento, California, USA
    Posts
    491
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Macro to format sheet (WIN2000-XL97)

    Once again, Thank you so much !!
    After opening the Object Browser it all came back to me! ( I did learn about the object browser
    in my VB class 5 years ago, but just didn't consider it within Excel.)

    You are very helpful and considerate Hans. Thank you.

    Michael

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •