Results 1 to 9 of 9
  1. #1
    New Lounger
    Join Date
    Apr 2014
    Posts
    15
    Thanks
    7
    Thanked 0 Times in 0 Posts

    print set up multiple sheets

    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.

  2. #2
    Gold Lounger Maudibe's Avatar
    Join Date
    Aug 2010
    Location
    Pa, USA
    Posts
    2,631
    Thanks
    115
    Thanked 645 Times in 589 Posts
    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
    Print1.png

    If you want all the cells in the range of data:
    Print2.png

    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:
    Print3.png

    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.
    Last edited by Maudibe; 2014-10-15 at 23:09. Reason: match code with the correct image

  3. #3
    Super Moderator RetiredGeek's Avatar
    Join Date
    Mar 2004
    Location
    Manning, South Carolina
    Posts
    9,433
    Thanks
    371
    Thanked 1,456 Times in 1,325 Posts
    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:
    VBA - Excel - Format All Sheets Before Print.pdf
    Test file:
    VBA - Excel - Format All Sheets Before Print.xlsm

    HTH

    P.S. you could incorporate Maud's code for clearing the formatting at before the formatting in this routine.
    Last edited by RetiredGeek; 2014-10-15 at 21:19.
    May the Forces of good computing be with you!

    RG

    PowerShell & VBA Rule!

    My Systems: Desktop Specs
    Laptop Specs

  4. #4
    New Lounger
    Join Date
    Apr 2014
    Posts
    15
    Thanks
    7
    Thanked 0 Times in 0 Posts

    a little more informaiton

    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. sample data.xlsx



    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.

  5. #5
    Super Moderator RetiredGeek's Avatar
    Join Date
    Mar 2004
    Location
    Manning, South Carolina
    Posts
    9,433
    Thanks
    371
    Thanked 1,456 Times in 1,325 Posts
    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: =OFFSET(Sheet1!R3C7,0,0,COUNTA(Sheet1!R3C7:R10003C 7),1)
    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: =Sum(BEGross).
    derf1.JPG

    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
    May the Forces of good computing be with you!

    RG

    PowerShell & VBA Rule!

    My Systems: Desktop Specs
    Laptop Specs

  6. The Following User Says Thank You to RetiredGeek For This Useful Post:

    Derfacnauck (2014-10-24)

  7. #6
    Super Moderator RetiredGeek's Avatar
    Join Date
    Mar 2004
    Location
    Manning, South Carolina
    Posts
    9,433
    Thanks
    371
    Thanked 1,456 Times in 1,325 Posts
    Derf,

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

    Output w/your data: Derfacnauck-sample data.pdf

    Output w/Added row: Derfacnauck-sample data2.pdf

    May the Forces of good computing be with you!

    RG

    PowerShell & VBA Rule!

    My Systems: Desktop Specs
    Laptop Specs

  8. #7
    Gold Lounger Maudibe's Avatar
    Join Date
    Aug 2010
    Location
    Pa, USA
    Posts
    2,631
    Thanks
    115
    Thanked 645 Times in 589 Posts
    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.

  9. The Following User Says Thank You to Maudibe For This Useful Post:

    Derfacnauck (2014-10-24)

  10. #8
    New Lounger
    Join Date
    Apr 2014
    Posts
    15
    Thanks
    7
    Thanked 0 Times in 0 Posts

    Thanks to Maud and Retired Geek and also Donald Guillett for all the help

    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!

  11. #9
    New Lounger
    Join Date
    Apr 2014
    Posts
    15
    Thanks
    7
    Thanked 0 Times in 0 Posts

    updated... still needing help

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

    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

    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
    Attached Files Attached Files

Posting Permissions

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