vba to automatically adjust “print” and include borders

Topic

New Reply

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). 37915-print-macro

As always, thanks for any help and suggestions.

Fred

Reply | Quote

Replies

Sub bordersaround()
With Selection.Borders
.LineStyle = xlContinuous
.Weight = xlThick
End With
End Sub

Reply | Quote

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

Reply | Quote