Print Worksheets (VBA Excel)

Topic

New Reply

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?

Reply | Quote

Replies

Here is a text file of the above code.

Reply | Quote

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

Reply | Quote

Hi Rob,

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

Mary

Reply | Quote

Mary,

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

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

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

So if you comment out that line:

    PrintDlg.Buttons("Button 2").BringToFront
    PrintDlg.Buttons("Button 3").BringToFront
    'CurrentSheet.Activate
    If SheetCount  0 Then
        If PrintDlg.Show Then

The it should work as expected. If you want me to post the whole routine, just let me know.

Reply | Quote

Bryon

Almost but not quite.

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

Mary

Reply | Quote

Quite right you are. I guess I need to do better checking before I post replies

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:

Dim bolFirstOne As Boolean

at the top and then replace:

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

with:

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

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

Reply | Quote

It Works! Thanks

Mary

Reply | Quote

Glad that it finally worked

Reply | Quote