Draw a border at the bottom of the page on a page-by-page basis in a macro

Overview

When printing, I think that it is often used as a frame of the paper to draw a border on the header and all fours. However, if the paper is a single sheet, you only need to draw the border manually, but if it becomes more than one page, the border will not appear at the bottom of each page. There is also a manual re-pull support, but if you add or remove lines in the middle, you will have to re-draw the border on all pages.

This section describes how to use macros to automatically draw borders at the bottom of every page.

Confirmed version of the operation

Supported Excel version

• 2010
• 2007
• 2003
• (It may work with other versions, but it is not confirmed.)

Make sure Excel version

• 2010
• 2007
• 2003

Contents

If you print a single page, you can draw a border at the bottom of the line to print without any problems.

However, if you have more than one page, you can draw a border only at the bottom of the line, but the border will not appear on the bottom row of each page. If you want the border to appear on each page, you must manually apply a border to the page change.

However, if you add or remove a line in the middle, the line of the page break and the line of the border are cut off, so it is troublesome because it becomes a re-draw again.

So I'm going to use a macro to draw a border on a line of page change in bulk.

It doesn't matter what way to start it because you can run it with a built-in macro, but in the sample, I place a button on a separate sheet and draw a border on the page break when I press the button.

If you want to distribute something that already contains macros, you can just pass the file as it is, but if you want to assemble the macro yourself, set excel so that you can assemble the macro. For information about how to do this, see Display the Development tab to enable form placement and VBA program development .

When you place a button, the macro registration dialog is displayed, so let's create it so that you can run the click event by pressing the "New" button.

We're not going to go into too much detail about macros or Visual Basic, so check it out separately.

When the Visual Basic editor opens, add the following code: You can draw a border on a row of page changes on a specified sheet by calling this function.

I have a comment about the description of the function, so please check it.

'【概要】
'　改ページプレビューに合わせて、ページの下に黒の羅線を引きます。
'
'【引数】
'　targetSheet    : 改ページラインの描画対象シート
'  lineStyle      : 罫線のスタイル。XlLineStyle 列挙型から指定。
'  lineWeight     : 罫線の太さ。XlBorderWeight 列挙型から指定。
'  lineColorIndex : 罫線の色。XlColorIndex 列挙型から指定、またはカラー パレットのインデックス値を指定。
'
'【備考】
'  一度罫線を設定すると戻せないので注意。
Private Sub SetHPageBreakLines(targetSheet As Worksheet, _
                               Optional lineStyle As XlLineStyle = xlContinuous, _
                               Optional lineWeight As XlBorderWeight = xlThin, _
                               Optional lineColorIndex As XlColorIndex = xlAutomatic)

  ' 列の最大位置取得
  Dim maxColumn As Integer
  maxColumn = targetSheet.UsedRange.Columns.Count
    
  ' 各ページ描画
  For Each hpg In targetSheet.HPageBreaks
    Dim row As Integer
    Dim targetCell As Range
    row = hpg.Location.row - 1
    Set targetCell = targetSheet.Range(targetSheet.Cells(row, 1), targetSheet.Cells(row, maxColumn))
    
    ' 罫線のプロパティを設定する
    With targetCell.Borders(xlEdgeBottom)
      .lineStyle = lineStyle
      .Weight = lineWeight
      .ColorIndex = lineColorIndex
    End With
  Next
    
End Sub

The click process of the button is as follows. If you specify a sheet for the SetHPageBreakLines function you created, you can draw a border on that sheet. If you also want to specify a border style, you can specify it with the remaining arguments.

Because it is a sample, the sheet name is specified directly in the macro, but I think that it is more general to make it possible to write the sheet name in the cell and to be able to refer to the cell.

'【概要】
'  ボタンクリックイベント
Sub ボタン1_Click()
  ' 対象シート
  Dim syoriSheet As Worksheet
  Set syoriSheet = Worksheets("ドキュメント")

  ' 罫線を設定
  Call SetHPageBreakLines(syoriSheet)

  ' 下は線のスタイル指定
  'Call SetHPageBreakLines(syoriSheet, xlContinuous, xlThin, xlAutomatic)

  MsgBox syoriSheet.HPageBreaks.Count & "ページ分の線の設定が完了しました。"
End Sub

When you run the macro, you can see that the bottom row of each page is bordered. You can also check it in Print Preview.

As a caveat, I think that it is good to run the macro only when printing because the border set when the macro is executed once cannot be returned.