2023/01/20

スクロールバー種類別・操作別の値取得方法




ユーザーが簡単に値を変更できるツールの1つに「スクロールバー」があります。ユーザーフォームの他、ワークシート上やグラフシート上でも使用できる便利なコントロールです。そのスクロールバーで動かした値を取得・表示する方法は、コントロールの種類・配置した場所により異なります。
今回は、そのスクロールバーの値の「取得方法」を紹介します。

1.スクロールバーの部位名

まず、スクロールバーの各部の名称を図1で整理します。

スクロールバーの部位名
図1


図1の各名称は、シャフト(レール)を除いてMicrosoftのサイトで紹介されているものです。シャフト(レール)についてMicrosoftでは「スクロール ボックスとスクロール矢印との間の領域」と説明していますが、フォームコントロールのスクロールバー書式設定の中では「スクロールバー シャフト」という名前がついていますので、ここでは「シャフト」としています。
また、他のExcelのサイトでは「レール」とも呼んでいるようです。

なお図1はActiveXコントロールの絵になっていますが、フォームコントロールの場合でも構造は同じです。

2.スクロールバーの種類

スクロールバーは「コントロールの種類・配置した場所」により異なると説明しましたが、図2のように3種に分類できます。
 配置場所種類形状
 Excel2016  Excel2021 
1ユーザーフォーム上ActiveX
コントロール

2シート上
3フォームコントロール
図2


形状は大きく、ActiveXコントロールとフォームコントロールの2種類に分かれます。Excelのバージョンにより多少形が変わるようですが、特にフォームコントロールのExcel2021版(2019版も同じかもしれません)は、両端のスクロール矢印が見えないため、ユーザーがスクロールバーだと気付いてくれないかもしれません(スクロール矢印をクリックしている間は見えます)。

今回は、この3種類のスクロールバーについて「ユーザーが動かした値」を取得・表示する方法について紹介します。

3.概要

今回は、3種類のスクロールバーの動きを確認するため、図3のような簡単なもの(添付ファイル)を作成しました。
スクロールバーの部位名
図3


1つ目の「ユーザーフォーム上のスクロールバー」は、ボタン1をクリックすることで、スクロールバーのあるUserForm1が起動します。
そのフォーム上のスクロールバーを操作すると、操作方法により「Changeイベント」と「Scrollイベント」が発生しますので、イベント発生した時の値をそれぞれLabel1・Label2に表示させます。

2つ目の「シート上のActiveXコントロールのスクロールバー」は、Sheet1上のC4~D4セル上に配置しました。
そのスクロールバーを操作すると、操作方法により「Changeイベント」と「Scrollイベント」が発生しますので、Changeイベント発生時の値をE4セル(オレンジ色セル)に、Scrollイベント発生時の値をF4セル(黄色セル)に表示します。
またスクロールバーのLinkedCellプロパティに「値書き込みセル位置」としてG4セル(緑色セル)を登録しています。

3つ目の「シート上のフォームコントロールのスクロールバー」は、Sheet1上のC6~D6セル上に配置しました。
フォームコントロールには「マクロ登録」が出来ますが、そのマクロにスクロールバー値をE6セル(オレンジ色セル)に表示する機能を持たせました。
また「コントロールの書式設定」のコントロールタブ内の「リンクするセル」にG6セル(緑色セル)を登録することでスクロールバー値を出力させました。

4.操作用ワークシート(Sheet1)

添付ファイル」では図4のように、「開発」タブ→「コントロール」グループ→「挿入」から、フォーム起動ボタン、および2種類のスクロールバーをシート上に配置しました。
スクロールバーの部位名
図4


それぞれのオブジェクト名は、作成したままとし、上から「Button 1」「ScrollBar1」「Scroll Bar 1」となっています。
なお、作成をやり直したり手動で名前を変更したりすると、オブジェクト名は変わります。但しActiveXのスクロールバーのオブジェクト名と、フォームコントロールのスクロールバーの個数だけを確認しておけば、今回は一応OKです。
スクロールバーの部位名
図5


一番上のボタンには登録マクロとして、標準モジュールの「UFstart」サブプロシージャ(図6)を登録します。

ActiveXコントロールのスクロールバー、フォームコントロールのスクロールバーのプロパティ等の設定に関しては、各スクロールバーの説明内で紹介していきます。

5.ユーザーフォームのスクロールバー

5-1.標準モジュール(Module1)

シート上の一番上のボタンをクリックした時に呼び出されるのが、図6のフォーム起動マクロです。
  1. '========== ⇩(1) ユーザーフォームの起動 ============
  2. Public Sub UFstart()
  3.  UserForm1.Show
  4. End Sub
図6


02行目「UserForm1.Show」では、ユーザーフォームを起動します。ここではパラメータを指定していないのでモーダル起動となっていますが、モードレスで起動しても問題ありません。

5-2.ユーザーフォーム(UserForm1)

5-2ー1.フォームレイアウト

図6の02行目から呼び出されるユーザーフォーム上のコントロール類のレイアウトが図7です。
フォームレイアウト
図7


今回の目的であるスクロールバーを置き、Changeイベントにより得られる値をLabel1に、Scrollイベントにより得られる値をLabel2に書き込むことにします。値Labelの上部の題名等は任意です。

5-2ー2.フォームモジュール

まず発生するイベントは、スクロールバーを操作する部位や動作によって異なりますので、図8で整理します。
クリック場所操作移動量示す値発生イベント
スクロール矢印クリックSmallChange値移動後のValue値Change
シャフト(レール)クリックLargeChange値移動後のValue値Change
スクロール
ボックス
クリック
移動Value=1ずつスクロールボックスの現位置のValue値
Scroll
クリックを離すクリックを離した位置のValue値Change
図8


つまり、両端のスクロール矢印や、スクロールボックスとの間の領域(シャフト)をマウスクリックした時には、Changeイベントが発生します。そしてChangeイベントが発生した時点では、Value値は「変更後の値」となっています。

また、スクロールボックスをマウスで移動しようとクリックして移動した場合には、Scrollイベントが発生します。Scrollイベントが発生した時点では、Value値は「移動中の値」となります。
移動が完了しマウスを離した時には、Changeイベントが発生します。
なお、スクロールボックスをクリックしただけで「移動せずにマウスを離した」場合には、Scrollイベントは発生せずにChangeイベントが発生することになります。

以上を踏まえ、フォーム上のスクロールバーを操作した時に発生するイベントプロシージャが、図9になります。
  1. '========== ⇩(2) 値の変更の完了時 ============
  2. Private Sub ScrollBar1_Change()
  3.  Me.Label1.Caption = Me.ScrollBar1.Value
  4. End Sub
  5. '========== ⇩(3) スクロールボックスの移動時 ============
  6. Private Sub ScrollBar1_Scroll()
  7.  Me.Label2.Caption = Me.ScrollBar1.Value
  8. End Sub
図9


両端のスクロール矢印・シャフト部をクリックした時、及びスクロールボックスをクリックして離した時に発生するのが11行目のChangeイベントです。
12行目「Me.Label1.Caption = Me.ScrollBar1.Value」で、変更後のスクロールバーのValue値をLabel1に書き込みます。

スクロールボックスを移動した時に発生するのが16行目のScrollイベントです。
17行目「Me.Label2.Caption = Me.ScrollBar1.Value」で、移動中のValue値をLabel2に書き込みます。

5ー2ー3.ChangeイベントとScrollイベントの動作

スクロール矢印やシャフト部をクリックして「スクロールバーの値」を変更させた時にはChangeイベントが発生します。
スクロールボックスを移動した場合にはScrollイベントが発生し、スクロールボックスを離した瞬間にChangeイベントが発生します。

つまり(有名な)Changeイベントのみでスクロールバーの値を表示しようとすると、スクロールボックスを使用した場合は「移動中にはChangeイベントが発生せず」、スクロールボックスを離した時にやっとChangeイベントが発生して「スクロールバー移動先の値が分かる」という事になります。
スクロールバーの総移動量が少ない場合は、ある程度カンで移動できると思いますが、総移動量が大きい場合には「目的の値にスクロールバーを合わせるのに時間が掛かる」可能性があります。

6.シート上のActiveXコントロールのスクロールバー

6ー1.シートモジュール(スクロールバーを配置したシート:今回はSheet1)

シート上のActiveXスクロールバーを操作した時に発生するイベントは、フォーム上と同じで図10のようになります。
  1. '========== ⇩(4) 値の変更完了時 ============
  2. Private Sub ScrollBar1_Change()
  3.  Sheets("Sheet1").Range("E4").Value = ScrollBar1.Value
  4. End Sub
  5. '========== ⇩(5) スクロールボックスの移動時 ============
  6. Private Sub ScrollBar1_Scroll()
  7.  Sheets("Sheet1").Range("F4").Value = ScrollBar1.Value
  8. End Sub
図10


両端のスクロール矢印・シャフト部をクリックした時、及びスクロールボックスをクリックして離した時に発生するのが31行目のChangeイベントです。
32行目「Sheets("Sheet1").Range("E4").Value = ScrollBar1.Value」で、変更後のスクロールバーのValue値をE4セル(オレンジ色背景色)に書き込んでいます。

スクロールボックスを移動した時に発生するのが36行目のScrollイベントです。
37行目「Sheets("Sheet1").Range("F4").Value = ScrollBar1.Value」で、移動中のValue値をF4セル(黄色背景色)に書き込んでいます。

このイベントを使った動作は、フォームの場合と全く同じです。

6ー2.スクロールバーのLinkedCellプロパティ

6ー2ー1.LinkedCellプロパティ設定

シート上に配置したActiveXコントロールのスクロールバーには、セルに値を出力できる「LinkedCell」プロパティがあります。図11のようにスクロールバーをアクティブにして、右クリックで表示されるリスト中からプロパティを選択①し、現れたダイアログ中のLinkedCellプロパティ値にセル番地(今回は、G4セル)を指定②します。
ActiveXスクロールバーのプロパティ設定
図11


なおプロパティ設定を手動では無く、マクロから行うには図12のようなコードを実行します。
  1. '========== ⇩(6) スクロールバーのプロパティ設定 ============
  2. Private Sub SBAset()
  3.  Sheets("Sheet1").ScrollBar1.LinkedCell = "Sheet1!G4"
  4. End Sub
図12


52行目「Sheets("Sheet1").ScrollBar1.LinkedCell = "Sheet1!G4"」では、G4セルをLinkedCellに登録しています。
なお右辺を単純に「 = "G4"」としてもOKです。この場合は「指定したG4セルは、スクロールバーが配置されたシート上のG4セルだ」とExcelが認識してくれ、勝手に「"Sheet1!G4"」に変換してくれます。

6ー2ー2.LinkedCellプロパティの動作

このLinkedCellプロパティを設定しておくと、「スクロールバーの現在の値」を指定したセルに書き込んでくれます。
イベント発生を受けてから値を書き込む場合、Changeイベントでは「変更後のみ」であり、またScrollイベントでは「移動中のみ」での書き込みになりますが、このLinkedCellプロパティでは「移動中+移動後」の両方でValue値を出力してくれます。

ユーザーフォーム上に配置するスクロールバーもActiveXではあるのですが、そちらにはLinkedCellプロパティが無いので「シートに配置した時だけの特典」とも言えます。

7.シート上のフォームコントロールのスクロールバー

フォームコントロールのスクロールバーには、操作した事を感知するイベントはありません。その代わりに「リンクするセル」や「登録マクロ」があります。これらの動きは図13のようになります。
ユーザー操作動作内容
スクロールバー各部をクリックし値を動かす「リンクするセル」の値が変化
クリックを離す「登録マクロ」が実行される
図13


今回は、この「リンクするセル」「登録マクロ」について説明します。

7ー1.リンクするセルを使用

7ー1ー1.リンクするセルの登録方法

図14は、フォームコントロールのスクロールバーへ「リンクするセル」を登録する方法です。
フォームコントロールのスクロールバーの書式設定
図14


スクロールバーをアクティブにし、右クリックで現れるリスト中から「コントロールの書式設定」をクリックします。するとダイアログが起動しますので、「コントロール」タブの中の「リンクするセル」に、値を表示するセル(今回はG6セル)を指定します。最後にOKボタンで登録します。

また図15のように、マクロ側から「リンクするセル」を登録することも出来ます。
  1. '========== ⇩(7) リンクするセルの登録 ============
  2. Private Sub SBFset1()
  3.  Sheets("Sheet1").ScrollBars(1).LinkedCell = "G6"
  4. End Sub
図15


62行目「Sheets("Sheet1").ScrollBars(1).LinkedCell = "G6"」では、スクロールバーのLinkedCellプロパティ(=リンクするセル)にG6セルを指定しています。

この時「フォームコントロール」のスクロールバーを特定するためには、ワークシート上のScrollBarsコレクションの番号(今回は、シート上にスクロールバーが1つだけなので「1」)で指定し、「ScrollBars(1)」とします。

他の指定の仕方としては「Sheets("sheet1").Shapes(1)」と、Shapesコレクションの1番目としても表せます。
なお、実はフォームコントロールのスクロールバーは3番目に作ったので、元々は Sheets("sheet1").Shapes(3) でした。しかし図5の絵を作る際に、右端に表示した「オブジェクトの順番」を入れ替えて「1番下に表示」にしたため、Shapesの1番目になっています(オブジェクトの順番は下から数えます)。
また、図5の右端に表示されているオブジェクトの名前を使い「Sheets("sheet1").Shapes("Scroll Bar 1")」と、名前で指定してもOKです。(オブジェクト名は、数式バーの左側の「名前ボックス」でも確認できます。)

7ー1ー2.リンクするセルの動作

スクロールバーを操作(スクロール矢印をクリック、シャフト部をクリック、スクロールボックスを移動)した後の値が、そのまま「リンクするセル」に表示されます。ですので、スクロールボックスを移動中も値が変化します。

7ー2.登録マクロを使用

7ー2ー1.マクロの登録方法

まず図16のように、スクロールバーへマクロ登録を行います。
ActiveXスクロールバーのプロパティ設定
図16


スクロールバーをアクティブにし、右クリックで現れるリスト中から「マクロの登録」をクリックします。するとダイアログが起動しますので、事前に作成したマクロ(今回はSBFプロシージャ(=図18))を選択し、OKボタンをクリックします。

またマクロ登録をマクロ側から行うには、図17のようなコードを実行します。
  1. '========== ⇩(8) マクロの登録 ============
  2. Private Sub SBFset2()
  3.  Sheets("Sheet1").ScrollBars(1).OnAction = "SBF"
  4. End Sub
図17


72行目「Sheets("Sheet1").ScrollBars(1).OnAction = "SBF"」では、スクロールバーに「SBF」という名前のマクロをOnActionプロパティ(=マクロ登録)に設定しています。
なお右辺のマクロ名は、標準モジュール内のPublicプロシージャですので、ファイル名を付ける必要は無いのですが、登録すると「'its-025.xlsm'!SBF」のように自動的についてしまいます。
もし、これに合わせた名前にするのであれば「Sheets("Sheet1").ScrollBars(1).OnAction = "'" & ThisWorkbook.Name & "'!" & "SBF"」のようにThisWorkbook.Nameを「'(シングルクォーテーション)」で囲み、プロシージャ名との間に「!(クエスチョンマーク)」を入れます。

7ー2ー2.登録するマクロ(標準モジュール:Module1)

今回登録するマクロが図18です。
  1. '========== ⇩(9) 登録するマクロ ============
  2. Public Sub SBF()
  3.  Sheets("Sheet1").Range("E6").Value = Sheets("Sheet1").ScrollBars(1).Value
  4. End Sub
図18


82行目「Sheets("Sheet1").Range("E6").Value = Sheets("Sheet1").ScrollBars(1).Value」では、スクロールバーの値(Vallue値)を指定セル(今回はE6セル)に表示させます。

なお図18では、スクロールバーの値(Value値)を直接取得し、その値をセル(E6セル)に書き込んでいます。別な手法として、間接的に「リンクするセル」の値を取得し、その値をセルに書き込むのが図19です。
  1. '========== ⇩(10) スクロールバーのプロパティ設定 ============
  2. Private Sub SBF2()
  3.  Dim LC As String   '←「リンクするセル」のセル位置
  4.  LC = Sheets("Sheet1").ScrollBars(1).LinkedCell
  5.  If Not LC = "" Then   '←Linkの設定がされている場合
  6.   Sheets("Sheet1").Range("E6").Value = Range(LC).Value
  7.  End If
  8. End Sub
図19


93行目「LC = Sheets("Sheet1").ScrollBars(1).LinkedCell」では、「リンクするセル」でも説明したスクロールバーの書式設定内のリンクするセルの「セル位置」を取得し、変数LC(LinkするCellの略のつもり)に代入しています。
リンクするセルにセル位置が入っている場合は、例えば「"G6"」のような文字列が取得できます。しかし、もしリンクするセルへの登録がされていない場合には「""(長さゼロの文字列)」となります。

95行目「If Not LC = "" Then」では、93行目で取得した「リンクするセル」が空では無い(=設定されている)時に、96行目を実行します。
96行目「Sheets("Sheet1").Range("E6").Value = Range(LC).Value」は、リンクするセル(変数LC)の値を、指定したセル(ここではE6セル)に書き込みます。

7ー2ー3.登録マクロでの動作

登録したマクロが呼び出されるのは、図13で分かるように「スクロールバーを操作し終わり、クリックを離した時」です。そのため、例えばスクロールボックスを移動させている最中はマクロが実行されず、移動が完了した時に初めて実行されます。

8.まとめ

以上を整理すると図20のようになります。
 配置場所種類可能な機能と動作
Change
イベント
OnAction
(登録マクロ)
Scroll
イベント
LinkedCell
(リンクするセル)
スクロール矢印クリック
シャフトクリック
スクロールボックスを離した時
スクロールボックス
の移動時
値が変化
した時
1ユーザーフォーム上ActiveX
コントロール
2シート上
3フォームコントロール
図20


例えば年月を合わせるスクロールバーを設計する場合、LargeChangeに「12」を指定しておくことでシャフト部のクリックで1年飛びに移動できるようになり、年が決まったらスクロール矢印で月を合わせれば良いことになります。このような年月合わせをするユーザーにはChangeイベントだけで充分です。
しかし中には、スクロールボックスを動かして年月を合わせたい人もいるので、Scrollイベントで「途中結果」を示してあげる必要が出てきます。

シート上でスクロールバーを使う場合には、イベント以外にも今回紹介したプロパティも使用できますので、これらをうまく使って表示することで、ユーザー側の負担を少しでも軽くできればと思います。

アプリ実例

ワークシート上で永久カレンダーを作る
図形も貼り付けられるスケジュール帳
テキストデータのスケジュール帳(入力と出力は別画面)
西暦・和暦対照表
ExcelシートDBとSQLを使った会議室予約システム
年月をスクロールバーで選択する予定表ひな型
CSVファイルでデータを読み書きする月間予定表
複数系列のデータを連続的にグラフ化
グラフのX軸をスクロールバーで移動
マウス操作で日程の開始・完了を設定できるタスク表
データの重みを考慮したComboBox入力補助
条件付き書式で作る月単位シフト表
設備の稼働状態記録とデータ集計
先行予約可能な備品予約・貸出システム
図形カレンダーをクリックし日付入力
設備の稼働状態を入力し、グラフで確認

サンプルファイル

スクロールバー種類別・操作別の値取得方法(its-025.xlsm)
セキュリティ向上を目的として「インターネット経由でダウンロードしたOfficeファイル(Excel等)のマクロは、既定でブロック」されるようにOfficeアプリケーションの既定動作が変更になりました。(2022年4月より切替開始)
解除の方法については「ダウンロードファイルのブロック解除方法」を参照下さい。