+ Reply to Thread
Results 1 to 15 of 15

Print Worksheet From Custom Button Only

  1. #1
    Forum Contributor
    Join Date
    09-19-2004
    Location
    Canada
    Posts
    405

    Question Print Worksheet From Custom Button Only

    Hi All,

    I have a workbook, which others besides me would be using.

    CHEQUE REGISTER is the name of the sheet containing financial data. Column B contains cheque numbers and Column U contains cheque amounts in words.

    Cells B6 to Q6, of sheet named Cheque Template, are merged and have a (combined) width of 72. It is formatted as @**, and contains the following formula:

    =IF(ISERROR(IF(VLOOKUP($S$3,'CHEQUE REGISTER'!$B$4:$U$10001,20,FALSE)="","",VLOOKUP($S$3,'CHEQUE REGISTER'!$B$4:$U$10001,20,FALSE))),"",IF(VLOOKUP($S$3,'CHEQUE REGISTER'!$B$4:$U$10001,20,FALSE)="","",VLOOKUP($S$3,'CHEQUE REGISTER'!$B$4:$U$10001,20,FALSE)))

    Page Setup Scaling is set to Adjust to 100 % normal size.

    When the formula copies a large amount, for example: Seven Hundred Seventy Seven Thousand Seven Hundred Seventy Seven Dollars and Seventy Seven Cents*********, the cell displays ###############. (Note: I am using font size 10.)

    So, in order to prevent a cheque from being printed with ############### instead of the amount in words, I want to force the user to do a print preview, decrease the font size, if necessary, and before he/she can print the cheque.

    In short, this is what I am trying to achieving:
    1. Disable print on sheet named Cheque Template
    2. Click a custom button to print
    3. Show print preview
    4. Question: OK to print?
    5. If Yes, print
    6. If No, show message “Please make the necessary adjustments and try again”)
    7. Close button

    I realize that this can only be done with VBA code so I am seeking someone to write it for me.

    Help, anyone?

    Thanks,
    Gos-C
    Using Excel 2010 & Windows 10
    "It is better to be prepared for an opportunity and not have one than to have an opportunity and not be prepared."

  2. #2
    Forum Expert
    Join Date
    12-10-2006
    Location
    Sydney
    MS-Off Ver
    2007
    Posts
    3,283
    Hi there,

    Have a look at the attached where all instances of Print and Print Preview have been disabled whenever the Cheque Template tab is active (selected).

    As you can't have a message box while in Print Preview mode, I've coded the Print button to ask the user if all is OK before going into this mode.

    Also, I'm not sure how you've merged the cells for the cheque amount, but this article http://www.ozgrid.com/forum/showthread.php?p=366772 could prove useful.

    HTH

    Robert
    Attached Files Attached Files

  3. #3
    Forum Expert royUK's Avatar
    Join Date
    11-18-2003
    Location
    Derbyshire,UK
    MS-Off Ver
    Xp; 2007; 2010
    Posts
    26,202
    Why not simply use the Before_Ptint event of the Workbook object/ Check the sheet name & cancel if it is the template

    Please Login or Register  to view this content.
    Hope that helps.

    RoyUK
    --------
    For Excel Tips & Solutions, free examples and tutorials why not check out my web site

    Free DataBaseForm example

  4. #4
    Forum Contributor
    Join Date
    09-19-2004
    Location
    Canada
    Posts
    405
    Thank very much for your responses, Robert and RoyUK. I would try them and then follow up later.

    (BTW, thanks also for the tip/link, Robert. I never knew about Center Across Selection under the Alignment Tab -- very useful indeed! Nevertheless, that wouldn't work for the cheque amount because it must be left aligned.)

    Gos-C
    Last edited by Gos-C; 06-18-2007 at 01:56 PM.

  5. #5
    Registered User
    Join Date
    08-19-2007
    Posts
    2

    A slight change to the print button function... question

    Hi Trebor,

    I loved your macro print button.

    I have created a spread sheet that on sheet 1 has hyperlinks to different forms on sheet 2. I would like to put a print button above each form so that the user has the ability to print out each form indiviually. Thus, your print button, but print out A1 to G25

    next print button would print out the form on sheet 2 at A35 to G50

    etc...

    I would also like to create a roll up print button that prints all the forms. IE

    A1 to G25
    new page
    A35 to G50
    new page
    etc
    etc

    Can you help me out, as I am a NEWBIE, on how to modify your code to do this?

    Thank you, Thank you!

    -Bob

  6. #6
    Forum Expert
    Join Date
    12-10-2006
    Location
    Sydney
    MS-Off Ver
    2007
    Posts
    3,283
    Hi Bob,

    If you attach your spreadsheet, I'll have a look for you.

    Cheers,

    Robert

  7. #7
    Forum Expert royUK's Avatar
    Join Date
    11-18-2003
    Location
    Derbyshire,UK
    MS-Off Ver
    Xp; 2007; 2010
    Posts
    26,202
    try this userform method
    Attached Files Attached Files

  8. #8
    Forum Contributor
    Join Date
    09-19-2004
    Location
    Canada
    Posts
    405
    I can't get this to work. It's is still printing from the Quick Print button (Excel 2007).

    Gos-C

  9. #9
    Forum Expert royUK's Avatar
    Join Date
    11-18-2003
    Location
    Derbyshire,UK
    MS-Off Ver
    Xp; 2007; 2010
    Posts
    26,202
    Quote Originally Posted by Gos-C
    I can't get this to work. It's is still printing from the Quick Print button (Excel 2007).

    Gos-C
    What isn't working?

  10. #10
    Forum Contributor
    Join Date
    09-19-2004
    Location
    Canada
    Posts
    405
    Hi royUK,

    First, in the Visual Basic Editor Project Explorer window, the CHEQUE TEMPLATE sheet is shown as PrintPrevClass (CHEQUE TEMPLATE) instead of Sheet11 (CHEQUE TEMPLATE) - like the other sheets show. I have been trying different things so I don't know what caused that. How can I get it to show Sheet11?

    I inserted a Module and pasted the following code (and Print hutton) from Robert's post:

    Please Login or Register  to view this content.
    The custom macro print button worked fine but I was still able to print normally -- e.g., by just clicking Excel's Quick Print button.

    I then insert your code:

    Please Login or Register  to view this content.
    before Robert's, but normal printing was not disabled.

    Thanks for you help,
    Gos-C

  11. #11
    Forum Expert royUK's Avatar
    Join Date
    11-18-2003
    Location
    Derbyshire,UK
    MS-Off Ver
    Xp; 2007; 2010
    Posts
    26,202
    The Before_Print code will work however, you need to use the correct sheet name. Can you attach your workbook so that I can see the sheet name? You will need to zip it.

  12. #12
    Forum Contributor
    Join Date
    09-19-2004
    Location
    Canada
    Posts
    405
    Hi royUK,

    I am unable to attach my workbook as it is 1.22 MB in size - yes, I deleted most of the data. Any advice?

    Gos-C

  13. #13
    Forum Expert royUK's Avatar
    Join Date
    11-18-2003
    Location
    Derbyshire,UK
    MS-Off Ver
    Xp; 2007; 2010
    Posts
    26,202
    The name of the sheet is capitalised in your earlier post, the name must be exact. Try

    Please Login or Register  to view this content.

  14. #14
    Forum Contributor
    Join Date
    09-19-2004
    Location
    Canada
    Posts
    405
    Hi royUK,

    Thank you very much for your help - I appreciate it greatly.

    I pasted your code in This Workbook and Robert's code in a module. Now, normal Print and Print Preview are disabled. Good - but now I can't print.

    When I click the macro Print button, OK to print, and then Yes, I am back to Please use the button.

    Do you know what I should do?

    Gos-c

  15. #15
    Forum Expert royUK's Avatar
    Join Date
    11-18-2003
    Location
    Derbyshire,UK
    MS-Off Ver
    Xp; 2007; 2010
    Posts
    26,202
    Don't use both codes.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1