Results 1 to 8 of 8
  1. #1
    2 Star Lounger
    Join Date
    Jan 2002
    Location
    Trenton, Ontario
    Posts
    175
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Printing Macro (2000)

    I have a spreadsheet in which a user will input a bit of information then switch from Sheet1 to Sheet2 and then print out detailed pallet tags. The problem is that many users still hit the print button when it is still on Sheet1 <img src=/S/bash.gif border=0 alt=bash width=35 height=39> , they then leave and come back about a half hour later only to find they have printed out 200 copies of the wrong screen.
    Could I trouble someone to offer up a quick macro that will only allow the "Print" button to work when Sheet2 is selected?
    Thanks in advance for any help you can give.
    Stats

  2. #2
    Super Moderator WebGenii's Avatar
    Join Date
    Jan 2001
    Location
    Redcliff, Alberta, Canada
    Posts
    4,066
    Thanks
    2
    Thanked 5 Times in 5 Posts

    Re: Printing Macro (2000)

    Why not a macro button on sheet 1 tied to a macro that switches to sheet2 and prints. Might be easier than disabling/enabling the print button.

    Just my <img src=/S/2cents.gif border=0 alt=2cents width=15 height=15>
    [b]Catharine Richardson (WebGenii)
    WebGenii Home Page
    Moderator: Spreadsheets, Other MS Apps, Presentation Apps, Visual Basic for Apps, Windows Mobile

  3. #3
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Printing Macro (2000)

    You could use a macro like the one below and attach it to a button on the toolbar.

    <pre>Public Sub PrintSheet2()
    Worksheets("Sheet2").PrintOut Copies:=1
    End Sub
    </pre>

    Legare Coleman

  4. #4
    5 Star Lounger
    Join Date
    Oct 2002
    Location
    Wellington, Wellington, New Zealand
    Posts
    621
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Printing Macro (2000)

    Why not just create a BeforePrint routine for the Workbook in question and force the target to be active

    Private Sub Workbook_BeforePrint(Cancel As Boolean)
    activeworkbook,worksheets("sheet2").activate
    End Sub

  5. #5
    2 Star Lounger
    Join Date
    Jan 2002
    Location
    Trenton, Ontario
    Posts
    175
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Printing Macro (2000)

    Thanks Legare!!
    For my application your macro is going to work great, one quick question...Is there a way for the macro to print out a user defined number of copies? I tried 'Worksheets("Sheet2").PrintOut Copies:=Sheet1!$b$5' where the user has already entered into B5 the number of copies they want, but that didn't work. Any ideas?
    Thanks again for your help.
    Stats

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

    Re: Printing Macro (2000)

    You can't refer to a cell in VBA the way you do in a formula. Use

    Worksheets("Sheet2").PrintOut Copies:=Worksheets("Sheet1").Range("B5")

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

    Re: Printing Macro (2000)

    Or Worksheets("Sheet1").[b5]
    Jan Karel Pieterse
    Microsoft Excel MVP, WMVP
    www.jkp-ads.com
    Professional Office Developers Association

  8. #8
    2 Star Lounger
    Join Date
    Jan 2002
    Location
    Trenton, Ontario
    Posts
    175
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Printing Macro (2000)

    Thanks Hans,
    Ive only just begun to learning about macros and I can't thank you (and Legare, and Jan.......et al) enough for all the help you give us VB challenged folks.
    Thanks Again
    Stats

Posting Permissions

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