Results 1 to 8 of 8
  1. #1
    New Lounger
    Join Date
    Mar 2002
    Location
    Peterborough, UK
    Posts
    18
    Thanks
    0
    Thanked 0 Times in 0 Posts

    File / New from the Toolbar (Excel 2000)

    How do I customise Excel 2000 so that the New Document button on the toolbar actually displays the New Worksheet dialog box that you get when you go File / New.

    I've got this setup in Word and it's much more useful.

    While I'm on the subject how can I get Print Preview to come up when I hit Ctrl-F2 as in Word? I find having to grab the mouse a real pain......

    Thanks.

  2. #2
    Platinum Lounger
    Join Date
    Feb 2001
    Location
    Weert, Limburg, Netherlands
    Posts
    4,812
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: File / New from the Toolbar (Excel 2000)

    File New:

    - Open the VBE editor
    - open the project of Personal.xls
    - insert a module or choose one appropriate
    - insert this macro:

    Sub ShowFileNew()
    Application.Dialogs(xlDialogNew).Show
    End Sub

    - Close the VBE
    - Rightclick the toolbar, choose customize
    - Rightclick the FIle New button and choose Assign Macro. Now select "ShowFileNew" and OK all your way back to XL.

    If you have no Personal.xls, have XL create one for you:

    - Tools, Macro, record macro
    - Make sure you choose "Personal Macro Workbook" in the Store macro In dropdown.
    - OK
    - do something trivial
    - stop recording.

    Now XL should have created a hidden workbook called Personal.xls

    Print preview:

    No easy way, except Control-P followed by Alt-w
    Jan Karel Pieterse
    Microsoft Excel MVP, WMVP
    www.jkp-ads.com
    Professional Office Developers Association

  3. #3
    WS Lounge VIP rory's Avatar
    Join Date
    Dec 2000
    Location
    Burwash, East Sussex, United Kingdom
    Posts
    6,280
    Thanks
    3
    Thanked 191 Times in 177 Posts

    Re: File / New from the Toolbar (Excel 2000)

    Or:
    Right-click toolbar, choose Customize..., select Commands Tab, and from the File category drag the item with New... onto the toolbar and remove the original. Icon will look the same but it will bring up the dialog rather than just creating a standard workbook.
    Regards,
    Rory

    Microsoft MVP - Excel

  4. #4
    Platinum Lounger
    Join Date
    Feb 2001
    Location
    Weert, Limburg, Netherlands
    Posts
    4,812
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: File / New from the Toolbar (Excel 2000)

    Hey, I liked my complicated solution better <g,d&r>
    Jan Karel Pieterse
    Microsoft Excel MVP, WMVP
    www.jkp-ads.com
    Professional Office Developers Association

  5. #5
    3 Star Lounger
    Join Date
    Jun 2001
    Location
    Maidstone, Kent, England
    Posts
    398
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: File / New from the Toolbar (Excel 2000)

    For print preview you could use a macro from a hot key in Personal xls but I don't know how you can set it to an "F" key!

  6. #6
    Platinum Lounger
    Join Date
    Feb 2001
    Location
    Weert, Limburg, Netherlands
    Posts
    4,812
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: File / New from the Toolbar (Excel 2000)

    example, in Personal.xls'es Thisworkbook module:

    Private Sub Workbook_open
    Application.OnKey "{F12}", "YourPrintPreviewSub"
    End Sub
    Jan Karel Pieterse
    Microsoft Excel MVP, WMVP
    www.jkp-ads.com
    Professional Office Developers Association

  7. #7
    New Lounger
    Join Date
    Mar 2002
    Location
    Peterborough, UK
    Posts
    18
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: File / New from the Toolbar (Excel 2000)

    Is it really that long since I posted that question :-)

    Thanks for the tip regarding dragging 'n dropping the toolbar button. Should have thought about that myself :-)

    Any ideas about Ctrl-F2 for Print Preview?

    Cheers,

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

    Re: File / New from the Toolbar (Excel 2000)

    About Ctrl+F2 for Print Preview: adapt Jan Karel Pieterse's suggestion.

    (1) Create a macro in a standard module in your Personal.xls

    Sub MyPrintPreview()
    On Error Resume Next
    ActiveWindow.SelectedSheets.PrintPreview
    End Sub

    Note: the On Error Resume Next is there to prevent error messages when you have no visible workbook open.

    (2) Create the following macro in the module behind ThisWorkbook in your Personal.xls:

    Private Sub Workbook_Open()
    Application.OnKey "^{F2}", "MyPrintPreview"
    End Sub

    Note: the ^ stands for the Control key.

    Note: to create a macro in ThisWorkbook:
    Expand Personal.xls in the Project Explorer in the Visual Basic Editor.
    Expand Microsoft Excel Objects.
    Double-click ThisWorkbook. The corresponding module opens.
    From the Object dropdown list in the upper left of the module window, select Workbook.
    You'll get the skeleton of the Workbook_Open event procedure. Type or copy <font color=blue>Application.OnKey "^{F2}", "MyPrintPreview"</font color=blue> into it.

    (3) Still in the Visual Basic Editor, save Personal.xls.

    (4) Exit and restart Excel. Open or create a workbook and try Ctrl+F2.

    HTH,
    Hans

Posting Permissions

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