セル値の変更でマクロを実行

質問

エクセルで棚卸表を作成しようとしています。各種類ごとに別々のシートに品名・単価が入ったマスターが登録してあります。（穀類シート、野菜シート、肉類シート、魚類シート、飲料シート　----）
印刷用のシートで特定のセルの値の変更によって(穀類、野菜、肉類など)、任意のシートを参照し表示する為、IFとVLOOKUP関数の組み合わせで初めは出来たですが、IFは１つの式では７個しか使用出来ず、シートの数が増えてきて、全てのシートを参照出来なくなりました。
そこで、マクロを使用して、特定のセルの値の変更のたびにマクロボタンを押し、式を変更したのですが、今までIF関数使用の時は、ある特定の値が変更されると瞬時に任意のシートを参照し表示されていたので、あまりスマートではありません。
特定のセルの値を変更したら、自動的にマクロが実行できる方法はありますか。それとも、もっと、良い方法があれば教えてください。
宜しければExcelファイルを見て頂きたいのですが。
お願いします。

回答No.4

imogasi · ベストアンサー

＃２，３です。
野菜、肉類、魚類等のテーブルが各種類ごとに、別シートになっている場合の例が出来ましたので、参考までに載せます。
＃３のコードの部分は下記に変えます。
Private Sub Worksheet_Change(ByVal Target As Range)
Dim sh2 As Worksheet
Dim sh3 As Worksheet
Set sh2 = Worksheets("sheet2")
Set sh3 = Worksheets("sheet3")
'----
If Target.Column = 1 Then 'Ａ列ならば
Select Case Mid(Target, 1, 1)
Case "Ｙ"
' MsgBox Target
x = WorksheetFunction.VLookup(Target.Value, sh2.Range("a1:b6"), 2, False)
Target.Offset(0, 1) = x
Case "Ｍ"
x = WorksheetFunction.VLookup(Target.Value, sh3.Range("a1:b6"), 2, False)
Target.Offset(0, 1) = x
Case "Ｆ"
MsgBox "F"
End Select
End If
End Sub
各シート別のテーブルを指定するところは、SHEET2!RANGE("A1:B6")等で上手く行かなくて、手間取りました。
なお＃３の例は商品コードは、テーブル表もＡ列への入力も全角文字数字で入れる例になっていますので、ご注意下さい。
また魚類Ｆ以下は、手抜きをして表引きコード行を入れていませんが、野菜の該当行をコピーして、修正して下さい。

お礼

色々なケースを考えて頂き有難う御座います。
多分、＃４の例で解決すると思いますが、私がまだＶＢＡについて未熟なので、この例を私のファイルに当てはめてやるには少し時間が必要です。もし、そこで解決しなかったらまた質問させて頂きます。
有難うございました。

回答No.3

imogasi

＃２です。
もしテーブル（野菜、肉類、魚類等）が同一シートにあれば使えるＶＢＡのＶＬＯＯＫＵＰ関数の使用例を参考までに上げます。セルの値の一部分の値によって、参照するテーブルを、別のテーブル部分に相対化しています。
（参照テーブル）
テーブルを（Ａ１：Ｄ６）
Ｙ１５ たまねぎ Ｍ１６ ミンチ
Ｙ１４ キャベツ Ｍ１１ 鶏肉
Ｙ１３ なす Ｍ１２ 牛肉
Ｙ１２ きゅうり Ｍ１３ 豚肉
Ｙ１１ ごぼう Ｍ１４ 鴨肉
Ｙ１５ ピーマン Ｍ１５ 鹿肉
と、サンプルとして作ります。
（入力）
Ａ６以降に商品コードを入れます。
（Ａ列）　（Ｂ列）
Ｙ１２ きゅうり
Ｙ１２ きゅうり
Ｍ１３ 豚肉
Ｍ１４ 鴨肉
Ｙ１２ きゅうり
Ｙ１１ ごぼう
Ｍ１２ 牛肉
Ｙ１５ ピーマン
（結果）
すると上記のようにＢ列に商品名が瞬時にセットされます。単価まで増やすのは、推測は難くありませんでしょう。
（ＶＢＡ）
Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Column = 1 Then
Select Case Mid(Target, 1, 1)
Case "Ｙ"
' ActiveWorkbook.Names.Add Name:="table1", RefersToR1C1:="=Sheet2!R1C1:R7C2"
x = WorksheetFunction.VLookup(Target.Value, Range("a1:b6"), 2, False)
Target.Offset(0, 1) = x
Case "Ｍ"
x = WorksheetFunction.VLookup(Target.Value, Range("c1:d6"), 2, False)
Target.Offset(0, 1) = x
Case "Ｆ"
MsgBox "F"
End Select
End If
End Sub
別シートが指定できるよう、引き続き考えてみます。

回答No.2

imogasi

＞各種類ごとに別々のシートに品名・単価が入ったマスターが登録してあります
なぜ商品の大分類ごとに、別シートにしないといけないでしょうか。一本化すれば、依然としてＶＬＯＯＫＵＰ
が使えると思いますが。
●各商品のコード体系はどうなっていますか。
野菜、肉類、魚類等の判別は商品コードのどの桁とかで
判別できるのですか。
●印刷用のシートで特定のセルの値の変更によって(穀類、野菜、肉類など)、任意のシートを参照し・・・
「特定のセルの変更」とは「特定のセル」として、
野菜、肉類、魚類等のセルが別々に割り当てられているのでしょうか。（＃１のご回答はこのタイプを想定か。）
それとも「１つのセルの値を、特定の値への変更」を意味するのでしょうか。
例えば、Ｙを野菜を示すコードとして、Ｙ０１２と言うコードを入力すると、野菜シートのＹ０１２を見に行きたい
と言うことでしょうか。
この場合の対応は例えば
Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Column = 1 Then
Select Case Mid(Target, 1, 1)
Case "Y"
MsgBox "Y"
Case "M"
MsgBox "M"
Case "F"
MsgBox "F"
End Select
End If
End Sub
MsgBoxの部分に各処理のコードを書きます。
●ＶＬＯＯＫＵＰ関数の第２引数たる、表の範囲を、ＩＦ関数を使わずに、相対化する方法を、前面に出して質問されてはどうでしょう。回答がでるような気もします。
●コマンドボタンを野菜、肉類、魚類等に別に割り当てるのはどうでしょうか。
クリックイベントがつかえます。
あるいはリストボックスのアイテムに野菜、肉類、魚類等
を設定するのもどうでしょうか。
●ご質問者とそのグループの方の、一層の飛躍（例えばアクセスの利用やＳＱＬの利用、アクセスＶＢＡの利用）が必要な局面に来ているように感じます。
いよいよ、今後他の点でも、ややこしい道に迷い込みそうな予感がします。
＞宜しければExcelファイルを見て頂きたいのですが
これはどう言う意味ですか。どうしてみる？あり得ないのでは。

回答No.1

popesyu

マクロというかＶＢＡでワークシートの変更を見張るイベントがありますのでそれを利用するとかぐらいでしょうか。
コード的には大した量もないのでそう難しくはないんですが。多分どこにどのように書けばよいのかとかその辺から難しいですかね。
---
Private Sub Worksheet_Change(ByVal Target As Range)
Select Case Target
　Case Range("A1")
　＜処理＞
　Case Range("B1")
　＜処理＞
　Case Range("C1")
　＜処理＞
　・・・
End Select
End Sub
---
まぁそれよりもそれぐらいの複数のシートがからみあったリレーショナルなデータとなれば、アクセスなどで処理した方がスマートなものが出来るのではないかなと思いますけども。