【ExcelVBA】チェックボックスに含まれる情報をシートに転記してみよう【栄養計算ソフト】

Excel×栄養学

みなさん,こんにちは.
シンノユウキ(@y_stadio)です.

今回は「栄養計算ソフトに機能を追加しよう」の連載第3回.以下の記事の続きです.

【ExcelVBA】クラスモジュールを使用してイベント処理を一元化しよう!【栄養計算ソフト】
今回は「栄養計算ソフトに機能を追加しよう」の連載第2回。今回はさらに使いやすさを向上させるために、クラスモジュールを利用して、オプションボタンを変更した際のイベント処理を一元化して簡単に記述できるようにしていきます。 ではいきましょう!

前回はクラスモジュールを利用して,オプションボタンを変更した際のイベント処理を一元化しました.それによって,コードの処理が簡潔になり,メンテナンス性も向上しましたね.

今回は,さらに使いやすさを向上させるために,ユーザーフォームのチェックボックスなどを活用して,栄養素の項目行をシートに表示させる機能を追加していきます.

ではいきましょう!

どのシートでも使えるようにしたい!

現在作成している栄養計算ソフトでは,栄養計算できるシートが決まっています.

でも,これだと栄養計算のたびにシートをコピーしなければならず,かなり面倒です.

なので,特定のシートだけでなく,他のシートでも栄養計算ができるようにしていきます.そのためにはまず,栄養素の項目を任意のシートに表示できるように機能を追加することから始めていきましょう.

 

メニューに「項目行を追加」を加える

まずは,コマンドバーに「項目行を追加」のボタンを加えましょう.このコードは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は定数で,設定シートのシート名が代入されています.設定シートの2列目の値を順番にチェックボックスのCaptionに設定しています.3列目には単位が入力されていますので,それを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
        '以下の1文を追加
        Controls("CheckBox" & i).Tag = Worksheets(WSNAME_STG).Cells(i, 3).Value
    Next i
End Sub

こうすることで,設定シートの3行目がTagプロパティに代入されます.実際の処理の際は,Captionだけでなく,Tagも活用していきます.

 

シートに項目行を追加する処理を実装しよう

まずは,先ほど作成したフォームに,項目行を追加するためのボタンを追加しましょう.また,ついでにフォームを閉じるためのキャンセルボタンも追加しましょう.以下のような感じなればOKです.

 

Caption オブジェクト名
追加するボタン 追加する btnAdd
キャンセルボタン キャンセル btnCancel

では,コードを追加していきましょう.

まずは,キャンセルボタンを押した際の処理です.キャンセルボタンを押すと,フォームが閉じられるようにコードを記述していきましょう.

Private Sub btnCancel_Click()
    Unload Me
End Sub

 

次にOKボタンを押した際の処理です.OKボタンを押した際には,アクティブセルに項目行表示用の行を追加し,そこにチェックボックスにチェックのついた栄養素を追加していきます.以下のようなコードになります.

Private Sub btnAddLabel_Click()
    'アクティブセルのある行に項目行表示用の2行を追加
    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

 

まとめ

今回は,「栄養計算ソフトに機能を追加しよう」の連載第3回として,ユーザーフォームを活用して,チェックボックスの内容をシートに転記する方法を紹介しました.

また次回もがんばりましょう.

 

連載目次

  1. Excelメニューからマクロを実行しよう!
  2. クラスモジュールを使用してイベント処理を一元化しよう!
  3. チェックボックスに含まれる情報をシートに転記してみよう!
タイトルとURLをコピーしました