献立自動化第３回目
これまでのレビュー
■ 週間献立表の枠組みを作成
■ 献立表の任意のセルをダブルクリックしたとき、レシピの一覧を表示するフォームを表示
詳しくは下のカードから参照してください
レシピデータの抽出
■ 表示したフォームでレシピ一覧中から任意のレシピを選択したとき、そのレシピの名前をセルに埋め込む処理を考えます。
■上の図でD13セル（赤枠のセル）をダブルクリックしてフォームが開いた状態です。
下図はsheet1の献立表をダブルクリックしたときの処理です。
８行目、 UserForm1.ComboBox1.Tag = Left(Cells(Target.Row, “C”), 2) はフォームが開いたときにレシピのリストを検索するキーワードをコンボボックスのタグに収納しています。（ダブルクリックしたセル位置の一つ前のセルの値の左から２文字を取り出して検索用キーワードとしています。）
Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
'食事カテゴリーの選択様態でフォームを開く
If Target.Row >= 6 And Target.Row <= 27 And Target.Column >= 4 And Target.Column <= 10 Then
If Not (Target.Row = 11 Or Target.Row = 19 Or Target.Row = 21) Then
If Left(Cells(Target.Row, "C"), 2) = "汁物" Then
UserForm1.ComboBox1.Tag = "汁"
Else
UserForm1.ComboBox1.Tag = Left(Cells(Target.Row, "C"), 2)
End If
UserForm1.Show
End If
End If
'ダブルクリックではセルが編集状態になるのでそれをキャンセルする
Cancel = True
End Sub
■フォームが開いたとき下のコードでこのキーワードを利用してリストをあいまい検索しています
Private Sub UserForm_Activate()
If ComboBox1.Tag = "その" Then
Call subete
ElseIf ComboBox1.Tag = "おや" Then
Call aimaiken("おやつ") 'コンボ１tagには食カテゴリーが入ってる（主食・主菜・副菜・・・）
Else
Call aimaiken(ComboBox1.Tag) 'コンボ１tagには食カテゴリーが入ってる（主食・主菜・副菜・・・）
End If
Me.Image1.Picture = LoadPicture(ActiveWorkbook.Path & ListView1.SelectedItem.SubItems(9))
End Sub
Call aimaiken(ComboBox1.Tag) が検索のコードです。’コンボ１tagには検索キーワードが入ってる（主食・主菜・副菜・・・）
■下図はあいまい検索するプロシージャです
Sub aimaiken(str As String)
'レシピ管理項目CSVを読込む フォルダrecipedata ファイルrecipeKanri.csv
Dim kanriCSV As Variant
Dim r As Long, i As Long, c As Integer
c = 0
kanriCSV = det_in("\recipedata\recipeKanri.csv")
With ListView1
.ListItems.Clear
For r = 1 To UBound(kanriCSV, 1)
If kanriCSV(r, 1) Like "*" & str & "*" Or kanriCSV(r, 2) Like "*" & str & "*" _
Or kanriCSV(r, 3) Like "*" & str & "*" Or kanriCSV(r, 4) Like "*" & str & "*" _
Or kanriCSV(r, 5) Like "*" & str & "*" Or kanriCSV(r, 6) Like "*" & str & "*" _
Or kanriCSV(r, 8) Like "*" & str & "*" Then
c = c + 1
.ListItems.Add Text:=kanriCSV(r, 0)
For i = 1 To 9
.ListItems(c).SubItems(i) = kanriCSV(r, i)
Next i
End If
Next r
End With
End Sub
■まず７行目、kanriCSV = det_in(“\recipedata\recipeKanri.csv”)で、検索対象の配列変数を作成しています。ファンクションプロシージャdet_in()では引数に”recipeKanri.csv”のCSVファイルを指定しています。
この”recipeKanri.csv”のCSVファイルはこの献立表と同じフォルダーにあるレシピ作成用ファイル、recipe２でレシピを作成すると、\recipedata\フォルダー内に自動的に保存するようになっています（レシピの管理用ファイルrecipeKanri.csvと、食材情報用ファイルrecipeData.csvが保存されています）
■ １１行目のIf文、kanriCSV(r, 1) Like “*” & str & “*”があいまい検索の構文です。
配列変数のr行目の２列目（配列の要素数は０から始まるので、この場合１になっているので２列目となる）の値にstr（キーワード）が含まれているかを調べることができます
ここでは更に、配列の３、４、５、６、７、９列目の値も調べるのでそれぞれを ORでつないでいます。これらの列のデータのどこかにstrキーワードが含まれていれば、このIF文は真となります。
そして１７行目のFor分に入り
１８行目の .ListItems(c).SubItems(i) = kanriCSV(r, i) でリストの列データが作成されます。
ここで注目するのはｃです。.ListItemsはリスト行を表すコレクションですからカッコのｃは指定する行数を意味します。ここでのｃはIF文が真になったときの回数をカウントしています。つまりこのIF文が偽だったとき、つまり検索結果が無かった時は.ListItemsを構成しないので、次にIF文が真になったときインデックスが飛んでしまうのでエラーになってしまいます。このためIF文が真になったときだけｃをカウントして.ListItems(c).SubItems(i) = kanriCSV(r, i)を実行しているのです。
■ 献立表の任意のセルをダブルクリックしたときフォームが開いてキーワードが含まれたデータだけを表示したリストビューができました。
■ここでは仮にレシピ名（鶏のから揚げ）の行をクリック選択して表示したイメージ写真で確認します。
■さらに、このリスト行をダブルクリックすると、献立表でダブルクリックしたセル（D13）にレシピ名が転送されます。
Private Sub ListView1_Click()
'リストをシングルクリックしたとき該当するイメージ写真を表示する処理
Dim imagName As String
With ListView1
If .SelectedItem.SubItems(9) <> "" Then
'リストのアドレス項目列にアドレスデータがあるとき
imagName = ActiveWorkbook.Path & .SelectedItem.SubItems(9)
Image1.Picture = LoadPicture(imagName)
Else
'アドレスデータが無いときは、それ用の画像なしを表示
Image1.Picture = LoadPicture(ActiveWorkbook.Path & "\resipImage\image2.jpg")
End If
End With
End Sub
Private Sub ListView1_DblClick()
'ダブルクリックでセルにレシピ名を転送
ActiveCell.Value = ListView1.SelectedItem.SubItems(1)
End Sub
■今回はここまでです。
まとめ
■週間献立表をダブルクリックでフォームを表示してレシピを選択するとそのデータを献立表に転記する仕組みをやってみました。献立表の指定する位置によって表示するレシピが変化するように工夫してみました。こうしたシステムを作成するときは、あらかじめ十分な検討を重ねて設計するのでしょうが私の場合はいつも行き当たりばったりでやるので途中で何度も紆余曲折しなかなか前に進みません。結果回りくどい部分や、それ、必要ある？というものも多く、独りよがりのシステムになってしまいがちです。
まとまってないですね！
ここまでのファイルをアップロードしておきますのでお試しください
ダウンロード
■下のカードからダウンロードできます。
コメント