[[20160722114303]]　『セルの相互同期について』（ニック）　ページの最後に飛ぶ

『セルの相互同期について』（ニック）

こちらの掲示板で似たような質問がありましたが、解決しそうにないので質問させてください。
http://www.excel.studio-kazu.jp/kw/20160602151334.html

仕事の資料で作成しているのですが、
sheet1のB5とB16に、名前の定義で作ったプルダウンリストを作成しています。
このリストはどちらも同じものを参照しています。

sheet1のB6とB17にはINDIRECT関数でB5やB16で選択したものをリストから選択できるように連動させています。

今回、対応表を作成しており、B6やB17で選択したものに対応するように表を連動させています。
B6もB17も同一の内容なのですが、その先の表の表示内容が異なるために別々の記載になります。

そこで、B6を選択した時にB17にも同じものを、B17を選択した時にB6を表示させるようにしたいと思い、VBAのsheet1の中に

Private Sub Worksheet_Change(ByVal Target As Range)

If Target.Address = "$B$6" Then
Range("$B$17").Value = Range("$B$6").Value
Else:
End If

If Target.Address = "$B$17" Then
Range("$B$6").Value = Range("$B$17").Value
Else:

End If

End Sub

こんな風に記述し、実行すると確かに動作しました。
しかし、動作直後、

実行時エラー'2147417848(80010108)':
'Value'メソッドは失敗しました:'Range'オブジェクト

と表示され、excelが強制終了してしまいます。

このメッセージを検索し、
https://support.microsoft.com/ja-jp/kb/414786
ここにたどり着くことはできたのですが、知識不足のためこのエラーを処理できる力なく、質問させていただきました。

分かりにくい文で恐縮ですが、どのように解決すればいいでしょうか＞＜

＜使用 Excel：Excel2010、使用 OS：Windows7 ＞

こんにちは

Changeイベントがループしています。

Private Sub Worksheet_Change(ByVal Target As Range)
　　Application.EnableEvents = False

    既存のコード

　　Application.EnableEvents = True
End Sub

としてみて下さい。

（ウッシ） 2016/07/22(金) 12:12

ありがとうございます！正しく動き、エラーも表示されなくなりました。

後学のために教えていただけるとありがたいのですが、

Changeイベントがループしている、といはどういうことでしょうか…。
無知ですみません。
（ニック） 2016/07/22(金) 12:31

こんにちは

Range("$B$17").Value = Range("$B$6").Value

とした時点で、

Private Sub Worksheet_Change(ByVal Target As Range)

が動いちゃうという事です。

（ウッシ） 2016/07/22(金) 12:35

ありがとうございます！

追加で質問になります。

Private Sub Worksheet_Change(ByVal Target As Range)

    Application.EnableEvents = False

If Target.Address = "$B$6" Then
Range("$B$17").Value = Range("$B$6").Value
Else:

End If

If Target.Address = "$B$17" Then
Range("$B$6").Value = Range("$B$17").Value
Else:

End If

    Application.EnableEvents = True
End Sub

この構文で、B6セルとB17セルは連動するようになりました。

同様に、同じシート内でB5セルとB16セルも連動させたいのですがこの場合に

Private Sub Worksheet_Change(ByVal Target As Range)

    Application.EnableEvents = False

If Target.Address = "$B$6" Then
Range("$B$17").Value = Range("$B$6").Value
If Target.Address = "$B$5" Then
Range("$B$16").Value = Range("$B$5").Value
Else:
End If

If Target.Address = "$B$17" Then
Range("$B$6").Value = Range("$B$17").Value
If Target.Address = "$B$16" Then
Range("$B$5").Value = Range("$B$16").Value
Else:

End If

    Application.EnableEvents = True
End Sub

こんな風に記述したところ

Ifブロックに対応する End If がありません。

とエラーではじかれました。

そもそも構文の作り方自体が間違っているような気がしないでもないのですが、
どのように記述するのが正しいでしょうか＞＜
（ニック） 2016/07/22(金) 12:44

 コード自体が正しいかどうかは見ていません。
 コンパイルレベルの話として、2か所ある Else: 、これを End If に変えてみてください。

（β） 2016/07/22(金) 13:12

 Private Sub Worksheet_Change(ByVal Target As Range)
     Application.EnableEvents = False

    If Target.Address = "$B$6" Then
        Range("$B$17").Value = Range("$B$6").Value
        If Target.Address = "$B$5" Then
            Range("$B$16").Value = Range("$B$5").Value
        Else:
        End If

        If Target.Address = "$B$17" Then
            Range("$B$6").Value = Range("$B$17").Value
            If Target.Address = "$B$16" Then
                Range("$B$5").Value = Range("$B$16").Value
            Else:

            End If

            Application.EnableEvents = True
 End Sub
 見やすいようにインデントをつけてみた。
 で、おかしい場所がわかるだろうか。

（ねむねむ） 2016/07/22(金) 13:23

すみません、
ねむねむ様のインデントつけていただいた構文で自分で見直し、End Ifを入れる場所がおかしいことに気づき、

Private Sub Worksheet_Change(ByVal Target As Range)

    Application.EnableEvents = False

If Target.Address = "$B$6" Then
Range("$B$17").Value = Range("$B$6").Value
End If

If Target.Address = "$B$5" Then
Range("$B$16").Value = Range("$B$5").Value
End If

If Target.Address = "$B$17" Then
Range("$B$6").Value = Range("$B$17").Value
End If

If Target.Address = "$B$16" Then
Range("$B$5").Value = Range("$B$16").Value
End If

    Application.EnableEvents = True
End Sub

このように表記することで改善いたしました。

ありがとうございました！
（ニック） 2016/07/22(金) 14:04

コメント返信：

[ 一覧(最新更新順) ]

[[20160722114303]] 『セルの相互同期について』（ニック） ページの最後に飛ぶ

[[20160722114303]]　『セルの相互同期について』（ニック）　ページの最後に飛ぶ