マクロでスピンボタンを相対参照に！コピーしても「リンクするセル」は変わらないを解消

11.27.2023

スピンボタンのあるデータ行をコピぺしても、リンクするセルはコピー先に反映されない。。。

私は仕事の関係上、在庫管理するにあたり簡易な棚卸表としてエクセルを使い、在庫数の入力をスピンボタンを設置して使っています。たまに商品データが増えるので都度データ行を追加し、スピンボタンのリンクセルもひとつひとつ更新していました。たまにしかしないから良いかと思っていましたが、さすがに面倒になってしまい、マクロ登録により解消することにしました。

スピンボタンのリンクセルの設定方法

スピンボタンを利用する時、通常は右クリック→コントロールの書式設定から「リンクするセル」を設定します。下図の場合だとスピンボタンを操作することで「C5」の値が変わります。

↓↓↓

この方法で設定した状態で仮にデータ行を追加するとしましょう。
各セルの形式ごとコピーしたいので5行目をコピーし、6行目を追加します。

↓↓↓

この状態で追加した6行目のスピンボタンを操作してみても、5行目のデータが変わってしまいます。

6行目のスピンボタンの設定を見てみると、数式をコピーした時のように「C6」になると思いきや、コピー元の「C5」のままであるためです。スピンボタンをコピーしても設定内容は変わりません。つまり、リンクするセルの設定を都度更新しなければなりません。

私の知る限りでは、この設定方法でコピー先の「リンクするセル」もスピンボタンの位置に追従する方法（相対参照）はありません。もし知っている方がいれば教えてほしいです。

そこでマクロで対応することにしました。

スピンボタンの「リンクするセル」を相対参照にするマクロ

相対参照基本マクロ

可能な限り少ないコードでシンプルにしたい私は、調べたところ基本的なマクロは下記になりました。

Sub SpinBtnLink()
  With ActiveSheet.Shapes(Application.Caller)
    .TopLeftCell = .OLEFormat.Object.Value
  End With
End Sub

これは操作された図形（ここではスピンボタン）に対してその図形が持つオブジェクトやプロパティを参照することにより値の増減値を取得し、指定セルに反映します。「.TopLeftCell」は、スピンボタンの左上部分に触れているセルを指定しています。
「リンクするセル」は空白のまま、このマクロをスピンボタンに登録することで相対参照のスピンボタンとなります。

「Shapes(Application.Caller)」はワークシート上にあるこのコードが書かれた図形のオブジェクトを指すため、スピンボタンでなくても他の図形で、参照できるオブジェクトやプロパティがあれば上記マクロのまま使用可能です。
例えばチェックボックスだと、onなら「1」、offなら「-4146」という定数を取得できます。

初期値をクリア

リンクするセルを相対参照にするには上記の基本マクロで実現できますが、スピンボタンで入力したデータをクリアした時の動作が不十分だということがわかりました。
このマクロではデータクリア（Delete）後、新たにスピンボタン入力した時、前回の値を記憶していました。一度消しても次入力する時は前回の続きからとなってしまいます。

↓データクリア（Delete）

↓再度入力

この対策として良い方法が思いつかなかったので、少々強引ですが「IsEmpty」でデータクリアを空白判定して切り分け、データクリア後の初期値は「1」としました。データクリア後の最初の動作は、スピンボタンの上下どちらを押しても「1」にはなってしまいますが、基本的に1以上が入力される表なので良しとします。

Sub SpinBtnLink()
  With ActiveSheet.Shapes(Application.Caller)
    '現在の値が空白の場合は1
    If IsEmpty(.TopLeftCell) Then
        .OLEFormat.Object.Value = 1
    End If
    .TopLeftCell = .OLEFormat.Object.Value
  End With
End Sub

「リンクするセル」をオフセット指定

スピンボタンの「リンクするセル」（値を反映させるセル）はOffsetで指定できます。
「.TopLeftCell」は、セル内に収めてスピンボタンを配置すれば、リンクするセルはスピンボタンのあるセルになりますが、「.TopLeftCell.Offset(0, 1)」のようにすると、配置したスピンボタンのセルを起点にオフセットできます。「Offset(0, 1)」は1列右にズレます。

Sub SpinBtnLink()
  With ActiveSheet.Shapes(Application.Caller)
    '現在の値が空白の場合は1
    If IsEmpty(.TopLeftCell) Then
        .OLEFormat.Object.Value = 1
    End If
    'リンクセルは同じ行の1列右
    .TopLeftCell.Offset(0, 1) = .OLEFormat.Object.Value
  End With
End Sub

まとめ

スピンボタンのコピー対策として、リンクするセルを相対参照にするマクロは下記で実現しました。

Sub SpinBtnLink()
  With ActiveSheet.Shapes(Application.Caller)
    If IsEmpty(.TopLeftCell) Then
        .OLEFormat.Object.Value = 1
    End If
    .TopLeftCell = .OLEFormat.Object.Value
  End With
End Sub

既存データの行コピーでデータを追加しても、スピンボタンに登録されたマクロはそままコピーされますので、「リンクするセル」をひとつずつ設定する必要はありません。

↓

↓

もし、もっと良い方法があるよ！って方はぜひご連絡を！