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

    vba to automatically adjust "print" and include borders

    Hi all.

    Did some looking but couldn't find an answer in the forums, apologies if it has been answered previously and also if this should be placed in a different forum.

    I have a user form that places information onto a summary worksheet. Once a user has finished inputting all of their data they will want to print the summary worksheet. Need to automate the process as much as possilbe and currently have a macro that automatically adjusts the print area to be reflective of the data inputted (eg. if only 5 rows have date, it will only print those 5 rows, if there are 125 rows, it will print the 125 rows. What needs to be added is to somehow indicate that all cells should have borders (like you can do in the "home" Font area - all borders selection in excel) Becasue of the nature of the summary sheet, it is important visually to have the borders in place, but I am at a loss. Still learning about VBA but should be considered a noob. I have attached the vba code that I am currently utilizing (stolen or borrowed in part from many different sources). print macro.txt

    As always, thanks for any help and suggestions.

    Fred

  2. #2
    3 Star Lounger Supershoe's Avatar
    Join Date
    Apr 2014
    Location
    Austin, TX
    Posts
    252
    Thanks
    1
    Thanked 36 Times in 34 Posts
    Sub bordersaround()
    With Selection.Borders
    .LineStyle = xlContinuous
    .Weight = xlThick
    End With
    End Sub

  3. The Following User Says Thank You to Supershoe For This Useful Post:

    Derfacnauck (2014-09-18)

  4. #3
    3 Star Lounger Supershoe's Avatar
    Join Date
    Apr 2014
    Location
    Austin, TX
    Posts
    252
    Thanks
    1
    Thanked 36 Times in 34 Posts
    Although I might do it differently here is the combined code you need

    Private Sub Workbook_BeforePrint(Cancel As Boolean)
    With Range(Cells(Rows.Count, 1).End(xlUp)(2, 1), Cells(18, Columns.Count).End(xlToLeft))
    .Name = "'" & ActiveSheet.Name & "'!Print_Area"
    .Borders.LineStyle = xlContinuous
    .Borders.Weight = xlThick
    End With
    End Sub

Tags for this Thread

Posting Permissions

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