print set up multiple sheets

Topic

New Reply

Hi all,

people here have been great with assistance (and I really am trying to learn so I don’t have to come back here all the time and only ask questions, want to be able to eventually contribute). Mr. Don Guillett has been immensely helpful but I’m stuck. I have a workbook, multiple sheets that are populated from several user forms. The amount of “data” that will be placed on each spreadsheet is varied and differs from sheet to sheet. Users that will be filling in the userforms will want a printed copy of the sheets. I need a way to dynamically format the sheets so that the data can be printed with each individual cell of data having a border. I need this to happen automatically and also need to allowe the user to go back and add data.

Mr. Guillet provided code that will format the “first” sheet correctly for printing.

The code is: (placed in ThisWorkBook)
Private Sub Workbook_BeforePrint(Cancel As Boolean)

Dim myBorders
Dim i As Integer
Application.ScreenUpdating = 0

ActiveSheet.Range(“a1”).CurrentRegion.Select
Selection.Name = _
“‘” & ActiveSheet.Name & “‘!print_area”
myBorders = Array(, xlEdgeLeft, xlEdgeTop, xlEdgeBottom, xlEdgeRight)
For i = 1 To UBound(myBorders)
With Selection.Borders(myBorders(i))
.LineStyle = xlContinuous
.Weight = xlMedium
End With
Next

If Selection.Columns.Count > 1 Then _
Selection.Borders(xlInsideVertical).LineStyle = xlContinuous

If Selection.Rows.Count > 1 Then _
Selection.Borders(xlInsideHorizontal).LineStyle = xlContinuous
Range(“a1”).Select
aplication.ScreenUpdating = 1

End Sub

any suggestions on how to modify so that all sheets will be formatted, and also so that a user can go back and add additional data.

Thanks from a stuggling noob.

Reply | Quote

Replies

Derf,

I put together a sample sheet with data. I am not sure of what kind of data you are using but the following code will adapt. The borders will be created just prior to printing based on the data on the sheet.

HTH,
Maud

Sample sheet
38157-Print1

If you want all the cells in the range of data:
38158-Print2

then use this code:

Code:

Private Sub Workbook_BeforePrint(Cancel As Boolean)
    Cells.Borders.LineStyle = xlNone
    ActiveSheet.UsedRange.Select
    Selection.Borders.LineStyle = xlContinuous
    [a1].Select
End Sub

If you want just the cells with data to have borders:
38159-Print3

then use this code:

Code:

Private Sub Workbook_BeforePrint(Cancel As Boolean)
    Dim rng As Range
    Dim cell As Range
    Cells.Borders.LineStyle = xlNone
    Set rng = ActiveSheet.UsedRange
    For Each cell In rng
        If Not IsEmpty(cell) Then
            cell.Borders.LineStyle = xlContinuous
        End If
    Next cell
End Sub

These codes will add or remove borders in accordance with the cells that have data.

Reply | Quote

Derf,

Here’s a variation that works if the user prints a single sheet or the entire workbook by performing the boarders on all sheets before printing. If a single sheet is requested it remembers to reselect it. If the user selects the “print entire workbook” option on the print menu you’re covered too.

Code:

Option Explicit

Private Sub Workbook_BeforePrint(Cancel As Boolean)

  Dim myBorders As Variant
  Dim i         As Integer
  Dim sht       As Worksheet
  Dim shtActive As Worksheet
  Dim rngLastcell As Range

  Application.ScreenUpdating = False
  Set shtActive = ActiveSheet
  
  For Each sht In ActiveWorkbook.Sheets
     sht.Activate
     
' Method in your code
'     Range("a1").CurrentRegion.Select

' My shot at it since I couldn't remember the method Maud used.
'     Set rngLastcell = [a1].SpecialCells(xlCellTypeLastCell)
'     Set rngLastcell = rngLastcell.Offset(0, -1)
'     Range("a1:" & rngLastcell.Address).Select
' Maud's selection method.
     sht.UsedRange.Select
     
     myBorders = Array(, xlEdgeLeft, xlEdgeTop, xlEdgeBottom, xlEdgeRight)

     For i = 1 To UBound(myBorders)
        With Selection.Borders(myBorders(i))
            .LineStyle = xlContinuous
            .Weight = xlMedium
        End With
     Next i

     If Selection.Columns.Count > 1 Then _
       Selection.Borders(xlInsideVertical).LineStyle = xlContinuous

     If Selection.Rows.Count > 1 Then _
       Selection.Borders(xlInsideHorizontal).LineStyle = xlContinuous

     [a1].Select
  Next sht
  
  shtActive.Activate
  Application.ScreenUpdating = True

End Sub

Note: the selection method in your code would not cover non-contiguous areas, see the attached pdf.
Results PDF file:
38161-VBA-Excel-Format-All-Sheets-Before-Print
Test file:
38162-VBA-Excel-Format-All-Sheets-Before-Print

HTH :cheers:

P.S. you could incorporate Maud’s code for clearing the formatting at before the formatting in this routine.

May the Forces of good computing be with you!

RG

PowerShell & VBA Rule!
Computer Specs

Reply | Quote

Thanks all for the help. However I guess I didn’t make myself entirely clear. I need borders around each cell within the range, whether or not the cell contains data. In trying to use Maud’s two examples, in one, only the cells that acutally contained data had borders, while in the other, it formatted the sheet to the maximum number of rows, rather than the range. I believe this happens because there is a formula (hidden at the top of one of the columns that sums all the values entered). I don’t kow how to make the formula only reference to as far as the inputted data goes. In other words if there is data in column 3 all the way to row 10, it would sum that data, or if there was data all the way to row 563, it would sum all that data. It all depends on what the “user” enters. Hope that makes it a little clearer than mud. As I said I’m a relative noob to using VBA, but want to learn. I think my rationale for why Maud’s bordering on each cell formats to the end of the maximum amount of rows makes sense, but could likely still be incorrect.

So in summary I need every cell (within the range that data has been inputted) to have a border whether or not it contains data and for this to happen automatically when someone goes to print the worksheet that the data has been transferred to through the userform. I have attached a short spreadsheet to show the columns and an example of how not all cells will have data within them. 38229-sample-data

If any additional help can be provided it would be greatly appreciated. Between all the advice I’ve received here and some of my own learnings I’m relatively close to a useable document.

Thanks again.

Reply | Quote

Derf,

Seems to work for me unless I still don’t understand what you are asking for.

Output w/your data: 38231-Derfacnauck-sample-data

Output w/Added row: 38232-Derfacnauck-sample-data2

:cheers:

May the Forces of good computing be with you!

RG

PowerShell & VBA Rule!
Computer Specs

Reply | Quote

Derf,

As to the formula to calculate what ever cells are used in Col 7 (G) you need to use a Dynamic Range Name as such:
BEGross: [noparse]=OFFSET(Sheet1!R3C7,0,0,COUNTA(Sheet1!R3C7:R10003C7),1)[/noparse]
As long as you don’t have more than 10,000 rows this will work just fine.
In Cell G2 (R2C7) the formula would be: [noparse]=Sum(BEGross)[/noparse].
38230-derf1

As to the cell borders I’m not sure what the problem is with the solution I provided. I’ll test it on the data you provided and post back. HTH :cheers:

May the Forces of good computing be with you!

RG

PowerShell & VBA Rule!
Computer Specs

Reply | Quote

Derf,

My first code uses the UsedRange method. UsedRange is dependent not only by data but also dependent on formatting, comments, etc. On your sheet, you may have cleared some cells of data but the cell still contains some formatting. Example: You deleted the value in cell D75 which was formatted as bold.

Reply | Quote

Thanks to both Maudibe & RetiredGeek for all their help and input. I’m actually begining to understand things a little better. I will let you know how things work out when I apply your advice as provided. Thanks again. It is people like you and Donald Guillett who previously helped that help to make the lounge R O C K! :thewave::thewave:

Reply | Quote

Hi all. Thanks again to those that have helped out. :fanfare:

In reviewing my posts, perhaps I haven’t provided quite enough information. As a wise individual said to me…. provide as much information as possible and don’t assume anything. What may seem obvious to you, may be a complete mystery to others. As a result I am posting again with what I hope is complete information and that my questions might help others learn.

Situation. Our non profit organization needs to collect data from a number of various organizations. We are attempting to do this by providing each organization with a workbook, (which will be provided to them in an email though MailChimp that will contain a link so that they can download the “blank” workbook) containing userforms that they are asked to complete and submit back to us. Each organization will have their own workbook and all data is kept separate from any other organization. I will end up with about 60 individual workbooks.

for each workbook there are 4 or 5 userforms that will be utilized, transferring data to 4 separate worksheets. The user that will be inputting the data will want a printed copy of what shows on the worksheet. The userforms are working correctly (still need to make a few minor adjustments), but what needs to happen each user will select print and based on selecting print for each worksheet the inputted data (even blank cells with the data), will be printed with all cells within the “range of data” will be printed with borders. I’m hoping that makes sense. A number of people have provided solutions, but for some reason, when I attempt to utilize their solutions, I don’t end up with borders around each cell for each worksheet. Users will likely print by worksheet and not print the workbook. Code has been inserted in “this workbook” to try and achieve what I am hoping to do with printing. It should also be noted that users will likely go back several times and add data to the various worksheets. I have attached the workbook as it currently exists and would appreciated any help that may be provided. It is a zip file through 7zip. Hope it works :crossfingers:

Please note. There are still part of the instructions to be added to take the user to the appropriate userform.

Thanks again for the great support provided here in the forum.

Derf

Reply | Quote