ページ制御　①ボタン・ページ指定

YouTube　VBA
2023.08.19

変数　ページ制御するために、必要なものは？

各変数の宣言

Public 最終行数 As Long　　　　　　 ‘データの総行数
Public ページ表示行数 As Long 　 　 ‘１ページに表示する行数
Public 全ページ数 As Long 　　　　 ‘表示するページの総数
Public 最初データ番号 As Long 　 　 ‘表示するページの最初のデータの位置
Public 最後データ番号 As Long 　 　 ‘表示するページの最後のデータの位置
Public 表示ページ As Long 　　　　 ‘表示するページ
Public Data() As Variant 　　　　　 ‘顧客データを格納する配列（動的配列で宣言）

Public変数とは…
プロジェクト全体でどのモジュールからでも使えるグローバルな変数

ページ制御の必要な各変数の値を求める

最終行数 = TestData.Cells(Rows.Count, 1).End(xlUp).Row – 1
表示ページ = 1
ページ表示行数 = 一覧表.Range(“J17”)

全ページ数 = WorksheetFunction.RoundUp(最終行数 / ページ表示行数, 0)
ExcelのRoundUp関数を使って、最終行数 / ページ表示行数 の小数点以下を切り上げます
例）　12.3ページ→13ページ

ReDim Data(最終行数, 7)
顧客データを格納する配列を 最終行数×７列で宣言し直す
（最終行数がわかったので、配列の大きさを決定できる）


シートをオブジェクト名TestDataで指定

TestData.Range(“A2”)は、
Worksheets(”テストデータ”).Range(“A2”)
と書くのと同じ
（シート名が変わってもプログラムをさわらなくてOK）


シートをオブジェクト名一覧表で指定

一覧表.Range(“J17”)は、
Worksheets(”顧客データ一覧”).Range(“J17”)
と書くのと同じ

あとは、表示するデータの位置だけだね！

最初データ番号 = 1
最後データ番号 = ページ表示行数
最終行数 > ページ表示行数 の場合は、最後データ番号 = 最終行数

Sub 一覧表表示()
    
    表示ページ = 1
    ページ表示行数 = 一覧表.Range("J17")
    最終行数 = TestData.Cells(Rows.Count, 1).End(xlUp).Row - 1
    全ページ数 = WorksheetFunction.RoundUp(最終行数 / ページ表示行数, 0)
    
    '配列を使う場合
    ReDim Data(最終行数, 7)
    Data = TestData.Range("a2", "g" & (最終行数 + 1))
    
     'どこからどこまで表示
    最初データ番号 = 1
    If 最終行数 > ページ表示行数 Then
        最後データ番号 = ページ表示行数
    Else
        最後データ番号 = 最終行数
    End If
        
    Dim i As Long, j As Long
    Dim Gyo As Long
    Gyo = 4
    For i = 最初データ番号 To 最後データ番号
        For j = 1 To 7
            '一覧表.Cells(Gyo, j + 1) = TestData.Cells(i + 1, j)
            一覧表.Cells(Gyo, j + 1) = Data(i, j)        '配列を使う場合
        Next j
        Gyo = Gyo + 1
    Next i
End Sub

実行してみましょう！
ForNextを以下の様にすることもできます（配列を用いた場合）
罫線も引いてみましょう

'１行いっぺんに書き出す場合
    For i = 最初データ番号 To 最後データ番号
        一覧表.Cells(Gyo, 2).Resize(1, 7) = WorksheetFunction.Index(Data, i)
        Gyo = Gyo + 1
    Next i
    
    '罫線を引く
    With 一覧表.Range("b3").CurrentRegion.Borders
        .LineStyle = xlContains
        .ColorIndex = 1
    End With

End Sub

表示データの削除

一覧表.Range(“b3”).CurrentRegion.Offset(1, 0).Clear

ページを表示する前に、前の表示データを削除する必要があります
罫線を引いた範囲と同じ範囲を１行下げた位置からClearします
見出しを消さないためにOffset(1, 0)しています

　　Dim i As Long, j As Long
    Dim Gyo As Long
    
    '前のデータを削除
    一覧表.Range("b3").CurrentRegion.Offset(1, 0).Clear

    Gyo = 4

Hyoujiプロシージャ作成

Hyoujiプロシージャ作成
1ページ表示する部分をまとめて、Hyoujiプロシージャにしましょう！
Hyoujiは、最初データ番号 から 最後データ番号まで表示するプロシージャです

    Sub Hyouji()
 
    Dim i As Long, j As Long
    Dim Gyo As Long
    
    '前のデータを削除
    一覧表.Range("b3").CurrentRegion.Offset(1, 0).Clear

    Gyo = 4
'    For i = 最初データ番号 To 最後データ番号
'        For j = 1 To 7
'            '一覧表.Cells(Gyo, j + 1) = TestData.Cells(i + 1, j)
'            一覧表.Cells(Gyo, j + 1) = Data(i, j)
'        Next j
'        Gyo = Gyo + 1
'    Next i
    
    For i = 最初データ番号 To 最後データ番号
        一覧表.Cells(Gyo, 2).Resize(1, 7) = WorksheetFunction.Index(Data, i)
        Gyo = Gyo + 1
    Next i
    
    '罫線を引く
    With 一覧表.Range("b3").CurrentRegion.Borders
        .LineStyle = xlContains
        .ColorIndex = 1
    End With
    
End Sub

一覧表表示プロシージャは、Hyouji を呼び出します
Call Hyouji

  'どこからどこまで表示
    最初データ番号 = 1
    If 最終行数 > ページ表示行数 Then
        最後データ番号 = ページ表示行数
    Else
        最後データ番号 = 最終行数
    End If
    
    Call Hyouji
    
End Sub

Workbook_Openを作成する

Workbook_Openを作成する
ThisWorkBookに、ブックが開いたとき実行されるプロシージャWorkbook_Openを作成する
ブックが開いたときに、1ページ目を表示する
Workbook_Openは一番初めに実行されるプロシージャです

Sub Workbook_Open()

    ページ表示行数 = 20                             '初期値は20に指定
    一覧表.Range("J17") = ページ表示行数   　　　　　'J17セルの値を更新
    表示ページ = 1
    最終行数 = TestData.Cells(Rows.Count, 1).End(xlUp).Row - 1
    全ページ数 = WorksheetFunction.RoundUp(最終行数 / ページ表示行数, 0)
    
    '配列を使う場合
    ReDim Data(最終行数, 7)
    Data = TestData.Range("a2", "g" & (最終行数 + 1))
        
    'どこからどこまで表示
    最初データ番号 = 1
    If 最終行数 > ページ表示行数 Then
        最後データ番号 = ページ表示行数
    Else
        最後データ番号 = 最終行数
    End If
    
    Call Hyouji
    
End Sub

２ページ目以降も表示できるように…

２ページ目以降も表示できるように、一覧表表示プロシージャを変更しましょう

最初データ番号 = (表示ページ – 1) * ページ表示行数 + 1
最後データ番号 = 表示ページ * ページ表示行数
　
最終ページはページの途中までしかデータがないので、
最後データ番号 = 最終行数

　　　　　　　　　　　　最終ページには注意してね！

'どこからどこまで表示
    最初データ番号 = (表示ページ - 1) * ページ表示行数 + 1
    If 最終行数 > 表示ページ * ページ表示行数 Then
        最後データ番号 = 表示ページ * ページ表示行数
    Else
        最後データ番号 = 最終行数
    End If

各ボタンのプロシージャを作成

次のページ
表示ページ = 表示ページ + 1だけど、
次のページがないときは、Exit　Sub
あとは同じ…

Sub 次のページ()

    If 表示ページ >= 全ページ数 Then Exit Sub
    表示ページ = 表示ページ + 1
    
    最初データ番号 = (表示ページ - 1) * ページ表示行数 + 1
    If 最終行数 > 表示ページ * ページ表示行数 Then
        最後データ番号 = 表示ページ * ページ表示行数
    Else
        最後データ番号 = 最終行数
    End If
    
    Call Hyouji
    
End Sub

前のページ
1ページ目を表示しているときは、前のページがないので　Exit　Sub
表示ページ = 表示ページ ー 1
最終ページは考えなくていいので、
最後データ番号 = 表示ページ * ページ表示行数

Sub 前のページ()

    If 表示ページ <= 1 Then Exit Sub
    表示ページ = 表示ページ - 1
    
    最初データ番号 = (表示ページ - 1) * ページ表示行数 + 1
    最後データ番号 = 表示ページ * ページ表示行数
    
    Call Hyouji
    
End Sub

最終ページ
最終ページを表示しているときは、Exit　Sub
あとは同様に…

Sub 最終ページ()

    If 表示ページ >= 全ページ数 Then Exit Sub
    表示ページ = 全ページ数
    
    最初データ番号 = (表示ページ - 1) * ページ表示行数 + 1
    If 最終行数 > 表示ページ * ページ表示行数 Then
        最後データ番号 = 表示ページ * ページ表示行数
    Else
        最後データ番号 = 最終行数
    End If
    
    Call Hyouji
    
End Sub

先頭ページ
1ページ目を表示しているときは、Exit Sub
表示ページ = 1、最初データ番号 = 1
1ページ目でない（1ページ目のときExit Subしている）のだから、
最後データ番号 = ページ表示行数　のみでOKです
最終行数の判断いらないね！（以下は動画のとおりです）

Sub 先頭ページ()
     
    If 表示ページ <= 1 Then Exit Sub
    表示ページ = 1
    
    最初データ番号 = 1
    If ページ表示行数 < 最終行数 Then
        最後データ番号 = ページ表示行数
    Else
        最後データ番号 = 最終行数
    End If
    
    Call Hyouji
    
End Sub

各ボタンにプロシージャを紐づけます

ボタンを押して、テストしてみましょう

表示ボタンは、　一覧表表示プロシージャと紐づけます

これでページ制御できるようになったけど、
数十ページ～数百ページ以上あるデータだとボタンで表示させるには無理があるよね？！
何ページとページを指定して表示したいね！
1ページの表示行数も変えて表示できるといいよね！

一覧表表示プロシージャをもう少し変更しましょう～


←　一覧表.Range(“J11”) ：表示する(している)ページ



←　一覧表.Range(“J17”) ：１ページに表示する行数

ページ表示行数が変更された場合

ページ表示行数が変更された場合

ページ表示行数 <> 一覧表.Range(“J17”)の時、（ページ表示行数は変数名ね）
ページ表示行数が変更されたと判断できる

Sub 一覧表表示()

　　'ページ表示行数が変更された場合
    If ページ表示行数 <> 一覧表.Range("J17") Then
        表示ページ = 1                                                  '表示ページは１にする
        ページ表示行数 = 一覧表.Range("J17")             　　　　　　　   'ページ表示行数を更新する
        全ページ数 = WorksheetFunction.RoundUp(最終行数 / ページ表示行数, 0)　'計算し直す
    
    End If

J17セル（ページ表示行数）の入力規制



整数　で　最小値１　最大値1000
整数以外・マイナス値の入力を排除できる

値を更新したので、
Hyoujiプロシージャの最後に

一覧表.Range(“J11”) = 表示ページ
一覧表.Range(“K11”) = “/” & 全ページ数

を追加します

Sub Hyouji()
 
    Dim i As Long, j As Long
    Dim Gyo As Long
       
    '前のデータを削除
    一覧表.Range("b3").CurrentRegion.Offset(1, 0).Clear

   　　　　　　　　～
    
    '罫線を引く
    With 一覧表.Range("b3").CurrentRegion.Borders
        .LineStyle = xlContains
        .ColorIndex = 1
    End With
    
    一覧表.Range("J11") = 表示ページ
    一覧表.Range("K11") = "/" & 全ページ数
    
End Sub

テストしてみましょう
一覧表.Range(“J17”)の値を変更し、表示ボタンを押します
テストの前に表示ボタンは、一覧表表示プロシージャと紐づけてね！

ページ表示行数が変更されましたか？
表示ページ（J11）と全ページ（K11）が変更されていることを確認します

表示ページが変更された場合


←　一覧表.Range(“J11”) ：表示する(している)ページ



←　一覧表.Range(“J17”) ：１ページに表示する行数

表示ページが変更された場合
J11セルは、表示しているページ数を表し　かつ　表示したいページを入力できる

表示ページ <> 一覧表.Range(“J11”)の時（表示ページも変数名ね）
表示ページが変更されたと判断できる

J11セル（ページ表示行数）の入力規制



整数　で　最小値１　
整数以外・マイナス値の入力を排除できる

入力された一覧表.Range(“J11”) > 全ページ数の時、
全ページ数以上ページは ないのでExit　Sub

Sub 一覧表表示()
    
    'ページ表示行数が変更された場合
    If ページ表示行数 <> 一覧表.Range("J17") Then
        　　～
    Else
    '表示ページが変更された場合
        If 表示ページ <> 一覧表.Range("J11") Then
            If 一覧表.Range("J11") > 全ページ数 Then Exit Sub  　　 '全ページ数を超える時
            表示ページ = 一覧表.Range("J11")                        '表示ページを更新する
        End If
    End If
　　　　　　　　　　～

テストしてみましょう！
表示したいページを入力し、表示ボタンを押します
指定ページが表示できましたか？

シートの保護

J11セルとJ17セル以外に保護をかけましょう
保護を掛けるとその他のセルは更新できなくなります

前のデータを削除でエラーとなり、更新できません

シートを更新する前にシートの保護を解除
一覧表.Unprotect

プロシージャの最後に再度シートの保護
一覧表.Protect

Sub Hyouji()
 
    Dim i As Long, j As Long
    Dim Gyo As Long
    
    'シートの保護解除'
   ' 一覧表.Unprotect
    
    　　　　～
    
    'シートの保護
     一覧表.Protect
     
End Sub

おまけ


書式(罫線・センタリング・表示形式など)を一気に貼り付ける方法


TestData.Range(“A2:G2”).Copy
一覧表.Range(“b4:h” & 最後データ番号 – 最初データ番号 + 4).PasteSpecial _
Paste:=xlPasteFormats

TestDataの見出し部分をCopyして、書式だけ１ページ全体に貼り付けます

プログラムコード

ThisWorkBook

Sub Workbook_Open()

    ページ表示行数 = 20                             '初期値は20に指定
    一覧表.Range("J17") = ページ表示行数   'J17セルの値を更新
    表示ページ = 1
    最終行数 = TestData.Cells(Rows.Count, 1).End(xlUp).Row - 1
    全ページ数 = WorksheetFunction.RoundUp(最終行数 / ページ表示行数, 0)
    
    '配列を使う場合
    ReDim Data(最終行数, 7)
    Data = TestData.Range("a2", "g" & (最終行数 + 1))
        
    'どこからどこまで表示
    最初データ番号 = 1
    If 最終行数 > ページ表示行数 Then
        最後データ番号 = ページ表示行数
    Else
        最後データ番号 = 最終行数
    End If
    
    Call Hyouji
    
End Sub

Module1

Public 最終行数 As Long　             'データの総行数
Public ページ表示行数 As Long   　  　 '１ページに表示する行数
Public 全ページ数 As Long      　      '表示するページの総数
Public 最初データ番号 As Long  　  　   '表示するページの最初のデータの位置
Public 最後データ番号 As Long  　 　    '表示するページの最後のデータの位置
Public 表示ページ As Long      　       '表示するページ
Public Data() As Variant      　       '顧客データを格納する配列
'一覧表.Range("J11")                   '表示する(している)ページ
'一覧表.Range("J17")                   '１ページに表示する行数
'********************************************************

Sub 一覧表表示()
    
    'ページ表示行数が変更された場合
    If ページ表示行数 <> 一覧表.Range("J17") Then
        表示ページ = 1                                     '表示ページは１にする
        ページ表示行数 = 一覧表.Range("J17")                'ページ表示行数を更新する
        全ページ数 = WorksheetFunction.RoundUp(最終行数 / ページ表示行数, 0)
    Else
        '表示ページが変更された場合
        If 表示ページ <> 一覧表.Range("J11") Then
            If 一覧表.Range("J11") > 全ページ数 Then Exit Sub   '全ページ数を超える時
            表示ページ = 一覧表.Range("J11")                            '表示ページを更新する
        End If
    End If
        
    'どこからどこまで表示
    最初データ番号 = (表示ページ - 1) * ページ表示行数 + 1
    If 最終行数 > 表示ページ * ページ表示行数 Then
        最後データ番号 = 表示ページ * ページ表示行数
    Else
        最後データ番号 = 最終行数
    End If
    
    Call Hyouji
    
End Sub

Sub Hyouji()
 
    Dim i As Long, j As Long
    Dim Gyo As Long
    
    'シートの保護解除'
    一覧表.Unprotect
    
    '前のデータを削除
    一覧表.Range("b3").CurrentRegion.Offset(1, 0).Clear

    Gyo = 4
'    For i = 最初データ番号 To 最後データ番号
'        For j = 1 To 7
'            '一覧表.Cells(Gyo, j + 1) = TestData.Cells(i + 1, j)
'            一覧表.Cells(Gyo, j + 1) = Data(i, j)
'        Next j
'        Gyo = Gyo + 1
'    Next i
    
    For i = 最初データ番号 To 最後データ番号
        一覧表.Cells(Gyo, 2).Resize(1, 7) = WorksheetFunction.Index(Data, i)
        Gyo = Gyo + 1
    Next i
    
    'TestDataの書式(罫線・センタリング・表示形式など)を一気に貼り付ける
    TestData.Range("A2:G2").Copy
    一覧表.Range("b4:h" & 最後データ番号 - 最初データ番号 + 4).PasteSpecial _
    Paste:=xlPasteFormats
    
    '罫線を引く
'    With 一覧表.Range("b3").CurrentRegion.Borders
'        .LineStyle = xlContains
'        .ColorIndex = 1
'    End With
    
    一覧表.Range("j11") = 表示ページ
    一覧表.Range("k11") = "/" & 全ページ数
    
    'シートの保護
     一覧表.Protect
     
End Sub

Sub 次のページ()

    If 表示ページ >= 全ページ数 Then Exit Sub
    表示ページ = 表示ページ + 1
    
    最初データ番号 = (表示ページ - 1) * ページ表示行数 + 1
    If 最終行数 > 表示ページ * ページ表示行数 Then
        最後データ番号 = 表示ページ * ページ表示行数
    Else
        最後データ番号 = 最終行数
    End If
    
    Call Hyouji
    
End Sub

Sub 前のページ()

    If 表示ページ <= 1 Then Exit Sub
    表示ページ = 表示ページ - 1
    
    最初データ番号 = (表示ページ - 1) * ページ表示行数 + 1
    最後データ番号 = 表示ページ * ページ表示行数
    
    Call Hyouji
    
End Sub

Sub 先頭ページ()
     
    If 表示ページ <= 1 Then Exit Sub
    表示ページ = 1
    
    最初データ番号 = 1
    If ページ表示行数 < 最終行数 Then
        最後データ番号 = ページ表示行数
    Else
        最後データ番号 = 最終行数
    End If
    
    Call Hyouji
    
End Sub

Sub 最終ページ()

    If 表示ページ >= 全ページ数 Then Exit Sub
    表示ページ = 全ページ数
    
    最初データ番号 = (表示ページ - 1) * ページ表示行数 + 1
    If 最終行数 > 表示ページ * ページ表示行数 Then
        最後データ番号 = 表示ページ * ページ表示行数
    Else
        最後データ番号 = 最終行数
    End If
    
    Call Hyouji
    
End Sub
【EXCEL VBA】PG付　中級編～　ページ制御　①ボタン・ページ指定　次・前・先頭・最終ページ、表示数変更、ページ指定、配列、Public変数、BookOpen時処理、入力規制、シートの保護/解除
【EXCELVBA】ページ制御　①ボタン・ページ制御画面のページ制御をやります～ボタンやページを指定することにより、見たいページを表示しましょう！*********************************************************訂正************************...
www.youtube.com

コメント