[[20161024165259]]　『マクロチェンジイベントについて』（江口）　ページの最後に飛ぶ

『マクロチェンジイベントについて』（江口）

マクロのチェンジイベントについて教えていただけませんでしょうか
せる〔a1:g50〕の範囲の値がA又はKが入力された時に入力されたセルの右のセルにZを入力する
チェンジイベントが分かりません…どなたか教えてください。
例
A5にAを入力したらA6にZが自動で入力

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

ども＾＾
例えば、こんな感じですかね。

Private Sub Worksheet_Change(ByVal Target As Range)

    If Intersect(Target, Me.Range("A1:G50")) Is Nothing Then Exit Sub

    Select Case Target.Value
        Case "A", "K"
            Target.Offset(, 1).Value = "Z"
    End Select
End Sub

細かい説明はしません。解らないところがあれば聞いてください。
（まっつわん） 2016/10/24(月) 17:22

やってみます
ありがとうございます
大変たすかります
（江口） 2016/10/24(月) 17:23

 Worksheet_ChangeイベントでTargetは複数セルになることもあるので。

 Private Sub Worksheet_Change(ByVal Target As Range)
    Dim WK_RANGE    As Range

    If Intersect(Target, Range("A1:G50")) Is Nothing Then Exit Sub

    Application.EnableEvents = False

    For Each WK_RANGE In Intersect(Target, Range("A1:G50"))
        With WK_RANGE
            If .Value = "A" Or .Value = "K" Then
                .Offset(, 1).Value = "Z"
            End If
        End With
    Next
    Application.EnableEvents = True

 End Sub

 なお、入力されているAやKが消されてもZはきえない。

（ねむねむ） 2016/10/24(月) 17:27

あ、バグ発見＾＾；
複数セルを一気に操作すると（コピペやクリア等）、
エラーが出ます。
どのように回避しましょう？

「複数セルが対象の時は、処理をしない。」でいいのかな？

っと、やはり指摘が入ってますね＾＾；；
（まっつわん） 2016/10/24(月) 17:31

ねむねむｻﾝ
ありがとうございます

あとまっつわんｻﾝので上手くったのですが
A1:G50をまとめて選択し、消去するとエラーがでます
なぜですか？
A1の単独の消去ではエラーがでません

またA又はKが消去されたらZも消去するパターンはどうなりますか
（江口） 2016/10/24(月) 17:39

 失礼します。

 >>「複数セルが対象の時は、処理をしない。」でいいのかな？

 よく、こういった制御を見かけますが、具合悪いと思います。
 まず、そんな入力はしないと思いますが、対象領域内で　コピペやフィルコピーがあった場合、
 A であっても K であっても、右隣りのセルが Z になりません。
 おそらく、シートのデータ仕様としては、AないしはKの時は絶対に右隣がZだということになっていると思います。
 シート内のデータの信憑性が崩れますよね。

 Changeイベント処理は、Intersectをかけたものからセルを取り出すのが鉄則だと思いますね。

 ところで、（今回の要件ではＯＫなんですが）いつも悩むんですが、削除や挿入された場合、
 特に削除の場合、入力されていない領域が Targetに入ってきますね。
 いつも、そういうことは考えないようにしていますが、皆さんどうしてますか？

（β） 2016/10/24(月) 17:46

 >>またA又はKが消去されたらZも消去するパターンはどうなりますか

 こんな感じですね。

 Private Sub Worksheet_Change(ByVal Target As Range)
    Dim r As Range
    Dim c As Range

    Set r = Intersect(Target, Range("A1:G50"))
    If r Is Nothing Then Exit Sub

    Application.EnableEvents = False

    For Each c In r
        Select Case c.Value
            Case "A", "K"
                c.Offset(, 1).Value = "Z"
            Case Empty
                c.Offset(, 1).ClearContents
        End Select
    Next
    Application.EnableEvents = True

 End Sub

（β） 2016/10/24(月) 18:56

ありがとうございます

すいません
AとZ
KとZ

は常にワンセットであり
Zが入力されているところに他のPなどが入ると
メッセージ表示され
消去または処理しないようにするには
何度すいません

（江口） 2016/10/24(月) 19:19

 >>Zが入力されているところに他のPなどが入ると

 こういう仕様でいいのですか？

 範囲内のセル（B1:H50) で左隣が AまたはKなら Zしか入力できないということではないのですか？

（β） 2016/10/24(月) 19:23

 ↑の理解で正しければ。

 一瞬、B1:H50 を選択して入力規則、ユーザー定義で　=OR(AND(A1<>"K",A1<>"A"),AND(OR(A1="K",A1="A"),B1="Z"))
 こう考えたのですが、空白入力が許されてしまい、K-空白、A-空白というペアが出来上がってしまうので
 断念し、Changeイベントに追加。

 Private Sub Worksheet_Change(ByVal Target As Range)
    Dim r As Range
    Dim c As Range

    Set r = Intersect(Target, Range("B1:H50"))

    If Not r Is Nothing Then
        For Each c In r
            Select Case c.Offset(, -1).Value
                Case "A", "K"
                    If c.Value <> "Z" Then
                        MsgBox "A,Kの右隣りにはZしか入力できません" & vbLf & _
                            "入力を取り消します"
                        Application.EnableEvents = False
                        Application.Undo
                        Application.EnableEvents = True
                        Exit Sub
                    End If
            End Select
        Next
    End If

    Set r = Intersect(Target, Range("A1:G50"))
    If r Is Nothing Then Exit Sub

    Application.EnableEvents = False

    For Each c In r
        Select Case c.Value
            Case "A", "K"
                c.Offset(, 1).Value = "Z"
            Case Empty
                c.Offset(, 1).ClearContents
        End Select
    Next
    Application.EnableEvents = True

 End Sub

（β） 2016/10/24(月) 19:47

無事なりました
大変ありがとうございました

（江口） 2016/10/24(月) 20:03

すいません
また教えてください
それぞれのには入力できる文字があらかじめ指定するにはどうしたらよいですか？
入力できる文字は　I1:k50に入力することとします
（江口） 2016/10/27(木) 22:52

 入力できる文字とは　今、固定で判断している "A" と "K" にかわり、別途登録した文字列　を使う　ということですか？

 その登録が I1:K50 に入っているのですか？　
 50個も　入力可能文字を登録するのですか？
 そもそも、 少なくとも、I1:I50　という領域は、現状、A1:H50 に　対象文字が入ったら "Z" になる場所ではないですか？
 そんなところに登録してあっても、上書きで、消えてしまいますが？

 正確に、やりたいことを、具体例で示してください。

（β） 2016/10/28(金) 09:04

すいません説明が下手くそで

a1:g50の範囲にはA～Kの文字が入力可能です
しかし特定の行には入力できない文字を設定したいのですが
例えば3行目にはBとDを入力するとメッセージが表示され
入力されたBまたはDは消去される
同じように
10行目にはG.J.Mが入力不可能と設定したいのです
またそれぞれの行に入力できない文字はH1:k50の中に入力あらかじめ入力しておきます

よろしくお願いいたします(^^)

（江口） 2016/10/28(金) 18:14

 つまり、今までの要件とは別に、第三（？）の、入力不可文字を設定したいということですね？
 で、各行あたり最大、３文字まで入力不可文字を登録しておこうということですね。

 以下のように、できないことはないですけど、さらにあれも、これもということになると
 もう、ごっちゃごちゃになって、何が何だか分からなくなりそうです。
 このあたりで打ち止めにしておいたほうがいいのでは？

 Private Sub Worksheet_Change(ByVal Target As Range)
    Dim r As Range
    Dim c As Range

    Set r = Intersect(Target, Range("B1:H50"))

    If Not r Is Nothing Then
        For Each c In r
            Select Case c.Offset(, -1).Value
                Case "A", "K"
                    If c.Value <> "Z" Then
                        MsgBox "A,Kの右隣りにはZしか入力できません" & vbLf & _
                            "入力を取り消します"
                        Application.EnableEvents = False
                        Application.Undo
                        Application.EnableEvents = True
                        Exit Sub
                    End If
            End Select
        Next
    End If

    Set r = Intersect(Target, Range("A1:G50"))
    If r Is Nothing Then Exit Sub

    For Each c In r
        If Not IsEmpty(c) Then
            If WorksheetFunction.CountIf(c.EntireRow.Columns("I:K"), c.Value) > 0 Then
                MsgBox "この行には " & c.Value & " の入力はできません" & vbLf & _
                    "入力を取り消します"
                        Application.EnableEvents = False
                        Application.Undo
                        Application.EnableEvents = True
                        Exit Sub
            End If
        End If
    Next

    Application.EnableEvents = False

    For Each c In r
        Select Case c.Value
            Case "A", "K"
                c.Offset(, 1).Value = "Z"
            Case Empty
                c.Offset(, 1).ClearContents
        End Select
    Next
    Application.EnableEvents = True

 End Sub

（β） 2016/10/28(金) 20:22

 実際のシートのレイアウトが見えませんが、どの列であっても A なら　右が Z といったことは
 どうも、ピンときません。

 そうではなく、A とか K が入力される列は、A:G の中でも、この列とこの列とこの列と、・・・と
 決まっているのでは？　つまり、Zが自動設定される列も決まっているのでは？

 今回の 3文字が入力不可という列も、こことこことここ、そういうように決まっているのではないですか？

 そのあたりを明確にしてもらえれば、もう少しすっきりした制御もできるかもしれません。
 （できないかもしれませんけど）

（β） 2016/10/28(金) 20:26

コメント返信：

[ 一覧(最新更新順) ]

[[20161024165259]] 『マクロチェンジイベントについて』（江口） ページの最後に飛ぶ

[[20161024165259]]　『マクロチェンジイベントについて』（江口）　ページの最後に飛ぶ