Results 1 to 9 of 9
  1. #1
    New Lounger
    Join Date
    Aug 2002
    Location
    Ontario, Canada
    Posts
    16
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Print Worksheets (VBA Excel)

    I want to be able to select the worksheets to print. The attached program works with the exception that it always prints the last worksheet even if it hasn't been checked. Can anyone help? <img src=/S/confused.gif border=0 alt=confused width=15 height=20>
    Attached Files Attached Files

  2. #2
    New Lounger
    Join Date
    Aug 2002
    Location
    Ontario, Canada
    Posts
    16
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Print Worksheets (VBA Excel)

    Here is a text file of the above code.
    Attached Files Attached Files

  3. #3
    4 Star Lounger
    Join Date
    Sep 2002
    Location
    Stafford, Staffordshire, England
    Posts
    585
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Print Worksheets (VBA Excel)

    I've rearranged a couple of lines of your code and those lines which I don't think are necessary I've memoed out.
    It appears to work now. See attachment.

    Rob
    Attached Files Attached Files
    <font color=blue><font face="Script MT Bold"><big>Rob</big></font face=script></font color=blue>

  4. #4
    New Lounger
    Join Date
    Aug 2002
    Location
    Ontario, Canada
    Posts
    16
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Print Worksheets (VBA Excel)

    Hi Rob,

    I tried your changes and found that only sheet 3 will print regardless of what is checked.

    Mary

  5. #5
    5 Star Lounger
    Join Date
    Jul 2002
    Location
    Toronto, Ontario, Canada
    Posts
    1,139
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Print Worksheets (VBA Excel)

    Mary,

    The problem is with this chunk of code towards the bottom.

    <pre> PrintDlg.Buttons("Button 2").BringToFront
    PrintDlg.Buttons("Button 3").BringToFront
    CurrentSheet.Activate
    If SheetCount <> 0 Then
    If PrintDlg.Show Then</pre>


    Using CurrentSheet.Activate activates seelcts the last sheet all the time.

    So if you comment out that line:

    <pre> PrintDlg.Buttons("Button 2").BringToFront
    PrintDlg.Buttons("Button 3").BringToFront
    <font color=448800>'CurrentSheet.Activate</font color=448800>
    If SheetCount <> 0 Then
    If PrintDlg.Show Then</pre>


    The it should work as expected. If you want me to post the whole routine, just let me know.
    --
    Bryan Carbonnell - Toronto <img src=/S/flags/Ontario.gif border=0 alt=Ontario width=30 height=18> <img src=/S/flags/Canada.gif border=0 alt=Canada width=30 height=18>
    Unfortunately common sense isn't so common!!
    Visit my website for useful Word, Excel and Access code, templates and Add-Ins

  6. #6
    New Lounger
    Join Date
    Aug 2002
    Location
    Ontario, Canada
    Posts
    16
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Print Worksheets (VBA Excel)

    Bryon

    Almost but not quite.

    It is now printing the checkbox window first then the selected sheet.

    Mary

  7. #7
    5 Star Lounger
    Join Date
    Jul 2002
    Location
    Toronto, Ontario, Canada
    Posts
    1,139
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Print Worksheets (VBA Excel)

    Quite right you are. I guess I need to do better checking before I post replies <img src=/S/grin.gif border=0 alt=grin width=15 height=15>

    The problem is that when you do the CurrentSheet.Activate, you are selecting the last sheet.

    Then when you loop through the checkboxes on the form, you are adding to the selection, which includes the last sheet. So what you have to do, is replace the current selection when you select the worksheet that is checked, and just add to it when you get to the rest. TO do that you will need to add a boolean flag to indicate if you have selected one yet. Add:

    <pre>Dim bolFirstOne As Boolean</pre>


    at the top and then replace:

    <pre>If cb.Value = xlOn Then
    Worksheets(cb.Caption).Select Replace:=False
    End If</pre>


    with:

    <pre>If cb.Value = xlOn Then
    <font color=448800>'Check to see if we have selected atleast one sheet yet</font color=448800>
    If bolFirstOne = False Then
    <font color=448800>'No, not yet
    ' Replace the current seelction</font color=448800>
    Worksheets(cb.Caption).Select Replace:=True
    <font color=448800>'Set the flag to indicate now we have set the first one</font color=448800>
    bolFirstOne = True
    Else
    <font color=448800>'Now that we have selected the first one, just add to the selection</font color=448800>
    Worksheets(cb.Caption).Select Replace:=False
    End If
    End If</pre>


    You can leave the CurrentSheet.Activate line there, or you can comment it out. It doesn't matter. I tested it with it uncommented. And yes I did some more testing before posting this <img src=/S/grin.gif border=0 alt=grin width=15 height=15>
    --
    Bryan Carbonnell - Toronto <img src=/S/flags/Ontario.gif border=0 alt=Ontario width=30 height=18> <img src=/S/flags/Canada.gif border=0 alt=Canada width=30 height=18>
    Unfortunately common sense isn't so common!!
    Visit my website for useful Word, Excel and Access code, templates and Add-Ins

  8. #8
    5 Star Lounger
    Join Date
    Jul 2002
    Location
    Toronto, Ontario, Canada
    Posts
    1,139
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Print Worksheets (VBA Excel)

    Glad that it finally worked <img src=/S/smile.gif border=0 alt=smile width=15 height=15>
    --
    Bryan Carbonnell - Toronto <img src=/S/flags/Ontario.gif border=0 alt=Ontario width=30 height=18> <img src=/S/flags/Canada.gif border=0 alt=Canada width=30 height=18>
    Unfortunately common sense isn't so common!!
    Visit my website for useful Word, Excel and Access code, templates and Add-Ins

  9. #9
    New Lounger
    Join Date
    Aug 2002
    Location
    Ontario, Canada
    Posts
    16
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Print Worksheets (VBA Excel)

    It Works! <img src=/S/clapping.gif border=0 alt=clapping width=19 height=23> Thanks

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

Posting Permissions

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