みなさん，こんにちは。
シンノユウキ（shinno1993）です。
今回は「栄養計算ソフトに機能を追加しよう」の連載第3回．以下の記事の続きです．
前回はクラスモジュールを利用して，オプションボタンを変更した際のイベント処理を一元化しました．それによって，コードの処理が簡潔になり，メンテナンス性も向上しましたね．
今回は，さらに使いやすさを向上させるために，ユーザーフォームのチェックボックスなどを活用して，栄養素の項目行をシートに表示させる機能を追加していきます．
ではいきましょう！
どのシートでも使えるようにしたい！
現在作成している栄養計算ソフトでは，栄養計算できるシートが決まっています．
でも，これだと栄養計算のたびにシートをコピーしなければならず，かなり面倒です．
なので，特定のシートだけでなく，他のシートでも栄養計算ができるようにしていきます．そのためにはまず，栄養素の項目を任意のシートに表示できるように機能を追加することから始めていきましょう．
メニューに「項目行を追加」を加える
まずは，コマンドバーに「項目行を追加」のボタンを加えましょう．このコードはWorkbook_Openプロシージャに記述していきます．btnShowAddLabelの変数を宣言し，コマンドバーに追加しています．onActionでshowAddLabelとしていますが，これについては後ほど作成していきますのでご安心ください．
Private Sub Workbook_Open()
Dim bar As CommandBar
Dim menu As CommandBarPopup
Dim btnShowFoodGroup As CommandBarButton, btnShowSearch As CommandBarButton, btnShowAddLabel As CommandBarButton
'Excelのメニューバーを指定
Set bar = Application.CommandBars("Worksheet Menu Bar")
'すでにコマンドバーにメニューがある場合は削除
For Each menu In bar.Controls
menu.Delete
Next menu
'コマンドバーにメニューを追加
Set menu = bar.Controls.Add(Type:=msoControlPopup)
menu.Caption = "栄養計算ソフト"
'それぞれのボタンを設置→キャプションと実行するマクロを指定
Set btnShowFoodGroup = menu.Controls.Add(Type:=msoControlButton)
With btnShowFoodGroup
.Caption = "食品群から入力"
.OnAction = "showFormFoodGroup"
End With
Set btnShowSearch = menu.Controls.Add(Type:=msoControlButton)
With btnShowSearch
.Caption = "検索して入力"
.OnAction = "showFormSearch"
End With
'━━━━━ここから追加━━━━━
Set btnShowAddLabel = menu.Controls.Add(Type:=msoControlButton)
With btnShowAddLabel
.Caption = "項目行を追加"
.OnAction = "showAddLabel"
End With
'━━━━━ここまで━━━━━
menu.Visible = True
End Sub
そして，標準モジュールに以下のコードを追記してくだしさい．
Sub showAddLabel()
frmAddLabel.Show vbModeless
End Sub
項目行を追加するユーザーフォームを作成しよう
では，項目行を追加するためのユーザーフォームを作成していきましょう．フォームは，栄養素の項目を選択し，選択された項目のみシートに表示する，という仕様にしたいと思います．
まずは設定シートに栄養素を入力しよう
表示する栄養素項目を選択できるようにするためには，全ての栄養素についてチェックボックスに表示させなければなりません．しかし，チェックボックスの全てについて手動でCaptionを設定するのは面倒です．なので，フォームを開いた際に，設定シートに表示させる栄養素を入力し，それをフォーム側のチェックボックスのCaptionに設定する，という方法を取りたいと思います．
なので，現在の栄養計算シートに表示されている栄養素を，設定シートにコピーしてください．単位も同様にコピーしましょう．
フォームに栄養素項目を表示しよう
では，いよいよフォームを作成していきましょう．VBEから「挿入」→「ユーザーフォーム」で挿入してください．
オブジェクト名は「frmAddLabel」，Captionは「項目行を追加」としてください．
次に，栄養素項目を選択するためのチェックボックスを作成してください．栄養素項目は全部で62個ありますので，62個のチェックボックスを作成します．この際，Captionは後ほどコードで一括で設定しますので，変更する必要はありません．
ただし，コードでCaptionを設定する際は，オブジェクト名を指定して設定しますので，オブジェクト名が連番になるようにチェックボックスを並べてください．
そして，以下のコードを，このフォームのinitializeイベントとして追加しましょう．
Private Sub UserForm_Initialize()
Dim i As Long
For i = 1 To 62
Controls("CheckBox" & i).Caption = Worksheets(WSNAME_STG).Cells(i, 2).Value
Next i
End Sub
WSNAME_STGは定数で，設定シートのシート名が代入されています．設定シートの２列目の値を順番にチェックボックスのCaptionに設定しています．３列目には単位が入力されていますので，それをTagに入力しています．
この状態でフォームを開いてみましょう．以下のようにCaptionが設定されていればOKです．
この状態では，かなり見づらいので，整列などをうまく使用して，整えましょう．以下のような感じでいかがでしょうか．
チェックボックスに情報を追加しよう
さて，栄養素項目を表示する際は，このチェックボックスのCaptionから栄養素項目を取得し，それをシートに表示させます．しかし，栄養素項目だけでなく，その単位も表示させたいところです．そのため，その単位の情報もチェックボックスに追加しておくと良いでしょう．
それにはチェックボックスのTagプロパティに追加すると便利です．ユーザーフォーム開始時の処理を以下のように変更しましょう．
Private Sub UserForm_Initialize()
Dim i As Long
For i = 1 To 62
Controls("CheckBox" & i).Caption = Worksheets(WSNAME_STG).Cells(i, 2).Value
'以下の１文を追加
Controls("CheckBox" & i).Tag = Worksheets(WSNAME_STG).Cells(i, 3).Value
Next i
End Sub
こうすることで，設定シートの３行目がTagプロパティに代入されます．実際の処理の際は，Captionだけでなく，Tagも活用していきます．
シートに項目行を追加する処理を実装しよう
まずは，先ほど作成したフォームに，項目行を追加するためのボタンを追加しましょう．また，ついでにフォームを閉じるためのキャンセルボタンも追加しましょう．以下のような感じなればOKです．
| Caption | オブジェクト名 | |
|---|---|---|
| 追加するボタン | 追加する | btnAdd |
| キャンセルボタン | キャンセル | btnCancel |
では，コードを追加していきましょう．
まずは，キャンセルボタンを押した際の処理です．キャンセルボタンを押すと，フォームが閉じられるようにコードを記述していきましょう．
Private Sub btnCancel_Click()
Unload Me
End Sub
次にOKボタンを押した際の処理です．OKボタンを押した際には，アクティブセルに項目行表示用の行を追加し，そこにチェックボックスにチェックのついた栄養素を追加していきます．以下のようなコードになります．
Private Sub btnAddLabel_Click()
'アクティブセルのある行に項目行表示用の２行を追加
Dim activeRow As Long
Dim activeCol As Long
activeRow = ActiveCell.Row
activeCol = ActiveCell.Column
Range(Rows(activeRow), Rows(activeRow + 1)).Insert
'共通項目を挿入
Dim ctr As Control
Dim cnt As Long 'カウンタ変数
cnt = 0
Dim index As Long
Dim aryLabel1 As Variant
Dim aryLabel2 As Variant
aryLabel1 = Array("食事区分", "食品番号", "食品名", "調理前重量", "廃棄率", "重量")
aryLabel2 = Array("", "", "", "g", "%", "g")
For index = 0 To 5
Cells(activeRow, activeCol + cnt).Value = aryLabel1(index)
Cells(activeRow + 1, activeCol + cnt).Value = aryLabel2(index)
cnt = cnt + 1
Next index
'コントロールの数だけループを回す
For Each ctr In Controls
If Left(ctr.Name, 8) = "CheckBox" And ctr.Value = True Then 'チェックボックスでかつチェックが入ってる場合
'栄養素名を追加
Dim nut As String
nut = ctr.Caption
Cells(activeRow, activeCol + cnt).Value = nut
'単位を追加
Dim unit As String
unit = ctr.Tag
Cells(activeRow + 1, activeCol + cnt).Value = unit
End If
cnt = cnt + 1
Next
'処理が完了したらメッセージボックスを表示させフォームを閉じる
MsgBox "項目行を追加しました", vbOKOnly
Unload Me
End Sub
実際に動かしてみて，共通項目や栄養素項目が表示されればOKです．
全選択・全解除のボタンを追加しよう
最後に，チェックボックスの全ての項目をチェックするボタンと，逆にチェックを解除するボタンを追加しましょう．以下のような感じでOKです．
オブジェクト名は，それぞれbtnAllCheck，btnAllClearとしています．
そして，コードは以下の通りです．
Private Sub btnAllCheck_Click()
Dim ctr As Control
For Each ctr In Controls
If Left(ctr.Name, 8) = "CheckBox" Then
ctr.Value = True
End If
Next ctr
End Sub
Private Sub btnAllClear_Click()
Dim ctr As Control
For Each ctr In Controls
If Left(ctr.Name, 8) = "CheckBox" Then
ctr.Value = False
End If
Next ctr
End Sub
まとめ
今回は，「栄養計算ソフトに機能を追加しよう」の連載第３回として，ユーザーフォームを活用して，チェックボックスの内容をシートに転記する方法を紹介しました．
また次回もがんばりましょう．