VBAで自動的にボタンを作成し固定の座標に配置したい

Question

Excel2021 にて とあるデータ入力フォーマットを作成中です。

1ページ目は数行の入力ページがあるのですが
1ページで収まるとは限らないため、2ページ目以降を作成する際はボタンを押下すると
VBAで2ページ目を作成し、特定の行に計算式や枠線を配置し、3ページ目を作成するためのボタンをA列の最下行に自動で生成するというコードを作成しました。

下記に実際のコードと実行結果の画像が記載されているのですが
実行をするとボタンの位置が最下行ではなく、変な位置に出力されてしまい、解決方法がわからず困っています。

Dim Act_sheet As Object
Dim button_y, button_x As Integer
Dim current_raw As Integer
Static downfirst, downlast As Integer

downfirst = 29
downlast = 53
button_x = downlast + 1
button_y = downlast + 1

Set Act_sheet = Worksheets(2)

'フッターにページ番号を挿入する
 With ActiveSheet.PageSetup
  .CenterFooter = "&P/&N"
 End With
 
 '2番目のワークシートに格子状の枠線を記載する。
 Act_sheet.Range(Cells(downfirst, 1), Cells(downlast, 7)).Borders.LineStyle = True
 
 'C列とE列の最後列まで掛け算の式を代入
 current_raw = downfirst
 While current_raw <= downlast
    Cells(current_raw, 6).Value = "=$C" & current_raw & "*$E" & current_raw
    Cells(current_raw, 5).NumberFormatLocal = "\#,##0"
    Cells(current_raw, 6).NumberFormatLocal = "\#,##0"
    current_raw = current_raw + 1
 Wend
 
 
  '次のページ最下行にボタンを生成する。
 With Act_sheet.Buttons.Add(Cells(button_y, 1).Left, _
                            Cells(button_y, 1).Top, _
                            Cells(button_y, 1).Width, _
                            Cells(button_y, 1).Height)
    .OnAction = "Pagemake_since2"
    .Characters.Text = "次のページ"
 End With
 
End Sub

　　

下記、画像の28行目のボタンは手動で配置しており、これをクリックすると
上記のコードが走り、29～53行目まで計算式と枠線を配置します。

このボタン配置を 54行目、80行目、96行目というように正確に配置するにはどのようにコードを修正したらよいでしょうか。

54,80,96の繰り上がりは動的に行う必要があると考えているので
静的変数に加算していく設計になっています。
なので、現状Rangeで指定するやり方は不可だと考えております。

回答をお願い致します。

Comments

[meg_]

@tennkさん
追加の説明はこの欄ではなく質問に追記してください。（質問は編集できます）

[meg_]

button_yで指定した行にボタンが作成されるようですが何が問題なのでしょうか？現状51行目にボタンが作成されますがこの位置を変えたいならbutton_yの数字を変更すれば良いかと思いますが何か違うのでしょうか？

[tennk]

button_y = downlast + 2 ですが
こちら、button_y = downlast + 1の間違いでした。
Cells(54,1)となるので A列54行目にボタンが表示されるはずなのですが
わずかにボタンの座標がズレていたり、画面のスクロールでボタンが望んだ位置に表示はされますがカーソルを合わせてもカーソルが変化せずボタンをクリックができないという状態になってしまいます。

[meg_]

こちらで(Excel2021 )簡単に試したところでは問題はないようでした。

[jinoji]

Act_sheet と ActiveSheet は別シートですか？
もしそうなら、ボタンの位置を指定する箇所もAct_sheetを明示して
Act_sheet.Buttons.Add(Act_sheet.Cells(button_y, 1).Left, _ ...
とかにしないとズレてしまいそうです。

[tennk]

@meg_
ありがとうございます。
私の方でもverは異なりますが別のpcのExcelで実施したところ変わらず位置ズレが発生しました。

[hatena19]

コード中に下記のコメントがありますが、
'2番目のワークシートに格子状の枠線を記載する。
ひょっとすると、ボタンのあるシートとは別のシートに枠線やボタンを追加するのですか。

[tennk]

@jinoji
ありがとうございます。
いえ、Act_sheetとActiveSheetは同じシートになります。
明示的にAct_sheetへ格納しておいた方が良いかと考えたのでオブジェクト型で変数を用意しています。

仰るやり方で実行してみましたがやはり、位置ズレが発生してしまいました。

[tennk]

@hatena19
ありがとうございます。
はい、その通りです。
1ページ目に配置されたボタンを押すことで2ページ目(画像の29行目から53行目)に格子状の枠線を入力し、A列54行目に3ページ目を作成するためのボタンを配置する
といった流れになっています。これをボタンを押した回数だけ繰り返すといった流れです。

[hatena19]

1ページ目に配置されたボタンがあるのは、ActiveSheet ですよね。
「2ページ目(画像の29行目から53行目)」というのも同じActiveSheetですか。
それとも別のシート(2番目のシート, Sheets(2) ?)ですか。

画像を見るかぎりは同じシート(ActiveSheet)に見えますが。

[tennk]

@hatena19
同じActiveSheetになります。
1シートに別のシートが有り、2シート目が画像のシートとなっているので
Set Act_sheet = Worksheets(2) と明示的に格納しています。

[hatena19]

「1シートに別のシートが有り」
日本語としておかしいですよ。
シートとページを混同してませんか。

1シートに、1ページ目、2ページ目・・・があるということではないのですか。
一つ目のシートに1ページ目、２つ目のシートに2ページ目・・・ということではないですよね。

[tennk]

@hatena19
失礼しました。
1シート目は別のシートで
2シート目が画像、およびActiveSheetとなっております。
1シートに、1ページ目、2ページ目・・・ が正しいです。

[hatena19]

一つのシートに「次のページ」ボタンが配置してあり、そのボタンをクリックするとボタンの下の行から、
24行7列の表罫線を表示して、その表の下に「次のページ」ボタンを表示(移動)するということなら、
私の回答を参照ください。

Answer 1

Sub Pagemake()
    Dim Act_sheet As Worksheet
    Dim downfirst, downlast
    
    Set Act_sheet = ThisWorkbook.Worksheets(2)
    
    Dim btn, r
    For Each btn In Act_sheet.Buttons
        If r < btn.TopLeftCell.Row Then r = btn.TopLeftCell.Row
    Next
    
    downfirst = r + 1
    downlast = downfirst + 24
    
    With Act_sheet
        'フッターにページ番号を挿入する
        .PageSetup.CenterFooter = "&P/&N"
        
        '2番目のワークシートに格子状の枠線を記載する。
        Dim rng As Range
        Set rng = .Cells(downfirst, 1).Resize(24, 7)
        rng.Borders.LineStyle = True
     
     
         'C列とE列の最後列まで掛け算の式を代入
         rng.Columns("E:F").NumberFormatLocal = "\#,##0"
         rng.Columns("F:F").Formula = "=$C" & rng.Row
     
    End With
      
      '次のページ最下行にボタンを生成する。
     With Act_sheet.Buttons.Add(Act_sheet.Cells(downlast + 1, 1).Left, _
                                Act_sheet.Cells(downlast + 1, 1).Top, _
                                Act_sheet.Cells(downlast + 1, 1).Width, _
                                Act_sheet.Cells(downlast + 1, 1).Height)
        .OnAction = "Pagemake"
        .Characters.Text = "次のページ"
     End With
 
End Sub

Answer 2

質問の症状とは関係ないかもすれませんが、
とりあえず、気になる点は、
まず、

Dim Act_sheet As Object
Dim button_y, button_x As Integer
Dim current_raw As Integer
Static downfirst, downlast As Integer

button_y と downfirst は型指定を省略していますので、variant型になります。
あと、Integer は Long にした方がいいでしょう。
Excel2021の最大行数はIntegerを超えますので。（まあ、そんなに追加することはないでしょうが）

Dim Act_sheet As Object
Dim button_y As Long, button_x As Long
Dim current_raw As Long
Static downfirst As Long, downlast As Long

---

次に、

54,80,96の繰り上がりは動的に行う必要があると考えているので
静的変数に加算していく設計になっています。

downfirst = 29
downlast = 53

これでは、静的変数にした意味がないです。固定値になってますので。

downfirst = downfirst + 29
downlast = downlast + 53

ただ、静的変数に格納した値はブックを閉じると消えてしまうので、いい方法ではないでしょう。

---

Set Act_sheet = Worksheets(2)

'フッターにページ番号を挿入する
 With ActiveSheet.PageSetup
  .CenterFooter = "&P/&N"
 End With

対象シートが Worksheets(2) と ActiveSheet となってますが、
ボタンをクリックしたシートに対して、操作するのだと思いますので、ActiveSheet に統一すればいいと思います。

---

    '次のページ最下行にボタンを生成する。
    With Act_sheet.Buttons.Add(Cells(button_y, 1).Left, _
                               Cells(button_y, 1).Top, _
                               Cells(button_y, 1).Width, _
                               Cells(button_y, 1).Height)
        .OnAction = "Pagemake_since2"
        .Characters.Text = "次のページ"
    End With

ページ追加ボタンは最下行だけにあればいいので、追加ではなく、位置を移動するだけでいいのでは。
途中にあるボタンを押されたら、ややこしいことになりませんか。

ボタンの位置を基準にして、その下に、罫線を設定するという設計にすれば、静的変数も不要になります。ボタンの位置は、TopLeftCell.Row で取得できます。

---

コード例

Sub Pagemake_since()
    Dim Act_sheet As Worksheet
    Dim btn As Button
    Dim downfirst As Long, downlast As Long

    Set Act_sheet = ActiveSheet
    'フッターにページ番号を挿入する
    Act_sheet.PageSetup.CenterFooter = "&P/&N"
 
    Set btn = Act_sheet.Buttons(Application.Caller) 'クリックしたボタン
    downfirst = btn.TopLeftCell.Row + 1
    downlast = downfirst + 24
    
    With Act_sheet.Range(Cells(downfirst, 1), Cells(downlast, 7))
        'ワークシートに格子状の枠線を記載する。
        .Borders.LineStyle = True
        'C列とE列の最後列まで掛け算の式を代入
        .Columns("F:F").Formula = "=$C" & downfirst & "*$E" & downfirst
        .Columns("E:F").NumberFormatLocal = "\#,##0"
    End With
 
 
    '次のページ最下行にボタンを移動する。
    btn.Top = Act_sheet.Cells(downlast + 1, 1).Top
End Sub

Comments

[tennk]

丁寧なご指摘やサンプルコードありがとうございます。

変数の型宣言の時点で勘違いしておりました。
Dim x, y As integer
のようにしたとしてもxはInteger型にならないのですね。

はじめのボタン座標をずらすことで
静的変数を使用しない方法は思いつきませんでした。
UI的にもそちらのほうがミスは少なくて済みますのでとても勉強になりました。

ありがとうございます。

Answer 3

皆様、お力添えいただきありがとうございました。

本件、解決いたしました。
位置ずれの理由としては、最初に生成したボタンのサイズがセルの幅を超えていたことが原因だったようです。

一度ボタンをセルに合わせて作成し直し、再度マクロを割り当てたところ
正常に同じ位置にボタンが生成されました。