asked on

MS Excel 2007 - Multiple selection of Print Areas within a worksheet

I currently have a series of command buttons that initiate the printing of print areas within a single worksheet.

The issue is that there are in excess of 12 command buttons that is both unappealing and lacks usability.

The current approach taken is shown below:

Sub MyPrint()
CurPrtArea = ActiveSheet.PageSetup.PrintArea
If Range("linkcellfromCBox") = True Then
myPrtArea = "B101:D131"
With ActiveSheet.PageSetup
.PageSetup.PrintArea = myPrtArea
.FitToPagesWide = 1
.FitToPagesTall = 1
.PrintOut
End With
End If
'
'~~> And So On
'
End Sub

I have been trying to create a different approach using a table and/or checkboxes to enable the user to select more than one print area at a time and have all selections printed automatically.

The print area is defined by the page width with all bar one needing to print to one A4 page in width BUT all need to be dynamic in determination of their length.

The solution needs to dynamically determine how many pages “tall” the print is to be. The print area should not include any rows where the first column cell has no data.

The print areas are:

Data entry                   A12:AB12
Methodology             CZ3000:DC3000
Coverage Summary             DD3103:DE3103
Classification Summary             DF3204:DR3204
Public Holidays             DS3408:DU3408
Payrate Chronology             DV3511:EC3511
Comments                   ED3615:EG3615
Penalty Summary             EH3718:FE3718
Allowances                   FF4718:FR4718
Other Conditions             FS5772:FU5926
Annual Leave             FV5926:GJ5926
Personal Leave             GK6155:GZ6155
Parental Leave             HA3695:HT3695
Long Service Leave             HU6622:IM6622
PILN                   IN6841:JB6841
Redundancy             JC6940:JQ6940
Disaggregation of Super from Base Rate       JR7039:KP7039
Disaggregation of Annual and Personal Leave from Base Rate       KQ7120:LP7120
Calculation of Commission                   LQ7200:LV7200
Calculation of Superannuation                   LW7240:MD7240

Any suggestions or solutions would be appreciated.

Andross9

Arno Koster

If I understand you correctly, your question is if it is possible to determine the height of a printout containing a specific selection according to several rules :

- a printout consists of one or multiple cell ranges, to be selected by the user
- the first column of any row in the printout contains data
- the printout contains a number of columns (more than one)
- printout must be sized such that all columns of a row are printed on a single page

is this correct ?

If so, a smart approach would be to hide all rows & columns that do not belong to the selection and define the printout to fit on 1 (width) by 999 (height) pages.
this way you only print what is required without loosing data / formulae / formatting etc.
By restricting the printout to 1 page width, you make sure that everything is automatically scaled to the required size.

Andross9

ASKER

Thanks for that but I havent explained myself clearly enough, so will rework the question.

1. I currently have a series of command buttons on a wage assessment spreadsheet that each initiate the printing of a single print area within a single worksheet.

The issue is that there are in excess of 20 command buttons, one for each of these print areas, that is both unappealing and lacks usability.

With the current approach, a user may need to print most of the print areas and has to individually click on each command button to do so.

I have been trying to create a different approach to the print selection - using a table and/or checkboxes to enable the user to select more than one print area at a time and have all selections printed automatically.

The current approach for each command button is shown below:

Sub MyPrint()
CurPrtArea = ActiveSheet.PageSetup.PrintArea
If Range("linkcellfromCBox") = True Then
myPrtArea = "B101:D131"
With ActiveSheet.PageSetup
.PageSetup.PrintArea = myPrtArea
.FitToPagesWide = 1
.FitToPagesTall = 1
.PrintOut
End With
End If
'
'~~> And So On
'
End Sub

I have been trying to create a different approach using a table and/or checkboxes to enable the user to select more than one print area at a time and have all selections printed automatically.

I have tried to represent this in the attached file "View of what I think it might look like?" and have included descriptions and print locations in "Printing Area Locations".

I am hoping that it is possible to include a "select all" to enable the users to select all print areas for printing with one click if so desired.

I would appreciate any help in developing an approach to this question.

2.. Dynamically setting print areas.

As I have been asked to separate the questions - I have asked a second question on print area setting at ID: 26990592
Printing-Area-Locations.xlsx Printing-Area-Locations.xlsx Printing-Checkboxes.docx

Regards,

Andross9

Arno Koster

to start with the checkboxes,

if you paste this code in a module, you can automate the number of checkboxes that are placed in Sheet1.
The last checkbox, select all, will either select or clear all other checkboxes on the sheet.

Sub remove_checkboxes()
Dim cb As CheckBox

    For Each cb In Sheets("Sheet1").CheckBoxes
        cb.Delete
    Next cb
    
End Sub

Sub add_checkboxes()
Dim pos As Integer
Dim cb As CheckBox

    '-- add selection checkboxes
    For pos = 2 To 21
        '-- checkboxes.add(left, top, width, height)
        Sheets("Sheet1").CheckBoxes.Add(12, Sheets("Sheet1").Rows(pos).Top, 24, Sheets("Sheet1").Rows(pos).Height).Text = ""
    Next pos
    
    '-- add 'select all' checkbox
    Set cb = Sheets("Sheet1").CheckBoxes.Add(12, Sheets("Sheet1").Rows(23).Top, 24, Sheets("Sheet1").Rows(23).Height)
    cb.Text = ""
    cb.OnAction = "'select_checkboxes""" & cb.Name & """'"
    
End Sub

Sub select_checkboxes(cbName As String)
Dim cb As CheckBox

    For Each cb In Sheets("Sheet1").CheckBoxes
        If cb.Name <> cbName Then cb.Value = Sheets("Sheet1").CheckBoxes(cbName).Value
    Next

End Sub

Open in new window

Arno Koster

to add a button which prints all the selected ranges, use this code in the same (or a different) module :

Sub add_button()
Dim target As Range
Dim btn As Button

    With Sheets("Sheet1")
        Set target = .Range("C23")
        Set btn = .Buttons.Add(target.Left, target.Top, target.Width, target.Height)
        btn.Caption = "Print Selection"
        btn.OnAction = "print_selection"
        Set btn = Nothing
    End With

End Sub

Sub print_selection()
Dim cb As CheckBox
Dim descr As String
Dim myPrtArea As String

    For Each cb In Sheets("Sheet1").CheckBoxes
        If cb.Value = 1 Then
            descr = Trim(cb.TopLeftCell.Offset(0, 1))
            
            myPrtArea = cb.TopLeftCell.Offset(0, 2)
            CurPrtArea = Sheets("Sheet1").PageSetup.PrintArea
            With Sheets("Sheet1")
                .PageSetup.PrintArea = myPrtArea
                .PageSetup.FitToPagesWide = 1
                .PageSetup.FitToPagesTall = 1
                .PrintOut
            End With
        End If
    Next cb
   
End Sub

Open in new window

Andross9

ASKER

Thanks akoster but I am struggling to put this all together.

Are you able to show me a finsihed product in an attached file?

Have recently completed some VBA training but still struggling with the context of what, how, when and why?

ASKER CERTIFIED SOLUTION

Arno Koster

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Andross9

ASKER

That makes sense, thanks

Do I need to change printer settings for landscape printing or change the code

Andross9

ASKER

Thanks for your patience

Really appreciate the step by step process

Helps with context.

Arno Koster

you can add to the VBA code

PageSetup.Orientation = xlLandscape
or
PageSetup.Orientation = xlPortrait

Open in new window