エクセルでchangeプロジェクトを複数設定する場

質問

すみません、マクロの基本的な部分を分かっておらず、Google検索などで知識を得た程度のど素人なのですが。
A1にあ・い
A2にう・え
A3にお・か
をプルダウンで選べるように設定していて
【い】を選んだ場合は選択肢を【う】と【お】のみにする
【あ】を選んだら選択肢を戻す
【え】を選んだ場合は選択肢を【あ】と【お】のみにする
【う】を選んだら選択肢を戻す
【か】を選んだ場合は選択肢を【あ】と【う】のみにする
【う】を選んだら選択肢を戻す
という挙動を設定したく、複数のchangeプロジェクトを書いてみたのですが、どうしても1箇所のみうまくいきません。（下のマクロでは【え】を選んだ場合、A3の選択肢が消えない。）
書き方・考え方自体が間違っているのかもしれませんが、教えていただけませんでしょうか。

Private Sub Worksheet_Change(ByVal Target As Range)
処理1 Target
処理2 Target
処理3 Target
End Sub
Private Sub 処理1(ByVal Target As Range)
If Intersect(Target, Range("A1")) Is Nothing Then
Exit Sub
Else
If Range("A1").Value = "い" Then
With Range("A2").Validation
.Delete
.Add _
Type:=xlValidateList, _
Formula1:="う"
End With
Range("A2") = "う"
With Range("A3").Validation
.Delete
.Add _
Type:=xlValidateList, _
Formula1:="お"
End With
Range("A3") = "お"
MsgBox "入場区分を【い】に設定した場合は、分配フラグは【え】、お客様情報取得フラグは【お】に固定となります。"
ElseIf Range("A1").Value = "あ" Then
With Range("A3").Validation
.Delete
.Add _
Type:=xlValidateList, _
Formula1:="お,か"
End With
With Range("A2").Validation
.Delete
.Add _
Type:=xlValidateList, _
Formula1:="=INDIRECT(A1)"
End With
End If
End If
End Sub
Private Sub 処理2(ByVal Target As Range)
If Intersect(Target, Range("A2")) Is Nothing Then
Exit Sub
Else
If Range("A2").Value = "え" Then
With Range("A3").Validation
.Delete
.Add _
Type:=xlValidateList, _
Formula1:="お"
End With
Range("A3") = "お"
With Range("A1").Validation
.Delete
.Add _
Type:=xlValidateList, _
Formula1:="あ"
End With
Range("A1") = "あ"
MsgBox "分配フラグを【え】に設定した場合は、入場認証区分は【あ】、お客様情報取得フラグは【お】に固定となります。"
ElseIf Range("A2").Value = "う" Then
With Range("A3").Validation
.Delete
.Add _
Type:=xlValidateList, _
Formula1:="お,か"
End With
With Range("A1").Validation
.Delete
.Add _
Type:=xlValidateList, _
Formula1:="=INDIRECT(A3)"
End With
End If
End If
End Sub
Private Sub 処理3(ByVal Target As Range)
Private Sub Worksheet_Change(ByVal Target As Range)
If Intersect(Target, Range("A3")) Is Nothing Then
Exit Sub
Else
If Range("A3").Value = "か" Then
With Range("A1").Validation
.Delete
.Add _
Type:=xlValidateList, _
Formula1:="あ"
End With
Range("A1") = "あ"
With Range("A2").Validation
.Delete
.Add _
Type:=xlValidateList, _
Formula1:="う"
End With
Range("A2") = "う"
MsgBox "お客さま情報をかに設定した場合は、入場区分は【あ】、分配フラグは【う】に固定となります。"
ElseIf Range("A3").Value = "お" Then
With Range("A1").Validation
.Delete
.Add _
Type:=xlValidateList, _
Formula1:="あ,い"
End With
With Range("A2").Validation
.Delete
.Add _
Type:=xlValidateList, _
Formula1:="=INDIRECT(A1)"
End With
End If
End If
End Sub

回答No.3

kkkkkm · ベストアンサー

> 【え】を選んだ場合

この時の実行過程です。

Private Sub 処理2(ByVal Target As Range)
が実行されて
If Range("A2").Value = "え" Then
なので
Range("A3") = "お"
が実行されると思います。

としたら、その時点でWorksheet_Changeイベントが発生しますから
(この時点で、Worksheet_Changeが再帰呼び出しになっていると思います)

Private Sub 処理3(ByVal Target As Range)
が実行されて
ElseIf Range("A3").Value = "お" Then
以降を処理した後に

Sub 処理2の
Range("A3") = "お"
の後の
With Range("A1").Validation
に戻ってきます(再帰していたので)
その後に
Range("A1") = "あ"
がありますのでWorksheet_Changeイベントが発生して
Sub 処理1が実行されます。

という事が思惑通りでないのでしたら、イベント内でセルに値を代入するときはイベントをオフにしておくといいと思います。
途中でエラーになって止まった場合
Application.EnableEvents = True
だけを実行しないとイベントはオフのままになります。

Private Sub Worksheet_Change(ByVal Target As Range)
Application.EnableEvents = False
処理1 Target
処理2 Target
処理3 Target
Application.EnableEvents = True
End Sub

Sub 処理2のどこかでブレークポイント設けて止まったら、そこからF8でステップ実行すれば動きがわかります。

お礼

お忙しいところ、細かにご説明いただきありがとうございました。イベントをオフにする方法も分からず書いていましたので、ご説明いただき大変助かりました。50の手習いになってしまいますが、もっとちゃんと基礎を勉強しようと思います！

回答No.6

SI299792

私にはあなたの上げたような長いプログラムは理解できません。
短くすれば、わかりやすくなると思います。
貴方のサブルーチンの使い方は意味まありません。
サブルーチンを使うなら、このように使い回しできるようにすべきです。
'
Private Sub Worksheet_Change(ByVal Target As Range)
'
　　If Target.Address = "$A$1" And Target = "い" Then
　　　　MsgBox "入場区分を【い】に設定した場合は、分配フラグは【う】、お客様情報取得フラグは【お】に固定となります。"
　　　　ListSet [B1], "う"
　　　　ListSet [C1], "お"
　　ElseIf Target.Address = "$B$1" And Target = "え" Then
　　　　MsgBox "分配フラグを【え】に設定した場合は、入場認証区分は【あ】、お客様情報取得フラグは【お】に固定となります。"
　　　　ListSet [A1], "あ"
　　　　ListSet [C1], "お"
　　ElseIf Target.Address = "$C$1" And Target = "か" Then
　　　　MsgBox "お客さま情報を【か】に設定した場合は、入場区分は【あ】、分配フラグは【う】に固定となります。"
　　　　ListSet [A1], "あ"
　　　　ListSet [B1], "う"
　　ElseIf Not Intersect(Target, [A1:C1]) Is Nothing Then
　　　　ListSet [A1], "あ,い"
　　　　ListSet [B1], "う,え"
　　　　ListSet [C1], "お,か"
　　End If
End Sub
'
Sub ListSet(Cell As Range, Formula As String)
'
　　Application.EnableEvents = False
'
　　With Cell.Validation
　　　　.Delete
　　　　.Add xlValidateList, Formula1:=Formula
　　End With
'
　　If Not Formula Like "*,*" Then
　　　　Cell = Formula
　　End If
　　Application.EnableEvents = True
End Sub

お礼

お忙しいところ、ありがとうございました。マクロの基礎も何も分かっておらず見よう見まねで書いていましたので、いただいたマクロを元にもっと理解できるように努力いたします。この度はお力添えいただき、ありがとうございました。

回答No.5

kkkkkm

No.3の蛇足です。

あと、余計なお世話ですが

Private Sub Worksheet_Change(ByVal Target As Range)
Application.EnableEvents = False
処理1 Target
処理2 Target
処理3 Target
Application.EnableEvents = True
End Sub

の場合、各処理がどのセルを変更した時に実行されるのかサブプロシージャを見ないと分からないと思います。
あと、全てのセルのチェンジイベントで3つのサブプロシージャが実行されます。
各サブプロシージャで該当セルじゃないと何もしないで戻ってくるので不都合はないのですが、何もしないのでしたら元から行かないという方法もあると思います。

以下はひとつの方法ですが、A1,A2,A3以外だと何もせずに、各サブプロシージャがどのセルの変更で実行されるのかもわかると思います。

Private Sub Worksheet_Change(ByVal Target As Range)
If Not Intersect(Target, Range("A1:A3")) Is Nothing Then
Application.EnableEvents = False
If Target.Address = Range("A1").Address Then
処理1 Target
ElseIf Target.Address = Range("A2").Address Then
処理2 Target
ElseIf Target.Address = Range("A3").Address Then
処理3 Target
End If
Application.EnableEvents = True
End If
End Sub

回答No.4

kkkkkm

No.3の追加です。

Formula1:="=INDIRECT(A1)"
多分「あ」とか「い」とかで名前の管理をしていると思いますが
どこのセルを指定してるのか分からないので、ここについては考えていません。

回答No.2

imogasi

#1です。
【い】を選んだ場合は選択肢を【う】と【お】のみにする。
についてのみやって、みました。
あと（残りの質問の「あ」「え」「う」「か」「う」、の場合は類推して質問者でやって見て。
ーー
まず下記の初期設定を手動でしておく。
A1　タイプ＝リスト　値　あ,い　（注意　,は半角）
A２　タイプ＝リスト　値　う,え　（注意　,は半角）
A1　タイプ＝リスト　値　お,か　（注意　,は半角）
を設定しておく。
下記はSheet1のイベントで
Private Sub Worksheet_Change(ByVal Target As Range)
'ーーーーA1の場合ーーーーーーーー
If Target = Worksheets("Sheet1").Range("A1") Then
Application.EnableEvents = False
Select Case Target
Case "い"

With Range("A2").Validation
'Modify⇒入力規則を変更
.Modify Type:=xlValidateList, _
AlertStyle:=xlValidAlertStop, _
Formula1:="う"
End With
With Range("A3").Validation
'Modify⇒入力規則を変更
.Modify Type:=xlValidateList, _
AlertStyle:=xlValidAlertStop, _
Formula1:="お"
End With
End Select
Application.EnableEvents = True
End If
End Sub
ーーーーーーーー
A1で「い」を選択すると、A2は「う」、A3は「お」のリスト設定をする。
＝＝＝
上記は、Validationの　Modifyの仕組みを使って、１つ１つ、ちまちまと組んでいるが、全般に何か無理があるような気がする。
こういうデータの推移を追うのは、テーブル化する考え方で整理出来ないかな。
抜本的な、設計のし直しをできないかな。

お礼

お忙しいところ、細かに考えていただきありがとうございました。マクロの基礎も何も分かっておらずご質問してしまい、申し訳ございませんでした。いただいたマクロを元にもっと理解できるように努力いたします。この度はお力添えいただき、ありがとうございました。

回答No.1

imogasi

小生は、VBAなどの素人的勉強を長年やっているものですが、
＞changeプロジェクト
の「プロジェクト」の使い方はおかしくないですか？普通は「イベント」というのでは？
WEBで両方の意味を照会してみてください。
ーー
この程度の課題で、こんなにVBAのコード行数が多くなるのは、やり方が普通ではないのでは？
考え方＝処理方法（どういう道具立てで対処するか？）を改めるべきでは？
エクセルではこういう場合「入力規則」のリストを使うように思う。
プルダウンというのは、このことを指しているのだろうが、正式名ではないだろう。
普通は、連動式・多段参照の「入力規則」に持ち込む。
ー
本件は前段下記の選択が、次の選択を別セルで行うときに規定する、という仕組みでないので、ややこしい。
また「選択肢を戻す」というのが、具体的にどうするのかわかりにくい。
ーー
しかし本問題は普通とちょっと、処理内容が違うので、今から考えてみます。
でも他の方の回答が出るでしょうが。