+ Reply to Thread
Results 1 to 35 of 35

Print Sheets based on checkboxes in UserForm, Checkboxes also control another macro

  1. #1
    Registered User
    Join Date
    01-31-2013
    Location
    Wisconsin USA
    MS-Off Ver
    Excel 2010
    Posts
    56

    Print Sheets based on checkboxes in UserForm, Checkboxes also control another macro

    I need some help on this one. I have a userform that runs multiple macros based on selected checkboxes, each checkbox controlling their own macro. What I am looking for is the ability to use these same checkboxes to select certain sheets to print.

    pseudocode,

    commandbutton print (start code)
    Print = 1 (enable variable to only run enabled code)
    Check values of checkboxes
    run only the print codes if true
    Print = 0 (reset variable to normal mode)
    end

    any help with this will be appreciated.

    Please Login or Register  to view this content.

    Please Login or Register  to view this content.

  2. #2
    Forum Guru Norie's Avatar
    Join Date
    02-02-2005
    Location
    Stirling, Scotland
    MS-Off Ver
    Microsoft Office 365
    Posts
    19,597

    Re: Print Sheets based on checkboxes in UserForm, Checkboxes also control another macro

    It shouldn't be difficult, what code do you have for printing?

    By the way, what are you using the variable Print for?
    Last edited by Norie; 02-28-2013 at 11:09 AM.
    If posting code please use code tags, see here.

  3. #3
    Registered User
    Join Date
    01-31-2013
    Location
    Wisconsin USA
    MS-Off Ver
    Excel 2010
    Posts
    56

    Re: Print Sheets based on checkboxes in UserForm, Checkboxes also control another macro

    what you see above is what i have for printing currently. "module 2"
    Im wanting to use this variable sheet print code because the pages i do have are quite large and use 11x17 plotter paper. so im trying conserve a few tree in the process.
    i have searched google for some solutions but i couldnt quite find anything pretaining to variable sheets. it was more for ranges and full workbooks.

  4. #4
    Forum Guru Norie's Avatar
    Join Date
    02-02-2005
    Location
    Stirling, Scotland
    MS-Off Ver
    Microsoft Office 365
    Posts
    19,597

    Re: Print Sheets based on checkboxes in UserForm, Checkboxes also control another macro

    Oh right, I thought you had an inividual print sub for each checkbox that would print a specific page, area, whatever.

    What exactly do you need for the sub Print_Current_Workbook?

    Set the print area, which pages to print?

  5. #5
    Registered User
    Join Date
    01-31-2013
    Location
    Wisconsin USA
    MS-Off Ver
    Excel 2010
    Posts
    56

    Re: Print Sheets based on checkboxes in UserForm, Checkboxes also control another macro

    im not exactly sure if the coding is possible, but what about this

    at each check box, have the sheet name variable
    and the coding inserts this variable into the module 2 print code.

    i think that this would work if inserted into the module 2 print code
    Sheets("Name").PrintOut
    to print certain sheets.

    is this possible? this way it keeps the coding shorter. also is there a time issue when multiple print code are set to the printer that you know of?
    by timing issue i mean is the code going too fast for the transfer of information to the print spooler.

    the coding just needs to print whole sheet, areas are not needed to for coding.
    Last edited by krackaberr; 02-28-2013 at 11:57 AM.

  6. #6
    Registered User
    Join Date
    01-31-2013
    Location
    Wisconsin USA
    MS-Off Ver
    Excel 2010
    Posts
    56

    Re: Print Sheets based on checkboxes in UserForm, Checkboxes also control another macro

    by the way, each check box has two macros that wont be run, these are for data entry and generating the data into a file.

  7. #7
    Forum Guru Norie's Avatar
    Join Date
    02-02-2005
    Location
    Stirling, Scotland
    MS-Off Ver
    Microsoft Office 365
    Posts
    19,597

    Re: Print Sheets based on checkboxes in UserForm, Checkboxes also control another macro

    Don't know about writing the name of a sheet to the code, that is possible but not necessary I think, but what you could do is
    pass a reference to or the name of the worksheet to be printed to the sub that does the printing?

    What exactly is being printed with the sub at it is?

    It looks like it's the entire workbook.

  8. #8
    Registered User
    Join Date
    01-31-2013
    Location
    Wisconsin USA
    MS-Off Ver
    Excel 2010
    Posts
    56

    Re: Print Sheets based on checkboxes in UserForm, Checkboxes also control another macro

    yes its the entire active workbook, how do i change this code to use " Sheets("Name").PrintOut " instead?

    what im thinking for passing the variable sheet name is,

    Please Login or Register  to view this content.

    this of course not be a working code but a good understanding of what i need.
    the print = 1 would be made when a button would be pressed to only print the sheets of the workbook.
    Last edited by krackaberr; 02-28-2013 at 12:22 PM.

  9. #9
    Forum Guru Norie's Avatar
    Join Date
    02-02-2005
    Location
    Stirling, Scotland
    MS-Off Ver
    Microsoft Office 365
    Posts
    19,597

    Re: Print Sheets based on checkboxes in UserForm, Checkboxes also control another macro

    All you should need in the print sub is this.
    Please Login or Register  to view this content.
    That is all you are doing isn't it, printing put one copy of the worksheet that's passed to the sub.

  10. #10
    Registered User
    Join Date
    01-31-2013
    Location
    Wisconsin USA
    MS-Off Ver
    Excel 2010
    Posts
    56

    Re: Print Sheets based on checkboxes in UserForm, Checkboxes also control another macro

    right now its printing out the whole workbook. and i dont want that. i only need certain sheets. in all, it prints one copy of the entire workbook as it stands now. i need to to print certain sheets.

  11. #11
    Registered User
    Join Date
    01-31-2013
    Location
    Wisconsin USA
    MS-Off Ver
    Excel 2010
    Posts
    56

    Re: Print Sheets based on checkboxes in UserForm, Checkboxes also control another macro

    let me put something together quick. as a whole.

  12. #12
    Registered User
    Join Date
    01-31-2013
    Location
    Wisconsin USA
    MS-Off Ver
    Excel 2010
    Posts
    56

    Re: Print Sheets based on checkboxes in UserForm, Checkboxes also control another macro

    is this the syntax to print a certain sheet, not an entire workbook, based on a value of a checkbox?

    Please Login or Register  to view this content.

  13. #13
    Forum Guru Norie's Avatar
    Join Date
    02-02-2005
    Location
    Stirling, Scotland
    MS-Off Ver
    Microsoft Office 365
    Posts
    19,597
    I posted the code you need to print one worksheet.
    Please Login or Register  to view this content.
    By the way, I thought you were going to use a seperate button for printing.
    Last edited by Norie; 02-28-2013 at 02:04 PM.

  14. #14
    Registered User
    Join Date
    01-31-2013
    Location
    Wisconsin USA
    MS-Off Ver
    Excel 2010
    Posts
    56

    Re: Print Sheets based on checkboxes in UserForm, Checkboxes also control another macro

    thats right i did... i was thinking of using that button to set the print variable to one. so that the code wouldnt execute both the macros for data entry, and the print code.
    with the code you posted, wouldnt that just print the sheet thats active? the code i currently has two workbooks open at a given time with either workbook active. i need to be able to specify between the two workbooks in the code and also specify what sheet to print.

    i guess i dont know how that code snippit you posted works

  15. #15
    Registered User
    Join Date
    01-31-2013
    Location
    Wisconsin USA
    MS-Off Ver
    Excel 2010
    Posts
    56

    Re: Print Sheets based on checkboxes in UserForm, Checkboxes also control another macro

    the command button code for the new button would be as follows i think

    Private Sub CommandButton'somenumber'_Click()
    dim Print as long
    Print = 1
    CommandButton1_Click
    End Sub

  16. #16
    Forum Guru Norie's Avatar
    Join Date
    02-02-2005
    Location
    Stirling, Scotland
    MS-Off Ver
    Microsoft Office 365
    Posts
    19,597

    Re: Print Sheets based on checkboxes in UserForm, Checkboxes also control another macro

    That code will print the worksheet that the variable ws refers to.

    I actually thought the printing would be separate from what you have already.

  17. #17
    Registered User
    Join Date
    01-31-2013
    Location
    Wisconsin USA
    MS-Off Ver
    Excel 2010
    Posts
    56

    Re: Print Sheets based on checkboxes in UserForm, Checkboxes also control another macro

    is that a better way to do that printing? i wont ever need to print the whole workbook if i have certain sheets selected to print. my ill logic for vb is at a standstill, still trying to pick up what i can. all im coming up with right now are ideas more or less that i see will work for me.

  18. #18
    Forum Guru Norie's Avatar
    Join Date
    02-02-2005
    Location
    Stirling, Scotland
    MS-Off Ver
    Microsoft Office 365
    Posts
    19,597

    Re: Print Sheets based on checkboxes in UserForm, Checkboxes also control another macro

    The way I would do this would be to use a listbox to list all the worksheets.

    It could be set up so there's a checkbox next to each worksheet name.

    Then there would be a print button which would have code behind it that would only print worksheets that are checked in the listbox.

    That might sound complicated and/or a lot of work but it really isn't.

  19. #19
    Registered User
    Join Date
    01-31-2013
    Location
    Wisconsin USA
    MS-Off Ver
    Excel 2010
    Posts
    56

    Re: Print Sheets based on checkboxes in UserForm, Checkboxes also control another macro

    i want to make this as automated as possible so as the person im training to fill my position can focus more on other things instead of data entry. i would like to do it this way if you could help...

    this is what i have put together so far.

    Please Login or Register  to view this content.


    Can you help me debug this? Error states Compile error: Argument not optional

  20. #20
    Registered User
    Join Date
    01-31-2013
    Location
    Wisconsin USA
    MS-Off Ver
    Excel 2010
    Posts
    56

    Re: Print Sheets based on checkboxes in UserForm, Checkboxes also control another macro

    i want to make this as automated as possible so as the person im training to fill my position can focus more on other things instead of data entry. i would like to do it this way if you could help...

    this is what i have put together so far.

    Please Login or Register  to view this content.
    Last edited by krackaberr; 02-28-2013 at 04:45 PM. Reason: troubleshooting, so far all ok.

  21. #21
    Registered User
    Join Date
    01-31-2013
    Location
    Wisconsin USA
    MS-Off Ver
    Excel 2010
    Posts
    56

    Re: Print Sheets based on checkboxes in UserForm, Checkboxes also control another macro

    now im coming up with an error on the sub print

    Please Login or Register  to view this content.
    runtime error 9 subscript out of range

  22. #22
    Forum Guru Norie's Avatar
    Join Date
    02-02-2005
    Location
    Stirling, Scotland
    MS-Off Ver
    Microsoft Office 365
    Posts
    19,597

    Re: Print Sheets based on checkboxes in UserForm, Checkboxes also control another macro

    Wouldn't a listbox with a list of the sheets that can be printed be a good way to automate this for a user?

    All they would need to do is select, from the listbox, the sheets they want to print and then click a button.

    Any chance you could attach a workbook?

    Click on GO ADVANCED and use the paperclip icon to open the upload window.

    View Pic

  23. #23
    Registered User
    Join Date
    01-31-2013
    Location
    Wisconsin USA
    MS-Off Ver
    Excel 2010
    Posts
    56

    Re: Print Sheets based on checkboxes in UserForm, Checkboxes also control another macro

    the user already has to check the required checkboxes, so why add another list to that? i think i have a solution for what i want to do. i have to finish a project here quick i will upload my files as soon i am finished.

  24. #24
    Forum Guru Norie's Avatar
    Join Date
    02-02-2005
    Location
    Stirling, Scotland
    MS-Off Ver
    Microsoft Office 365
    Posts
    19,597

    Re: Print Sheets based on checkboxes in UserForm, Checkboxes also control another macro

    You could actually replace the existing checkboxes with a listbox.

    That would make things a lot easier as you would only have one control to deal with.

    The code would also be a lot shorter and more manageable.

  25. #25
    Registered User
    Join Date
    01-31-2013
    Location
    Wisconsin USA
    MS-Off Ver
    Excel 2010
    Posts
    56

    Re: Print Sheets based on checkboxes in UserForm, Checkboxes also control another macro

    and you say i can choose multiple, all, or even just one selection on the list box?

    this is what i did and it works for me 100%.

    Please Login or Register  to view this content.

    Please Login or Register  to view this content.
    This is exactly what i needed here. now, whats this list box because the example im looking at right now only show how to select one at a time. not multiple.

  26. #26
    Forum Guru Norie's Avatar
    Join Date
    02-02-2005
    Location
    Stirling, Scotland
    MS-Off Ver
    Microsoft Office 365
    Posts
    19,597

    Re: Print Sheets based on checkboxes in UserForm, Checkboxes also control another macro

    What example is that?

  27. #27
    Registered User
    Join Date
    01-31-2013
    Location
    Wisconsin USA
    MS-Off Ver
    Excel 2010
    Posts
    56

    Re: Print Sheets based on checkboxes in UserForm, Checkboxes also control another macro

    this code, checks if a checkbox is set for printing a set of pages.runs only that, printing selected pages if thats true, else will run a set of macros associated with each checkbox if the checkbox is set. this is how i need the code to act. BUT about this listbox, i still havent gotten any details on how it works. wether its a multiple select or not. i have no idea about listboxes, i have never used them before.

  28. #28
    Forum Guru Norie's Avatar
    Join Date
    02-02-2005
    Location
    Stirling, Scotland
    MS-Off Ver
    Microsoft Office 365
    Posts
    19,597
    If you were to attach an sample workbook I could demonstrate how a multiselect listbox might work.

  29. #29
    Registered User
    Join Date
    01-31-2013
    Location
    Wisconsin USA
    MS-Off Ver
    Excel 2010
    Posts
    56

    Re: Print Sheets based on checkboxes in UserForm, Checkboxes also control another macro

    user form and modules are used from addin


    sample.zip

  30. #30
    Forum Guru Norie's Avatar
    Join Date
    02-02-2005
    Location
    Stirling, Scotland
    MS-Off Ver
    Microsoft Office 365
    Posts
    19,597

    Re: Print Sheets based on checkboxes in UserForm, Checkboxes also control another macro

    There's no addin, and I really don't want to reconstruct the files/modules etc.

  31. #31
    Registered User
    Join Date
    01-31-2013
    Location
    Wisconsin USA
    MS-Off Ver
    Excel 2010
    Posts
    56

    Re: Print Sheets based on checkboxes in UserForm, Checkboxes also control another macro

    the addin is in the folder... i exported it... i kind of figured noone would bother taking the 4 steps to install the addin, so i havent uploaded you anything...

  32. #32
    Forum Guru Norie's Avatar
    Join Date
    02-02-2005
    Location
    Stirling, Scotland
    MS-Off Ver
    Microsoft Office 365
    Posts
    19,597

    Re: Print Sheets based on checkboxes in UserForm, Checkboxes also control another macro

    Sorry, you've lost me - you haven't upload anything?

    What about the zip file which contains these files?

    DEVICE PANEL ORDER.xlsx
    Label_Generator ARROWHEAD.xlsm
    Module1.bas
    Module2.bas
    Module3.bas
    Module4.bas
    Module5.bas
    UserForm1.frm
    UserForm1.frx

    I tried reconstructing the xlsm file but kept on hitting errors, especially after I imported the 5 modules listed above.

    By the way, code like this can be reduced by a fair bit if you get rid of all the selecting/activating.
    Please Login or Register  to view this content.
    Without activating/selecting.
    Please Login or Register  to view this content.

    There also appears to be a pattern in that code which could be used to make the code even shorter.

  33. #33
    Registered User
    Join Date
    01-31-2013
    Location
    Wisconsin USA
    MS-Off Ver
    Excel 2010
    Posts
    56

    Re: Print Sheets based on checkboxes in UserForm, Checkboxes also control another macro

    uploaded until now i mean... not sure about the errors, the modules are to be uploaded to the userform addin. im not sure how i can have it all packaged for you... other then the way you have it. the shortening of the code, ya i can shorten it but i have had errors where the code wont start in the correct spot. it would happen every once in a while. not sure if it was a compile error or not. better safe then sorry i think.

    yes that workbook. code for going back and forth to another workbook.
    also the modules that are in the file to be imported, has everything that you would need to run the userform. only thing is the generating code on each sheet of the generator. the modules in the generator are for backup in case something happened while i was coding. i have to use this while im programming.
    Last edited by krackaberr; 03-05-2013 at 10:53 AM. Reason: found comment in code

  34. #34
    Forum Guru Norie's Avatar
    Join Date
    02-02-2005
    Location
    Stirling, Scotland
    MS-Off Ver
    Microsoft Office 365
    Posts
    19,597

    Re: Print Sheets based on checkboxes in UserForm, Checkboxes also control another macro

    Can't you just attach the files?

    Like I said I tried to reconstruct the xlsm but when I imported Modules 1-5 I started to get a whole bunch of duplicate declaration compile errors.

    That was caused by the subs in those modules already existing in one of the 9 modules in the workbook.

    I'm not sure what you are saying about shortening the code.

    As well as getting rid of the activating/selecting I added workbook references to ensure the right data was being copied to the right place, I would say that's safer than flipping between workbooks/worksheets/ranges.

  35. #35
    Registered User
    Join Date
    01-31-2013
    Location
    Wisconsin USA
    MS-Off Ver
    Excel 2010
    Posts
    56

    Re: Print Sheets based on checkboxes in UserForm, Checkboxes also control another macro

    the error isnt coming from the workbook... let me see what i can come up with

    are you importing the modules into the userform module folder? remember that this userform is an addin.

    Untitled.png
    Last edited by krackaberr; 03-05-2013 at 11:17 AM.

+ 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